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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

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

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