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