I have seen
even cases when these values were even bigger after the
instance was restarted with P_A_T set at 4GB.

The maximum
value for parallel operations changed from 30% to 50%
PGA_AGGREGATE_TARGET/DOP. Also the DOP changed. When DOP <=5
then _smm_max_size is used, otherwise _smm_px_max_size/DOP
limits the maximum memory usage. . .

Obviously the
default value for PGA maximum size of 200M and 50% of that value
as maximum value for the single SQL operator were causing
performance bottlenecks in previous releases of the Oracle
database. Therefore Oracle changed the defaults in release 10.2
and made them much more aggressive.

In
Oracle9i/10gR1 when one has to perform a big sort or
hash-join or bitmap operation, he can change the hidden
parameters like _PGA_MAX_SIZE, _SMM_PX_MAX_SIZE for parallel
execution when DOP > 6 or _SMM_MAX_SIZE to be able to use
the available, still unused memory on the system for large
SQL operators.

It is important to
recall that _PGA_MAX_SIZE parameter defines the maximum size of
process’ global area (PGA). Because an SQL statement can have
more than one SQL operator like sort, group-by, hash-join, etc.
the size of working area for single SQL operator is limited by
default to 50% of _PGA_MAX_SIZE.”

��

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.