Tuning PGA_AGGREGATE_TARGET in Oracle 9i

, Oracle Pipeline SYSOP

Abstract '

Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to help better manage session working areas in a session’s Program Global Area (PGA). This paper discusses available methods to help tune this new Oracle 9i parameter.

Introduction

Each Oracle session needs memory set aside for it to perform certain work operations. For instance, if the application requests a sorting operation by using certain SQL statements, like GROUP BY or ORDER BY, the application’s session can perform this sort in memory, provided enough memory has been reserved for that sort operation. If there is not enough reserved memory, then the sort operation is done in pieces using a temporary holding area on disk in the TEMP tablespace.

Before Oracle 9i, the DBA configured a session’s working areas by configuring a number of parameters such as BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, and SORT_AREA_SIZE. Collectively, these parameters are referred to as the *_AREA_SIZE parameters. The problem with this approach is that one often reserved memory that was never used. If I set SORT_AREA_SIZE=1M and HASH_AREA_SIZE=5M, then I have allocated at least 6MB of working area. What if I needed 2MB for sorting and I am not going to perform any hash joins? I have 5MB of allocated working area that is sitting there idle that I would like to use to assist my sort operation. The pre-9i Oracle DBA had to carefully determine how to allocate the best working area sizes so that optimal performance could be achieved, without using up too much of the server’s physical memory.

To address this type of situation, Oracle created a way to let the instance automatically manage the working areas of the database sessions. Oracle 9i now has the ability to reserve a large chunk of working area space in memory and to let the instance dynamically change the working area allocations depending on the session’s operations. One session that needs 1MB of sort space and 4MB of hash area space would fit well into an allocation as defined above. A second session that needs 4MB of sort space and 1MB of hash area space would also fit well into a similar allocation since Oracle 9i now has the capability to dynamically change these working area allocations depending on the usage. Oracle 9i uses the PGA_AGGREGATE_TARGET initialization parameter to define the total amount of PGA reserved memory.

Initial Setup

To begin using this new Oracle 9i feature, you need to set the PGA_AGGREGATE_TARGET initialization parameter. This parameter can be set without restarting the Oracle instance. As a guideline, Oracle recommends initially setting this parameter to 16% of your server’s physical memory for OLTP systems and 40% of your server’s physical memory for DSS systems. Like any other memory configuration guidelines from Oracle Corp, this is just a starting place. You will most likely want to tune this setting depending on the usage of your system’s resources. My server has 1GB of physical memory. Using the above guidelines, I will initially set my PGA_AGGREGATE_TARGET initialization parameter. You can see an example of this in Figure 1.

Figure 1. – Setting PGA_AGGREGATE_TARGET

ORA9I SQL> alter system set pga_aggregate_target=160M;

System altered.

ORA9I SQL> show parameter pga_aggregate_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
pga_aggregate_target                 big integer 167772160

With this parameter set, Oracle will now automatically perform dynamic working area memory management.

Please note that the amount of memory set aside for the PGA_AGGREGATE_TARGET is for all server processes, not for each server process. This parameter can be set to zero to turn off dynamic working area memory management. The acceptable range of values is between 10MB and 4096GB – 1.

Tuning PGA_AGGREGATE_TARGET

To illustrate how to tune this parameter, I will set my PGA_AGGREGATE_TARGET to the minimum value allowed, 10MB and run a load simulation on my instance. We want to determine when the PGA_AGGREGATE_TARGET is too low to give optimal performance and too high so as to not waste allocated memory.

Oracle 9i gives us many different views to query to see how well our dynamic working area memory management is performing. Oracle 9i has added a few statistics to V$SYSTAT and V$SESSTAT.

Figure 2. – V$SYSTAT with 10MB PGA Target

ORA9I SQL> select name,value from v$sysstat
  2>  where name like 'workarea executions%';

NAME                                          VALUE
---------------------------------------- ----------
workarea executions - optimal                   510
workarea executions - onepass                     1
workarea executions - multipass                   4

The query in Figure 2 shows us how many operations, or executions, were performed in the work areas. These executions fall into three categories. The optimal executions are those operations that were performed entirely in memory. As the name suggests, this is the most favorable type of execution. If the operation was too big to be performed in memory, then part of the operation spills onto disk. If only one pass was needed on disk, then this execution is noted in the onepass statistic. If more than one pass was needed on disk, then this execution is noted in the multipass statistic. Ideally, all executions should be in the optimal statistic and the statistics for onepass and multipass should be zero. From the query in Figure 2, I can see that my value for PGA_AGGREGATE_TARGET is too small.

Oracle 9i includes a new view called V$PGASTAT. This view can give you additional statistics on how well the dynamic working area memory management is performing.

Figure 3. V$PGASTAT with 10MB PGA Target

ORA9I SQL> select * from v$pgastat;

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter             10485760 bytes
aggregate PGA auto target                   4248576 bytes
global memory bound                          524288 bytes
total PGA inuse                             5760000 bytes
total PGA allocated                        10342400 bytes
maximum PGA allocated                      42925056 bytes
total freeable PGA memory                     65536 bytes
PGA memory freed back to OS                 9306112 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas          631808 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas        529408 bytes
over allocation count                          9201
bytes processed                            55100416 bytes
extra bytes read/written                  159971328 bytes
cache hit percentage                          25.61 percent

16 rows selected.

The first line of output in Figure 3 shows that my PGA_AGGREGATE_TARGET is currently set to 10MB. Some parts of the PGA are used for non-tunable information such as session context information and other overhead. The rest of the PGA memory footprint is dynamically tunable and is indicated by the aggregate PGA auto target statistic. The value in the second line of output of Figure 3 should not be significantly smaller than the value in the first line, as it is above. If this value is too small, then Oracle does not have enough memory to dynamically adjust.

