PGA advise
Skrevet af: Karsten Birch-Johansen i
Program global area (PGA) 
Technorati Tags:
Program global area PGA
Hver eneste oracle session har et behov for memory. så der kan laves forskellige operationer; f.eks kan en applikation forespørge efter en sortering af data, ved f.eks brug af ORDER BY eller GROUP BY. Ved en sådan forespørgsel kan operationen foretages i memory, hvis der ellers er nok, og hvis ikke, foretages sorteringen på disk delvist (one pass eller multi pass) på hvad der svarer til TEMP tablespacet.
I versioner før Oracle 9 sad DBAeren selv via BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, SORT_AREA_SIZE og konfigurerede disse arbejds områder. Problemmet var hvis jeg satte SORT_AREA_SIZE til 10M og HASH_AREA_SIZE til 50M, og dermed havde allokeret 60M arbejdsarealer, men behovet var 20M til sorteringer og hash joins er ikke eksisterende, så skulle jeg til at omkonfigurerer igen.
I version 9i kom PGA_AGGREGATE_TARGET så til verden, og man slap for ovenstående men kunne blot nøjes med denne ene parameter. Værdien kommer Oracle selvfølgelig med nogen anbefalinger; 16% af SGA_TARGET for OLTP systemer og 40% for DSS systemer. For de fleste, benytter man typisk de nye v$ views på et kørende system, for at få et råd til hvor stor værdien skal være. Parameteren kan sættes i init.ora eller på et kørende system vha.
alter system set pga_aggregate_target=værdi
Vær opmærksom på værdien gælder for alle alle server processes og ikke for hver enkelt ! For at tune på PGA skal man først og fremmest kigge lidt på følgende;
SQL> select name, value from v$sysstat where name like '%workarea executions%'; NAME VALUE ---------------------------------------------------------------- ---------- workarea executions - optimal 997353 workarea executions - onepass 7807 workarea executions - multipass 0
Optimal fortæller os de fleste operationer er foregået i memory. 7807 er one pass, og dvs. operationen er sendt til disk og foregået der, i vores TEMP tablespace, men har kunnet klares på den ene gang. Multipass derimod er dyr og sorteringer sendes flere gange til disk før operationen har kunnet fuldføres.
Et andet view var fra version 9 er PGASTAT
SQL> select * from v$pgastat; NAME VALUE ---------------------------------------------------------------- ---------- aggregate PGA target parameter 981467136 aggregate PGA auto target 761545728 global memory bound 104857600 total PGA inuse 215543808 total PGA allocated 366279680 maximum PGA allocated 898377728 total freeable PGA memory 67436544 process count 98 max processes count 181 PGA memory freed back to OS 8.0197E+11 total PGA used for auto workareas 79865856 maximum PGA used for auto workareas 557657088 total PGA used for manual workareas 0 maximum PGA used for manual workareas 1356800 over allocation count 0 bytes processed 1.0835E+12 extra bytes read/written 7.8433E+11 cache hit percentage 58 recompute count (total) 29496
Læg mærke til linie 2 som det første! Er den meget lavere end PGA_AGGREGATE_TARGET er værdien sat for lav !
Fra version 9.2 kom over allocation count og cache hit med. Over allocation count er de situationer hvor PGA_AGGREGATE_TAGET er sat for lavt og Oracle selv skal ud og allokere mere memory - den værdi skal ideelt være 0 for det er en dyr operation. Hvis optimal, one-pass, og multi-pass er sat optimalt er cache hit percentage tæt ved 100%. I ovenstående var PGA_AGGREGATE_TAGET sat til 936M hvilket er en del og alligevel ikke, hvilket cache hit også fortæller der kun er på 58%.
Vores PGA er i dette tilfælde sat til 936M lad os se om V$PGA_TARGET_ADVICE kan hjælpe os;
SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
117 1.0866E+12 1.0105E+12 52 3231
234 1.0866E+12 4.4739E+11 71 5
468 1.0866E+12 4.1731E+11 72 0
702 1.0866E+12 3.9324E+11 73 0
936 1.0866E+12 3.9314E+11 73 0
1123 1.0866E+12 1.4814E+11 88 0
1310 1.0866E+12 1.4814E+11 88 0
1498 1.0866E+12 1.4814E+11 88 0
1685 1.0866E+12 1.4814E+11 88 0
1872 1.0866E+12 1.4814E+11 88 0
2808 1.0866E+12 1.4814E+11 88 0
3744 1.0866E+12 1.4814E+11 88 0
5616 1.0866E+12 1.4814E+11 88 0
7488 1.0866E+12 1.4814E+11 88 0
In our environments, its come to my attention that! When tuning, focus is on sga_target and other parameters, depending on the problems. Allmost everytime PGA_AGGREGATE_TARGET has been disregarded. You might think it does not have any value to the system, and thats why its disregarded - but yet there in all our init.ora files.
Every Oracle session needs memory to be able to do things like; an application requesting that data is sorted using ORDER BY or GROUP BY, before sent back to the application. If possible - depends on how much memory is available, and how many rows needs sorting - data is either sorted in memory, or on disk. Memory sorting a fast approach and disk sorting can be expensive. Disk sorting is done, in what we know as Oracles TEMP datafile/tablespace. If the sort can be done in one operation its called one-pass, if not! then its expensive, and called multi-pass.
Before Oracle version 9 we, as DBAs, had to control memory like this using these parameters; BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, SORT_AREA_SIZE. Lets say i decided SORT_AREA_SIZE to be 10M an HASH_AREA_SIZE to 50M, then i had claimed 60M of mermory. However if my needs where 20M for sorting and there where no hash joins on my system, i had to reconfigure, and with many databases it could be quite a puzzle.
In Oracle version 9i, PGA_AGGREGATE_TARGET was introduced. All you had to do was setting it to a size; 60M! And the system figured out where to use what. With the introduction Oracle recommended the following settings: 16% of SGA_TARGET for OLTP systems and 40% for DSS systems. Along with the new parameter, came a couple of new v$ views, where you could find advisory information regarding target size on an online system. The parameter itself can be set in your init.ora or online using this command
alter system set pga_aggregate_target=værdi
Pay attention: The value size is for all oracle sessions, not just one. To get started take a look at v$sysstat.
SQL> select name, value from v$sysstat where name like '%workarea executions%'; NAME VALUE ---------------------------------------------------------------- ---------- workarea executions - optimal 997353 workarea executions - onepass 7807 workarea executions - multipass 0
Optimal tells us how many executions where done in memory. One-pass tells us how many where done on disk, however in just 1 operation. Multi-pass is operations where the sorting where done on disk, but it took more than one pass/operation to get data sorted - and thats expensive.
Another view is PGASTAT
SQL> select * from v$pgastat; NAME VALUE ---------------------------------------------------------------- ---------- aggregate PGA target parameter 981467136 aggregate PGA auto target 761545728 global memory bound 104857600 total PGA inuse 215543808 total PGA allocated 366279680 maximum PGA allocated 898377728 total freeable PGA memory 67436544 process count 98 max processes count 181 PGA memory freed back to OS 8.0197E+11 total PGA used for auto workareas 79865856 maximum PGA used for auto workareas 557657088 total PGA used for manual workareas 0 maximum PGA used for manual workareas 1356800 over allocation count 0 bytes processed 1.0835E+12 extra bytes read/written 7.8433E+11 cache hit percentage 58 recompute count (total) 29496
Pay attention to line 2, if that value is much smaller than PGA_AGGREGATE_TARGET then its set to low.
As of Oracle version 9.2 over allocation count and cache hit, where introduced. Over allocation count are those situations where PGA_AGGREGATE_TARGET is set to low, and Oracle have to allocate extra memory itself. The value from pgastat and over allocation count should ideally be 0. If optimal, one-pass and multi-pass are optimal, cache hit should be near 100%. As you can see above PGA_AGGREGATE_TARGET is problerly set to low even though its high 936M, however cache hit only 58%.
V$PGA_TARGET_ADVICE can help us;
SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
117 1.0866E+12 1.0105E+12 52 3231
234 1.0866E+12 4.4739E+11 71 5
468 1.0866E+12 4.1731E+11 72 0
702 1.0866E+12 3.9324E+11 73 0
936 1.0866E+12 3.9314E+11 73 0
1123 1.0866E+12 1.4814E+11 88 0
1310 1.0866E+12 1.4814E+11 88 0
1498 1.0866E+12 1.4814E+11 88 0
1685 1.0866E+12 1.4814E+11 88 0
1872 1.0866E+12 1.4814E+11 88 0
2808 1.0866E+12 1.4814E+11 88 0
3744 1.0866E+12 1.4814E+11 88 0
5616 1.0866E+12 1.4814E+11 88 0
7488 1.0866E+12 1.4814E+11 88 0
Det jeg gør er at øge pga_aggregate_target fra 936M til 1123M, vha. alter system set pga_aggregate_target=1123M scope=both; Vi er dog ikke færdige med tuning af PGAen. Vend databasen og vent 1 dag eller 2. Hvis vi tager den sidste SQL ser det nu sådan ud hos mig:
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
117 1.0866E+12 1.0105E+12 52 3231
234 1.0866E+12 4.4739E+11 71 5
468 1.0866E+12 4.1731E+11 72 0
702 1.0866E+12 3.9324E+11 73 0
936 1.0866E+12 3.9314E+11 73 0
1123 1.0866E+12 0 88 0
1310 1.0866E+12 0 88 0
1498 1.0866E+12 0 88 0
1685 1.0866E+12 0 88 0
1872 1.0866E+12 0 88 0
2808 1.0866E+12 0 88 0
3744 1.0866E+12 0 88 0
5616 1.0866E+12 0 88 0
7488 1.0866E+12 0 88 0
Læg mærke til EST_RW_EXTRA_BYTES og EST_OVERALLOC er 0 når TARGET_SIZE_MB=1123M Det er det punkt hvor Oracle fortæller at tilføjer vi mere memory får vi ikke yderligere fordele ud af det, så vi stopper her.
