Configuring Automatic PGA Memory--自动程序全局区管理

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm#PFGRF01401

 

PGA MemoryManagement

 

      The Program Global Area (PGA) is a private memory region containingdata and control information for a server process. Access to it isexclusive to that server process and is read and written only bythe Oracle code acting on behalf of it. An example of suchinformation is the runtime area of a cursor. Each time a cursor isexecuted, a new runtime area is created for that cursor in the PGAmemory region of the server process executing that cursor.

 

     A sort operator uses a work area (the sort area) to perform thein-memory sort of a set of rows. Similarly, a hash-join operatoruses a work area (the hash area) to build a hash table from itsleft input.

     The size of a work area can be controlled and tuned. Generally,bigger work areas can significantly improve the performance of aparticular operator at the cost of higher memory consumption.Ideally, the size of a work area is big enough that it canaccommodate the input data and auxiliary memory structuresallocated by its associated SQL operator. This is known as theoptimal size of a work area. When the size of the work area issmaller than optimal, the response time increases, because an extrapass is performed over part of the input data. This is known as theone-pass size of the work area. Under the one-pass threshold, whenthe size of a work area is far too small compared to the input datasize, multiple passes over the input data are needed. This coulddramatically increase the response time of the operator. This isknown as the multi-pass size of the work area. For example, aserial sort operation that needs to sort 10GB of data needs alittle more than 10GB to run optimal and at least 40MB to runone-pass. If this sort gets less that 40MB, then it must performseveral passes over the input data.

The goal is to have most work areas running with an optimal size(for example, more than 90% or even 100% for pure OLTP systems),while a smaller fraction of them are running with a one-pass size(for example, less than 10%). Multi-pass execution should beavoided. Even for DSS systems running large sorts and hash-joins,the memory requirement for the one-pass executions is relativelysmall. A system configured with a reasonable amount of PGA memoryshould not need to perform multiple passes over the input data.

     Automatic PGA memory management simplifies and improves theway PGA memory is allocated. By default, PGA memory management isenabled. In this mode, Oracle dynamically adjusts the size of theportion of the PGA memory dedicated to work areas, based on 20% ofthe SGA memory size. The minimum value is 10MB.

 

Note:

For backward compatibility, automatic PGA memorymanagement can be disabled by setting the value of thePGA_AGGREGATE_TARGET initialization parameter to0. When automatic PGA memory management is disabled, themaximum size of a work area can be sized with the associated_AREA_SIZE parameter, such as theSORT_AREA_SIZE initialization parameter.

 

Configuring Automatic PGAMemory

 

     When running under the automatic PGA memory management mode, sizingof work areas for all sessions becomes automatic and the*_AREA_SIZE parameters are ignored by all sessionsrunning in that mode. At any given time, the total amountof PGA memory available to active work areas in the instance isautomatically derived from the PGA_AGGREGATE_TARGET initializationparameter.This amount is set to the value ofPGA_AGGREGATE_TARGET minus the amount of PGA memoryallocated by other components of the system (for example, PGAmemory allocated by sessions). The resulting PGA memory is thenassigned to individual active work areas, based on their specificmemory requirements.

(译文：在任何给定时间，可用于实例中各活动工作区的 PGA 内存
总量自动从PGA_AGGREGATE_TARGET 初始化参数派生。此内存量设置为
PGA_AGGREGATE_TARGET 值减去系统其它组件分配的 PGA 内存量（例如，会话
分配的PGA 内存）。由此得到的PGA 内存随后按照各活动工作区的特定内存需
求分配给相应的工作区)

      Underautomatic PGA memory management mode, the main goal of Oracle is tohonor the PGA_AGGREGATE_TARGET limit set by the DBA,by controlling dynamically the amount of PGA memory allotted to SQLwork areas. At the same time, Oracle tries tomaximize the performance of all the memory-intensive SQLoperations, by maximizing the number of work areas that are usingan optimal amount of PGA memory (cache memory). The rest of thework areas are executed in one-pass mode, unless the PGA memorylimit set by the DBA with the parameterPGA_AGGREGATE_TARGET is so low that multi-passexecution is required to reduce even more the consumption of PGAmemory and honor the PGA target limit.

      Whenconfiguring a brand new instance, it is hard to know precisely theappropriate setting for PGA_AGGREGATE_TARGET. You candetermine this setting in three stages:

1. Make a first estimate for PGA_AGGREGATE_TARGET,based on a rule of thumb. By default, Oracle uses 20% of the SGAsize. However, this initial setting may be too low for a large DSSsystem.

2. Run a representative workload on the instance and monitorperformance, using PGA statistics collected by Oracle, to seewhether the maximum PGA size is under-configured orover-configured.

3. Tune PGA_AGGREGATE_TARGET, using Oracle PGA advicestatistics.