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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








2007 updates to Oracle PGA behavior

Oracle Database Tips by Donald Burleson

Oracle PGA management is one of the most complex areas of DBA management, and it's very difficult to codify for many reasons:

  • The PGA behavior is influenced by bugs

  • Test cases can be biased by hidden settings

Today, the general consensus on PGA management is that the DBA must try to optimize their PGA regions to minimize disk sorts and give Oracle an opportunity to replace nested loop joins with hash joins, when appropriate.

In addition, many shops undertake to override the PGA default values to "supersize" their PGA regions during batch processing, overtaking the built-in throttles.  In my article Oracle PGA pga_max_size undocumented parameter, I note that over-riding the PGA governors can allow a single-threaded job to perform at lightening speeds.

If you have a limited number of active sessions you may wish to override the PGA governor that only allows any single task to consume 5% of the total PGA.  Laurent Schneider notes in Oracle MOSC that overriding the PGA defaults made a large batch processes run more than 8x faster:

"I set appropriate values for pga_aggregate_target and _pga_max_size...

alter system set pga_aggregate_target=6G; 
alter system set "_pga_max_size"=2000000000; 

...and I gave the query some hints "NOREWRITE FULL USE_HASH ORDERED". As a result, it boosted my query performance from 12 hours to 1.5 hour."

PGA usage Note:  There are other tricks for overcoming the built-in governor for PGA usage.  Oracle has a 5% limit for any individual process, and by using parallel DML any single batch job can consume 30% of the PGA without touching any of the undocumented parameters.  Oracle author Laurent Schneider noted:

?I finally opted for a more maintainable solution.  No more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 CPU server.   As discussed in MOSC thread 460157.996, a supported way to increase the maximum PGA memory per single SQL query is to increase the degree of parallelism."

New Observations on PGA behavior

Charles Hooper (IT Manager/Oracle DBA for K&M Machine-Fabricating, Inc.), did this excellent test of the PGA parameters, spending more than 10 hours to illustrate the behavior of the PGA parameters in 10g.  Charles concludes that "odd quirks" makes definitive test-case proofs very difficult:
In summary, as the "Oracle Database Performance Tuning Guide 10g Release 2" Pg 7-38 (PDF page 146) documentation states, "sizing of work areas for all sessions becomes automatic and the *_AREA_SIZE parameters are ignored by all sessions running in that mode."  There is apparently an odd quirk that once in a while, the first time a SQL statement is parsed, a sort to disk may be required, at least under the base patch of Oracle 
This lead me, incorrectly, to believe that setting the SORT_AREA_SIZE to a larger value and re- executing the query actually removed the sort to disk - but it was actually the second parse that resulted in the removal of the sort to disk.  This test case disproves my suggestion that the SORT_AREA_SIZE has any impact on Oracle when all sessions are set to auto for the WORKAREA_SIZE_POLICY. 
It is possible to modify the WORKAREA_SIZE_POLICY at the session level, and then the SORT_AREA_SIZE setting takes effect for that session.

Here are my related notes on PGA management

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.