Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Important caveats in PGA management

Overriding the built-in safeguards of pga_aggregate_target can make more efficient use of RAM resources in cases where large RAM regions are available on the database server. When used with care (and the blessing of Oracle Technical Support) it can often make sense to over-ride these default values to make better use of expensive RAM resources. There is also lots of evidence that changing these parameters will have a positive effect of large, batch-oriented Oracle jobs, but you must be very careful to fully understand the limitations of the PGA parameters:

·       Do not adjust any hidden parameters without opening an iTar and getting the consent and advice of Oracle Technical Support. These are undocumented, hidden parameters you must be willing to accept full responsibility for any issues.

·       Some hidden parameters have no effect when set at session level and you must issue "alter system" commands for them to take effect.

·       These PGA rules no not apply to shared server environments using Oracle multi-threaded server (MTS). However, the vast majority of Oracle shops do not use the MTS.

·       Each process (with one PGA area) may have multiple work areas. For example, a query might perform a parallel full-table scan followed by an ORDER BY sort, having one PGA and two workareas. The _pga_max_size controls the PGA size and _smm_max_size controls the size for each workarea.

 

The Ion for Oracle tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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

 

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks