| |
Oracle 10g
release 2 changes to pga_aggregate_target limits
In a paper titled “Advanced
Management Of Working Areas in Oracle 9i/10g”, author Joze
Senegacnik delivers a must-read paper on internals for Oracle PGA
management in 10g release 2, including important changes to the
sizing rules for pga_aggregate_target:.
(download password is “network”)
“In the latest
release 10.2 Oracle changed these default values. The memory
allocated to a single SQL operator (_smm_max_size) is limited
to:
-
for P_A_T <=
500MB the parameter _smm_max_size = 20% of P_A_T
-
for P_A_T
between 500MB and 1000MB the parameter _smm_max_size = 100M
-
for P_A_T
betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size
= 10% of P_A_T
-
for P_A_T >
2560MB (2,5GB) the parameter _smm_max_size = 262,060 MB
(~0,25GB)
-
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.”
 |
|
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. |
|
|
|