Oracle 9.2 includes two additional rows of output to V$PGASTAT. They are over allocation count and cache hit percentage. If Oracle determines that it cannot honor the setting for PGA_AGGREGATE_TARGET, then it needs to allocate additional memory. The number of times Oracle detects this condition since instance startup is noted by the over allocation count statistic. Ideally, this value should be zero. The cache hit percentage statistic shows a hit ratio on the number of bytes where optimal executions were performed compared the total number of bytes for all executions, optimal, one-pass, and multi-pass. If all executions where optimal, then this statistic should be 100%.

It should be obvious from the queries in Figures 2 and 3 that the PGA_AGGREGATE_TARGET is under allocated. The question that remains is how much to increase this parameter to obtain optimal performance without wasting allocated memory? Oracle 9i includes a new V$PGA_TARGET_ADVICE view to help us answer this question. In order to use this view, one needs to ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. Let’s look at Figure 4 to see what advice Oracle can give us if we change our PGA_AGGREGATE_TARGET to a different value.

Figure 4. – V$PGA_TARGET_ADVICE with 10MB PGA Target

ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
  2>              bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
  3>              estd_pga_cache_hit_percentage as est_hit_pct,
  4>              estd_overalloc_count as est_overalloc
  5>        from v$pga_target_advice;

TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
            10        51766272           80197632          39             4
            12        51766272           79773696          39             3
            14        51766272           79773696          39             3
            16        51766272           79773696          39             2
            18        51766272           79773696          39             2
            20        51766272           79773696          39             2
            30        51766272           19943424          72             0
            40        51766272           19943424          72             0
            60        51766272           19943424          72             0
            80        51766272           19943424          72             0

10 rows selected.

The output in Figure 4 shows us statistics that Oracle estimates would happen under our current workload if the PGA were changed to a different target size. Using this information, we should first attempt to reduce the estimated over allocations to zero. The first target size listed where this value is eliminated is 30MB. Notice that as we increase the target size, the estimated cache hit percentage increases, and the estimated number of bytes read and written in one-pass or multi-pass executions falls as well. Knowing that I need to increase this value, and looking at the target advice, I’m going to set PGA_AGGREGATE_TARGET to 80MB and restart the instance to clear all statistics. I’ll then rerun the same simulated load on the database.

I expect that I will still have some executions that will require reads and writes to disk since the read/write estimate show in Figure4 is non-zero for a 80MB PGA target. After restarting the instance and running the simulated load, I can see that this is true from the query in Figure 5.

Figure 5. – V$SYSTAT with 80MB PGA Target

ORA9I SQL> select name,value from v$sysstat 
  2> where name like 'workarea executions%';

NAME                                          VALUE
---------------------------------------- ----------
workarea executions - optimal                   511
workarea executions - onepass                     4
workarea executions - multipass                   0

I have eliminated the multi-pass executions, but a few one-pass executions remain. Let’s now check the results in V$PGASTAT show in Figure 6.

Figure 6. – V$PGASTAT with 80MB PGA Target

ORA9I SQL> select * from v$pgastat;

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter             83886080 bytes
aggregate PGA auto target                  70290432 bytes
global memory bound                         4194304 bytes
total PGA inuse                             5790720 bytes
total PGA allocated                        10792960 bytes
maximum PGA allocated                      15750144 bytes
total freeable PGA memory                    196608 bytes
PGA memory freed back to OS                11403264 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas         4319232 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                             0
bytes processed                            47950848 bytes
extra bytes read/written                   39481344 bytes
cache hit percentage                          54.84 percent

We can see in Figure 6 that we have eliminated the over allocation count statistic. The auto target statistic is very close to the target parameter. So we are getting closer. But the cache hit percentage is still far away from 100% and there are a large number of extra bytes read and written. We will look for advice for our next setting in Figure 7.

Figure 7. V$PGA_TARGET_ADVICE with 80MB PGA Target

ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
  2  bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
  3  estd_pga_cache_hit_percentage as est_hit_pct,
  4  estd_overalloc_count as est_overalloc
  5  from v$pga_target_advice;

TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
            10        45456384           84205568          35             2
            20        45456384           78962688          37             0
            40        45456384           19740672          70             0
            60        45456384           19740672          70             0
            80        45456384           19740672          70             0
            96        45456384           19740672          70             0
           112        45456384           19740672          70             0
           128        45456384           13095936          78             0
           144        45456384           13095936          78             0
           160        45456384           13095936          78             0
           240        45456384           13095936          78             0
           320        45456384                  0         100             0
           480        45456384                  0         100             0
           640        45456384                  0         100             0

The query output in Figure 7 shows that we can eliminate the extra read and write bytes if we allocate 320MB to the PGA_AGGREGATE_TARGET. We can also hit our 100% cache hit ratio. We can further see that allocating 640MB or even 480MB of memory would not help us achieve any better performance. These settings would waste memory. Our next step would be to change the PGA_AGGREGATE_TARGET parameter and then perform the same queries at regular intervals to ensure that we are achieving optimal performance.

Conclusion

The benefit of letting Oracle 9i dynamically manage your working area memory is a great tool for the DBA to employ. The DBA does not have to worry about setting each of the *_AREA_SIZE parameters correctly. 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.

References

Oracle 9i Database Reference
Metalink Note: 148346.1 Oracle 9i Monitoring Automated SQL Execution Memory Management
Metalink Note: 223730.1 Automatic PGA Memory Management in 9i