Undocumented secrets for super-sizing your PGA

Almost every Oracle professionals agrees that the old-fashioned sort_area_size and hash_area_size parameters imposed a cumbersome one-size-fits-all approach to sorting and hash joins. Different tasks require different RAM areas, and the trick has been to allow "enough" PGA RAM for sorting and hash joins without having any high-resource task "hog" all of the PGA, to the exclusion of other users. Oracle9i introduced the pga_aggregate_target parameters to fix this resource issue, and by-and-large, pga_aggregate_target works very well for most systems. You can check your overall PGA usage with the v$pga_target_advice advisory utility or a STATSPACK or AWR report. High values for multi-pass executions, high disk sorts, or low hash join invocation might indicate a low resource usage for PGA regions. Let's take a look at the issues surrounding the hidden limits of pga_aggregate_target. The limits of sorting and hashing Many Oracle professionals do not know the important limitations of pga_aggregate_target: The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size. No RAM sort may use more than 5% of pga_aggegate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 10 megabytes for sorting or hash joins. These restrictions were made to ensure that no large sorts or hash joins hog the PGA RAM area, but there are some secrets to optimizer the PGA. For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management. sort_area_size=1048576 <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto. pga_aggregate_target = 500m <-- The maximum default allowed value is 200 megabytes Let's take a closer look at expert tricks for advanced PGA management. Hidden parameters for Oracle PGA regions With proper understanding (and knowing that these undocumented parameters are not supported by Oracle), you can adjust your PGA regions to allow for system-specific sorting and hash joins. _pga_max_size – his hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target. _smm_px_max_size – This parameter is used for Oracle parallel query, and defaults to 30% of the pga_aggregate_target setting, divided by degree of parallelism (as set by a PARALLEL hint, the "alter table xxx parallel" command, or the parallel_automatic_tuning initialization parameter). For example, by default a DEGREE=4 parallel query would have a maximum value of 15 megabytes. Remember, parallel full-table scans bypass the data buffers and store the incoming data rows in the PGA region and not inside the data buffers (as defined by the db_cache_size parameter). We also see these additional undocumented parameters: Parameter Name Description _smm_advice_enabled if TRUE, enable v$pga_advice _smm_advice_log_size overwrites default size of the PGA advice workarea history log _smm_auto_cost_enabled if TRUE, use the AUTO size policy cost functions _smm_auto_max_io_size Maximum IO size (in KB) used by sort/hash-join in auto mode _smm_auto_min_io_size\ Minimum IO size (in KB) used by sort/hash-join in auto mode _smm_bound overwrites memory manager automatically computed bound _smm_control provides controls on the memory manager _smm_max_size maximum work area size in auto mode (serial) _smm_min_size minimum work area size in auto mode _smm_px_max_size maximum work area size in auto mode (global) _smm_trace Turn on/off tracing for SQL memory manager WARNING - These are undocumented parameters and they should not be used unless you are willing to accept full responsibility for any issues. Super-size me For certain Oracle applications the Oracle professional will want to allow individual tasks to exceed the default limits imposed by Oracle. For example, PC-based Oracle servers (1 or 2 CPU's with 8 gigabytes of RAM) will often have unused RAM available. For example, a fully-cached 5 gigabyte database on an 8 gigabyte dedicated Oracle server will have approximately 1 gigabyte available for the PGA (allowing 20% for the OS and other SGA regions): O/S - 1.6 gig SGA - 5 gig PGA Space - 1 gig Total - 8 gig While it is unusual for an online system to require super-sized regions for sorting (because the result sets for online screens are normally small), there can be a benefit to having large RAM regions available for the Oracle optimizer. The Oracle cost-based optimizer will determine whether a hash join would be beneficial over a nested-loop join, so making more PGA available for hash joins will not have any detrimental effect since the optimizer will only invoke a super-sized hash join if it is better than a nested-loop join. In a system like the example in Figure 1, the following settings would increase the default sizes for large sorts and hash joins. pga_aggregate_target = 1000m _pga_max_size = 1000m _smm_px_max_size = 333m With these hidden parameters set we see a 5x large size increase for parallel queries and sorts: A RAM sort or hash join may now have up to 50 megabytes (5% of pga_aggegate_target) a 5x increase. Parallel queries may now have up to 330 megabytes of RAM (30% of pga_aggegate_target), such that a DEGREE=4 parallel query would have 83 megabytes (333 meg/4). In conclusion, 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.

REFERENCES

Undocumented secrets for super-sizing your PGA by Don Burleson