| |
 |
|
2007 updates to Oracle PGA behavior
Oracle Tips by Burleson Consulting
|
Oracle PGA management is one of the most complex areas of
DBA management, and it's very difficult to codify for many reasons:
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
MetaLink 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."
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.
Beware of "proofs"
Every Oracle database is different, each
with unique settings and configurations that make it virtually impossible to
extrapolate from a single example. One of the most onerous examples of
using a test
case to "prove" the behavior of the SGA cloaked the real behavior by
employing shared servers, without disclosing it up-front. This led to
massive confusion within the Oracle community, and we see this comment by
Bob Jones regarding the rigged test case of PGA behavior:
Wow, this guy really has too much time
in his hands, or he just hate this Don guy too much. It is pointless to
set _AREA_SIZE when using PGA_AGGREGATE_TARGET anyway.
WIP! I will continue to add to
this page as new observations and real-world evidence is disclosed.
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. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
