Monday, September 24, 2018

Dive into PGA


PGA Stats :  Oracle database has one of its memory components termed as PGA
All sorting operations are done in PGA. I am demonstrating here with an example : 
v$pgastat is a dynamic view to monitor on pga usage and few more metrics.The view has additional rows also however I have highlighted  the rows of interest here :
select * from v$pgastat
NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                                                                334495744 bytes
aggregate PGA auto target                                                                            262490112 bytes
global memory bound                                                                                      66898944 bytes
total PGA inuse                                                                                                    42831872 bytes
total PGA allocated                                                                                            57158656 bytes
total PGA used for auto workareas                                                                            0 bytes

We created a table test1a_tab1 and executed select query  using order by clause that fetched 86278 rows and during execution the pga stats shows as below.We can see that total PGA inuse increased

NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                                                                334495744 bytes
aggregate PGA auto target                                                                            262665216 bytes
global memory bound                                                                                      66898944 bytes
total PGA inuse                                                                                                    54038528 bytes
total PGA allocated                                                                                            67054592 bytes
total PGA used for auto workareas                                                             11401216 bytes

After the query completed see below result   from v$pgastat

NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                                                                334495744 bytes
aggregate PGA auto target                                                                            259209216 bytes
global memory bound                                                                                      66898944 bytes
total PGA inuse                                                                                                    46481408 bytes
total PGA allocated                                                                                            62681088 bytes
PGA memory freed back to OS                                                                    154927104 bytes
total PGA used for auto workareas                                                                            0 bytes

After another similar query execution we can see result below

NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                                                                334495744 bytes
aggregate PGA auto target                                                                            259494912 bytes
global memory bound                                                                                      66898944 bytes
total PGA inuse                                                                                                    57562112 bytes
total PGA allocated                                                                                            72249344 bytes
total PGA used for auto workareas                                                             11401216 bytes

NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                                                                334495744 bytes
aggregate PGA auto target                                                                            256296960 bytes
global memory bound                                                                                      66898944 bytes
total PGA inuse                                                                                                   106719232 bytes
total PGA allocated                                                                                           121667584 bytes
maximum PGA allocated                                                                                               121667584 bytes
total PGA used for auto workareas                                                             57006080 bytes


Another interesting row in v$pgastat is Cache hit.We executed similar order by queries in multiple parallel sessions and we can see that extra bytes read/written  increased as well as cache hit percentage got hit and reached at 82.97% from 100%.
Wehenver we look at Cache hit %age to be very less probably less than 50% then there is a need to increase the pga .We need to always look at AWR for problematic  period to monitor the cache hit%  under PGA section.

NAME                                                                                                                         VALUE UNIT
---------------------------------------------------------------- ---------- ------------
extra bytes read/written                                                                                 93182976 bytes
cache hit percentage                                                                                            82.97 percent
recompute count (total)                                                                                      2379

Another view that gives us more insight when there is memory pressure on pga is as below.In a idle database ONEPASS_EXECUTIONS would be 0 however if we look at the database when there is workload going on we may notice specific values of  ONEPASS_EXECUTIONS and MULTIPASSES_EXECUTIONS as below - we can 1 under ONEPASS_EXECUTIONS that means for one execution PGA was not optimal and Oracle had to look for additional memory for sorting from disk.
select * from v$sql_workarea_histogram;

LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS TOTAL_EXECUTIONS
---------------- ----------------- ------------------ ------------------ ---------------------- ----------------
                 1048576                  2097151                                40                               0                             0              40
                 2097152                  4194303                                 0                                 0                             0               0
                 4194304                  8388607                                 4                                 0                             0               4
                 8388608                 16777215                              18                               0                             0              18
                16777216               33554431                               0                                 0                             0               0
                33554432               67108863                               0                                 0                             0               0
                67108864             134217727                              0                                 1                             0               1


ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE
------------------ ---------------------- ---------------- -----------------
                                 0                                             0                 1048576                  2097151
                                 0                                             0                 2097152                  4194303
                                 0                                             0                 4194304                  8388607
                                 0                                             0                 8388608                 16777215
                                 0                                             0                16777216               33554431
                                 0                                             0                33554432               67108863
                                 1                                             0                67108864              134217727

After execution of
select * from test1a_tab2 order by object_name

over allocation count                                                                                                        0
bytes processed                                                                                                 552438784 bytes
extra bytes read/written                                                                                186365952 bytes
cache hit percentage                                                                                            74.77 percent
recompute count (total)                                                                                      2558
 ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE
                 2                                             0                67108864              134217727

To get pga advice we need to check using the query
SQL> select snap_id,PGA_TARGET_FOR_ESTIMATE,ESTD_EXTRA_BYTES_RW,ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT from DBA_HIST_PGA_TARGET_ADVICE where ESTD_PGA_CACHE_HIT_PERCENTAGE<100;

   SNAP_ID PGA_TARGET_FOR_ESTIMATE ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------------- ------------------- ----------------------------- --------------------
67   41811968       74272768   91 7
67   83623936       54915072   93 3
31   41811968       25526272   89 5
64   41811968       74272768   89 7
64   83623936       54915072   91 3
68   41811968       74272768   91 8
68   83623936       54915072   93 3
69   41811968       74272768   92 9
69   83623936       54915072   94 3
70   41811968       82341888   91        10
70   83623936       54915072   94 3


1 comment: