search
HomeDatabaseMysql TutorialOracle9i自动PGA管理的新特性

Oracle9i自动PGA管理的新特性

Jun 07, 2016 pm 03:14 PM
new featurescharacteristicmanageautomatic

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 在Oracle9i之前,PGA的计算和控制都是比较复杂的事情,从Oracle9i开始,Oracle提供了一种SQL内存管理的新方法:自动化SQL执行内存管理(Automated SQL Execution Memory Management),使用这个新特

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

    在Oracle9i之前,PGA的计算和控制都是比较复杂的事情,从Oracle9i开始,Oracle提供了一种SQL内存管理的新方法:自动化SQL执行内存管理(Automated SQL Execution Memory Management),使用这个新特性,Oracle可以自动调整S Q L内存区,而不用关闭数据库,这一改进大大简化了DBA的工作,同时也提高了Oracle数据库的性能。

    为实现自动的PGA管理,Oracle引入了几个新的初始化参数:

    1.PGA_AGGREGATE_TARGET-此参数用来指定所有session总计可以使用最大PGA内存。这个参数可以被动态的更改,取值范围从10M ―― (4096G-1 )bytes. 2.WORKAREA_SIZE_POLICY-此参数用于开关PGA内存自动管理功能,该参数有两个选项:AUTO 和 MANUAL,当设置为AUTO时,数据库使用Oracle9i提供的自动PGA管理功能,当设置为MANUAL时,则仍然使用Oracle9i前手工管理的方式。

    缺省的,Oracle9i中WORKAREA_SIZE_POLICY被设置为AUTO.

    需要注意的是,在Oracle9i中,PGA_AGGREGATE_TARGET参数仅对专用服务器模式下(Dedicated Server)的专属连接有效,但是对共享服务器(Shared Server)连接无效;从Oracle10g开始PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连接同时生效。

    PGA_AGGREGATE_TARGET 参数同时限制全局PGA分配和私有工作区内存分配:

    1.对于串行操作,单个SQL操作能够使用的PGA内存按照以下原则分配:MIN(5% PGA_AGGREGATE_TARGET,100MB)

    2.对于并行操作30% PGA_AGGREGATE_TARGET /DOP (DOP=Degree Of Parallelism 并行度)

    要理解PGA的自动调整,还需要区分可调整内存(TUNABLE MEMORY SIZE)与不可调整内存(UNTUNABLE MEMORY SIZE)。可调整内存是由SQL工作区使用的,其余部分是不可调整内存。

    启用了自动PGA调整之后, Oracle仍然需要遵循以下原则:

    UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE

    数据库系统只能控制可调整部分的内存分配,如果可调整的部分过小,则Oracle永远也不会强制启用这个等式。

    另外,PGA_AGGREGATE_TARGET参数在CBO优化器模式下,对于SQL的执行计划会产生影响。Oracle在评估执行计划时会根据PGA_AGGREGATE_TARGET参数评估在Sort,HASH-JOIN或Bitmap操作时能够使用的最大或最小内存,从而选择最优的执行计划。

    对于PGA_AGGREGATE_TARGET参数的设置,Oracle提供这样一个建议方案1.对于OLTP系统PGA_AGGREGATE_TARGET  = ( * 80%) * 20% 2.对于DSS系统PGA_AGGREGATE_TARGET  = ( * 80%) * 50%

    也就是说,对于一个单纯的数据库服务器,通常我们需要保留20%的物理内存给操作系统使用,剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA,那么PGA可以占用Oracle消耗总内存的20%(OLTP系统)至50%(DSS系统)。

    这只是一个建议设置,更进一步的我们应该根据数据库的具体性能指标来调整和优化PGA的使用。

Oracle9i自动PGA管理的新特性

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.