Donald K. Burleson Oracle Tips

Using the New Oracle v$pgastat view

The v$pgastat view provides instance level summary statistics on the PGA usage and the automatic memory manager. The following script provides excellent overall usage statistics for all Oracle9i connections.

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

select
   name,
   value
from
   v$pgastat
;

The output of this query might look like the following:

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

In the above display from v$pgastat we see the following statistics.

1. Aggregate PGA auto target – This column gives the total amount of available memory for Oracle connections.  As we have already noted, this value is derived from the value on the init.ora parameter pga_aggregate_target.

2. Global memory bound – This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below one megabyte, then you should increase the value of the pga_aggregate_target parameter.

3. 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.

4. 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.

5. 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, he will invoke the multi-pass operation.  This statistics is critical for monitoring RAM consumption in Oracle, and most Oracle DBA’s will increase pga_aggregate_target to this value.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (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_1002_oracle_tuning_definitive_reference_2nd_ed.htm