Performance Tuning PGA_AGGREGATE_TARGET
- The oracle 9i introduces a new parameter PGA_AGGREGATE_TARGET to fix the issue of multiple parameters in oracle 9i such as SOR_AREA_SIZE, HASH_AREA_SIZE of earlier version.
- The PGA is private memory region that contains the data and control information for a server process. Oracle Database reads and writes information in the PGA on behalf of the server process. The RAM allocated to the PGA_AGGREGATE_TARGET is used by Oracle connections to maintain connection-specific information (e.g., cursor states) and to sort Oracle SQL result sets.
- PGA_AGGREGATE_TARGET allows Oracle9i perform far faster than earlier version because the memory is only allocated in used for the duration of the session upon which is immediately freed up to become available for use by other connected Oracle tasks.
Initial or Default Setting of PGA_AGGREGATE_TARGET
- By default oracle database uses PGA_AGGREGATE_TARGET 20% of SGA Size. However the initial or default setting may be too low for database. In that case you need to run a representative workload on the instance and monitor performance of PGA statistics. Finally after monitoring (if required)
- tune PGA_AGGREGATE_TARGET using oracle PGA advice setting.
- If you have total memory of system = 4GB then memory required for OS = 20% of total Memory 0.8GB resulting memory 3.2GB available for use of SGA+PGA.
- For OLTP system the PGA_AGG_TARGET = 20% of resulting memory i.e 3.2 * 0.2 = 0.64GB or 655MB. For DSS system the PGA_AGG_TARGET = 70% of resulting memory i.e 3.2 * 0.7 = 2.24GB or 1600MB
- If the amount of data processed fits the size of optimal work area (Optimal size is when the size of a work area is large enough that it can accommodate the input data) then all the operation is performed in memory and cache is almost 100%.
- If the amount of data processed larger than the optimal size then the input is divided into smaller pieces and in that case some piece of data are processed in memory while rest are spilled to temporary tablespace, thus an extra parse is performed on all or some input data, that corresponding size of available work area is called "one-pass". when the available work area size even less than "one-pass" then multiple passes over the input data are required causing dramatic increase in response time hence decreasing the cache hit %.
- Generally in OLTP system, size of input data is small hence mostly run in optimal mode where as in DSS system, input data is very large thus require more PGA for good or healthy performance rate.
- Before tuning PGA you need to monitor performace of automatic PGA memory. For that several dynamic performance views are available. These views show the total amount of RAM memory utilization for every RAM memory region within the database and new statistics to V$SYSTAT and the new V$PGASTAT and V$PGA_TARGET_ADVICE views assist the DBA in determining if this parameter is not set correctly and the best setting for the PGA_AGGREGATE_TARGET parameter.
SQL> Select * from v$SYSSTAT;
SQL> select name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
Profile Count Percentage
----------------------------------- ------------ ----------
workarea executions - optimal 2,258 100
workarea executions - onepass 0 0
workarea executions - multipass 0 0
Select name, value/1024/1024 VALUE_MB
from V$PGASTAT
where name in ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
select name, value
from V$PGASTAT
where name in ('over allocation count');
NAME VALUE_MB
--------------- ----------------
aggregate PGA target parameter 471
over allocation count 0
total PGA allocated 33.8583984375
total PGA inuse 26.32421875
The optimal executions are performed entirely within the allocated memory areas. This is the most favorable type of execution. Sometimes, the operation is too big to perform within allocated memory area, and then some part of the operation spills on to disk. If only one-pass was needed on disk, then this execution is noted on one-pass statistics. If more than onepass was needed on disk then this execution is noted on multi-passstatistics. Ideally all execution should be in optimal statistics and the statistics for one-pass and multi-pass should be zero.
PGA_AGGREGATE_TARGET multipass executions indicate a RAM shortage, and you should always allocate enough RAM to ensure that at least 95% of connected tasks can acquire their RAM memory optimally. Thus DBA needs to increase this Parameter when "multipass" value is greater than ZERO and Reduce whenever the optimal executions are 100 percent.
SQL> select name, value from v$pgastat; NAME VALUE ------------------------------------------------------ ---------- aggregate PGA target parameter 284164096 aggregate PGA auto target 235938816 global memory bound 14208000 total PGA inuse 25638912 total PGA allocated 35466240 maximum PGA allocated 1 84498176 total freeable PGA memory 0 PGA memory freed back to OS 0 total PGA used for auto workareas 3637248 maximum PGA used for auto workareas 15818752 total PGA used for manual workareas 0 maximum PGA used for manual workareas 0 over allocation count 0 bytes processed 18302224384 extra bytes read/written 4149905408 cache hit percentage 81.51
In the above display from v$pgastat we see the following statistics.
Aggregate PGA auto target - This column gives the total amount of available memory for Oracle9i connections. This value is derived from the value on the INIT.ORA parameter PGA_AGGREGATE_TARGET.
Global memory bound - This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below 1MB, you should increase the value of the PGA_AGGREGATE_TARGET parameter.
Total PGA allocated - This statistic display the high-water mark of all PGA memory usage on the database. You should see this value approach the value of PGA_AGGREGATE_TARGET as usage increases.
Over allocation Count - If over allocation > 0 indicating that PGA_Target is too small to even meet the minimum PGA memory needs then you must increase the PGA_AGG_TARGET.
extra bytes read/write - Ideally it should be small if it is having large value you should increase the PGA_TARGET.
Cache hit percentage - A value of 100% means that all work are executed by the system since instance startup time have used an optimal amount of PGA memory. When it is not running optimally one or more extra parse is performed on input data which reduce the cache hit percentage.
1> If available memory >= optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = optimal memory as in PGA_TARGET_ADVICE.
2> If available memory < optimal memory for 100% PGA cache hit %, set PGA_AGGREGATE_TARGET = available memory and settle for a lower PGA cache hit %.
Total PGA used for auto workareas - This statistic monitors RAM consumption or all connections that are running in automatic memory mode. Remember, not all internal processes may use the automatic memory feature. For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this statistic. Hence, we can subtract value to the total PGA allocated to see the amount of memory used by connections and the RAM memory consumed by Java and PL/SQL.
Estimated PGA memory for optimal/one-pass
This statistic estimates how much memory is required to execute all task connections RAM demands in optimal mode. Remember, when Oracle9i experienced a memory shortage, it invoked the multi-pass operation. This statistics is critical for monitoring RAM consumption in Oracle9i, and most Oracle DBA's will increase PGA_AGGREGATE_TARGET to this value.
This statistic estimates how much memory is required to execute all task connections RAM demands in optimal mode. Remember, when Oracle9i experienced a memory shortage, it invoked the multi-pass operation. This statistics is critical for monitoring RAM consumption in Oracle9i, and most Oracle DBA's will increase PGA_AGGREGATE_TARGET to this value.
SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
34 95 0
68 100 0
136 100 0
203 100 0
271 100 0
325 100 0
379 100 0
434 100 0
488 100 0
542 100 0
813 100 0
Set the value of PGA_AGG_TARGET to a value where we avoid any over allocation, so lower target value we can set 942 where allocation count is 0 as well as cache 100%.
SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
estd_total_executions estd_tot_exe,
estd_optimal_executions estd_opt_cnt,
estd_onepass_executions estd_onepass_cnt,
estd_multipasses_executions estd_mpass_cnt
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;
LOW_KB HIGH_KB ESTD_TOT_EXE ESTD_OPT_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
---------- ---------- ------------ ------------ ---------------- --------------
8 16 1721 1721 0 0
16 32 61 61 0 0
64 128 6 6 0 0
128 256 22 22 0 0
512 1024 19 19 0 0
1024 2048 61 61 0 0
2048 4096 2 2 0 0
You can use the content of above views to determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET.
To change the PGA_AGG_TARGET value:
Alter system set pga_aggregate_target=987758592;
After increasing the PGA check PGA_AGG_target again.
Alter system set pga_aggregate_target=987758592;
After increasing the PGA check PGA_AGG_target again.
select * from v$pgastat;
Some mor dynamic views to check the PGA perfromace:
v$process, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM V$SQL_WORKAREA_HISTOGRAM; V$SQL_WORKAREA_ACTIVE; V$SQL_WORKAREA; V$PGA_TARGET_ADVICE; V$PGA_TARGET_ADVICE_HISTOGRAM V$SYSSTAT V$SESSTAT
Comments
Post a Comment