Oracle memory management PGA details

Oracle 10gIn the database, you need to set workarea_size_policy to AUTO, and set the pga_aggregate_target parameter to implementPGAOfMemory Management. Next we will introduce the implementation of this setup process. First, take a look at the following code:

 
 

  
  
SQL> show parameter pga    
  
  
 
  
  
NAME                                 TYPE        VALUE    
  
  
 
  
  
------------------------------------ ----------- ------------------------------     
  
  
 
  
  
pga_aggregate_target                 big integer 169M    
  
  
 
  
  
SQL> show parameter workarea    
  
  
 
  
  
NAME                                 TYPE        VALUE    
  
  
 
  
  
------------------------------------ ----------- ------------------------------     
  
  
 
  
  
workarea_size_policy                 string      AUTO    
  
  
 
  
  
SQL> show parameter pga  
  
  
 
  
  
NAME                                 TYPE           VALUE  
  
  
 
  
  
------------------------------------ ----------- ------------------------------  
  
  
 
  
  
pga_aggregate_target big  integer           169M  
  
  
 
  
  
SQL> show parameter workarea  
  
  
 
  
  
NAME                                TYPE            VALUE  
  
  
 
  
  
------------------------------------ ----------- ------------------------------  
  
  
 
  
  
workarea_size_policy         string            AUTO 
 
 

The pga_aggregate_target parameter can be set based on the experience value. A simple method to evaluate the PGA can be used to view the PGA Hit rate (PGA Cache Hit %) in the AWR report ), analyze whether the wait events of direct path read temp and direct path write temp are high. These wait events indicate the waiting events that result in a large number of temporary tablespace operations due to limited parameters set by PGA. Of course, there are also complicated methods to evaluate PGA. Let's take a look later.

Note: For 9I shared server connection, you must explicitly set SORT_AREA_SIZE and HASH_AREA_SIZE, that is, you cannot use the automatic management mode. 10 Gb does not have this restriction.

PGA_AGGREGATE_TARGET is a theoretical maximum value, and PL/SQL is easily exceeded). It is not allocated so much at ORACLE startup, you can even set the size of the production database larger than the physical MEM. Do not set pga_aggregate_target + sga <MEM. Do not challenge the limits of ORACLE ). A session may have multiple sort and hash workareas. Each workarea may use 5% or 100 M at most (controlled by two hidden parameters). Therefore, if each sort is expected, the hash workarea is 5 M, and PGA_AGGREGATE_TARGET should be set to 100 M. However, as the user increases or the workload increases, the capacity of each workarea may be reduced, because there is a limit on the total PGA_AGGREGATE_TARGET, such as 100 workareas, therefore, each instance can only be allocated to 1 MB.

Parallel query uses a maximum of 30% PGA_AGGREGATE_TARGET (controlled by hidden parameters). Each PIECE of parallel query is allocated a corresponding 30%, that is, parallel query may use 30 M and 10 PARALLEL, 3 M for each instance. This is also the reason why auto management is recommended. A system usually uses workload and the session Changes over time. Three users may be in the morning, and 300 users may be in the middle of the day, so fixed sort is used, hash parameters are out of date. automatic Management allows users to allocate more memory when less concurrency occurs. When more concurrency occurs, users can take care of the public and allocate less memory. Since ORACLE 9.2, PGA advisory is available. I do not know whether the 5% and 30% mentioned in this section are correct. I don't have time to read the oracle documentation. I would like to remind myself here.

V $ pgastat:

 
 

  
  
SQL> set pagesize 200  
  
  
 
  
  
SQL> select name||' '|| to_char(decode( unit,  
  
  
 
  
  
'bytes', value/1024/1024,  
  
  
 
  
  
value ),'999,999,999.9')||' '||  
  
  
 
  
  
decode( unit, 'bytes', 'mbytes', unit   
  
  
 
  
  
from v$pgastat;  
  
  
 
  
  
NAME||''||TO_CHAR(DECODE(UNIT,'BYTES',VALUE/1024/1024,VALUE),'999,999,999.9')||''||DECODE(UN  
  
  
 
  
  
--------------------------------------------------------------------------------------------  
  
  
 
  
  
aggregate PGA target parameter 169.0 mbytes  
  
  
 
  
  
aggregate PGA auto target 124.3 mbytes  
  
  
 
  
  
global memory bound 33.8 mbytes  
  
  
 
  
  
total PGA inuse 30.9 mbytes  
  
  
 
  
  
total PGA allocated 65.4 mbytes  
  
  
 
  
  
maximum PGA allocated 82.2 mbytes  
  
  
 
  
  
total freeable PGA memory .0 mbytes  
  
  
 
  
  
process count 24.0  
  
  
 
  
  
max processes count 33.0  
  
  
 
  
  
PGA memory freed back to OS .0 mbytes  
  
  
 
  
  
total PGA used for auto workareas .0 mbytes  
  
  
 
  
  
maximum PGA used for auto workareas .6 mbytes  
  
  
 
  
  
total PGA used for manual workareas .0 mbytes  
  
  
 
  
  
maximum PGA used for manual workareas .0 mbytes  
  
  
 
  
  
over allocation count .0  
  
  
 
  
  
bytes processed 23.5 mbytes  
  
  
 
  
  
extra bytes read/written .0 mbytes  
  
  
 
  
  
cache hit percentage 100.0 percent  
  
  
 
  
  
recompute count (total) 817.0 
 
 

Several important parameters are described as follows:

The target parameter value of PGA set by aggregate pga target parameter.

In automatic management mode, aggregate PGA auto target can be used in the oracle Workspace.

Total PGA inuse the pga used by the current instance.

Total PGA allocated the pga actually allocated by the current instance.

Maximum PGA allocated can be allocated to the maximum pga.

Over allocation count the PGA allocated by ORACLE exceeds the pga_aggregate_target times. This parameter can be used to determine whether pga_aggregate_target is set too small.

Cache hit percentage the PGA hit rate after the instance is started. If all operations are performed in MEM and not in TEMP, the hit rate should be 100%.