Donald K. Burleson Oracle Tips

The new Oracle9i pga_aggregate_target parameter

Oracle9i has introduced a new parameter called pga_aggregate_target and I have notes on related pga_aggregate_target details here:

When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle9i will ignore all of the PGA parameters in the init.ora file, including sort_area_size and sort_area_retained_size. 

Oracle recommends that the value of pga_aggregate_target be set to the amount of remaining memory (less a 20% overhead for other UNIX tasks) on the UNIX server after the instance has been started

Once the pga_aggregate_target has been set, Oracle will automatically manage PGA memory allocation, based upon the individual needs of each Oracle connection. 

The PGA information is kept in a new view called v$pgastat.  This view can tel us the utilization of PGA memory so that we can adjust it according to our data activity.

column name  format a30
column value format 999,999,999

select
   name,
   value
from
   v$pgastat
;

Here is a sample listing from this script.

NAME                                                   VALUE    

------------------------------------------------------ ----------

aggregate PGA auto target                             736,052,224

global memory bound                                        21,200

total expected memory                                     141,144

total PGA inuse                                        22,234,736

total PGA allocated                                    55,327,872

maximum PGA allocated                                  23,970,624

total PGA used for auto workareas                         262,144

maximum PGA used for auto workareas                     7,333,032

total PGA used for manual workareas                             0

maximum PGA used for manual workareas                           0

estimated PGA memory for optimal                          141,395

maximum PGA memory for optimal                        500,123,520

estimated PGA memory for one-pass                         534,144

maximum PGA memory for one-pass                        52,123,520

The most important metric in v$pgastat is the “Estimated PGA memory for optimal/one-pass” executions. This statistic estimates how much PGA memory is required to execute all task connections RAM demands in optimal mode. 

Remember, when Oracle9i experiences a memory shortage, it will invoke the multi-pass operation.  Oracle DBA’s will increase pga_aggregate_target to the estimated value for optimal one-pass.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm