Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

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


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:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.