Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

sizing pga_aggregate_target and pga_max_size for Oracle

Oracle Tips by Burleson Consulting
Don Burleson

Almost every Oracle professionals agrees that the old-fashioned sort_area_size and hash_area_size parameters imposed a cumbersome one-size-fits-all approach to sorting and hash joins.  Different tasks require different RAM areas, and the trick has been to allow 'enough' PGA RAM for sorting and hash joins without having any high-resource task 'hog' all of the PGA, to the exclusion of other users. 

Oracle9i introduced the pga_aggregate_target parameters to fix this resource issue, and by-and-large, pga_aggregate_target works very well for most systems.  You can check your overall PGA usage with the v$pga_target_advice advisory utility or a STATSPACK or AWR report.  High values for multi-pass executions, high disk sorts, or low hash join invocation might indicate a low resource usage for PGA regions.

Automatic Memory Management (AMM) replaces pga_aggregate_target,  For 10g see sga_max_size, sga_target and 11g and beyond, see my notes on the memory_target_parameter.

For monitoring pga_aggregate_target Oracle provides a dictionary view called v$pgastat. The v$pgastat view shows the total amount of RAM memory utilization for every RAM memory region within the database.

 

You can also increase your pga_aggregate_target above the default 200 megabyte setting by setting the hidden _pga_max_size parameter.
 

  • _pga_max_size = 1000m
  • _smm_px_max_size = 333m

With pga_aggregate_target and _pga_max_size hidden parameter set to 1 meg we see a 5x improvement over the default for parallel queries and sorts:

  • A RAM sort or hash join may now have up to 50 megabytes (5% of pga_aggegate_target).
     
  • Parallel queries may now have up to 330 megabytes of RAM (30% of pga_aggegate_target), such that a DEGREE=4 parallel query would have 83 megabytes (333 meg/4).

 

In Oracle 10g we see that the AWR has a time-series table called dba_hist_pgastat to measure PGA usage over time.  You can get complete 10g scripts for monitoring AWR dba_hist_pgastat here:
 

Advanced Oracle Monitoring and Tuning Script Collection


This information can tell you the high water mark of RAM utilization, and allow you to size RAM memory demands according to the relative stress on the system. Here is a simple query against v$pgastat:


column name format a40
column value format 999,999,999


select name,value
from
v$pgastat
order by
value desc;

 

From this listing, you can see the value of pga_aggregate_target and the high water marks for all RAM memory areas used by this instance. But let's take a look at optimal, one pass, and multipass RAM memory executions.

When an Oracle process requires an operation, such as a sort or a hash join, it goes to the shared RAM memory area within pga_aggregate_target region and attempts to obtain enough contiguous RAM frames to perform the operation. If the process is able to acquire these RAM frames immediately from pga_aggregate_target, it is marked as an "optimal" RAM access.

 

If the RAM acquisition requires a single pass through pga_aggregate_target, the RAM memory allocation is marked as one pass. If all RAM is in use, Oracle may have to make multiple passes through pga_aggregate_target to acquire the RAM memory. This is called multipass.

Remember, RAM memory is extremely fast, and most sorts or hash joins are completed in microseconds. Oracle allows a single process to use up to 5 percent of the pga_aggregate_target, and parallel operations are allowed to consume up to 30 percent of the PGA RAM pool.

Pga_aggregate_target "multipass" executions indicate a RAM shortage, and you should always allocate enough RAM to ensure that at least 95 percent of connected tasks can acquire their RAM memory optimally.

You can also obtain information about workarea executions by querying the v$sysstat view shown here:


col c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count' format 999,999,999
col c3 heading 'Percentage' format 99
 

select name c1,cnt c2,decode(total, 0, 0, round(cnt*100/total)) c3
from
(
select name,value cnt,(sum(value) over ()) total
from
v$sysstat
where

name like 'workarea exec%'
);

 

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.