[Editor's note: This article was corrected and expanded on 23 August 2005 and again on 5 September 2005. Make sure to heed the warning in the text: "These are undocumented parameters and they should not be used unless you are willing to accept full responsibility for any issues."]
[Author's note: In response to this tip, one reader has repeatedly demanded that SearchOracle remove this tip, claiming that my observations are wrong, and citing as evidence a single test case using the
MTS. Of course, an artificial test case cannot be generalized to all Oracle databases, and this is why real-world experience is so valuable. The information in this tip was gleaned from my real-world experiences and those of my co-workers and colleagues. Remember, there are hundreds of default installations of Oracle and obviously, your mileage may vary. I have enhanced this tip again, including more clarifications and a 230 page artificial test case to show that my original tip observations were substantially correct. Note that this tip contains unsupported secrets that are not published in the Oracle documentation and you should carefully read the MetaLink references, test them on your own database, and open an iTar before trying any of these powerful techniques. – Donald K. Burleson, 9/5/2005]
Almost every Oracle professional 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
There are 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 200 megabytes for sorting or hash joins. The algorithm further reduces this to (200/2) for sorts so the actual limit for pure sorts will be 100 megabytes.
These restrictions were made to ensure that no large sorts or hash joins hog the PGA RAM area, but there are some secrets to optimize the PGA. For example, the following set of parameters may be mutually-exclusive:
- sort_area_size=1048576 <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto, unless you are using a specialized feature such as the MTS. If dedicated server connections are used, the sort_area_size parameter is ignored.
- pga_aggregate_target = 500m <-- The maximum default allowed value is 200 megabytes, this limits sorts to 25 megabytes (5% of 500m).
- mts_servers<>0 <-- If Multi-threaded server is being used, the pga_aggregate_target setting would be ignored in all versions except Oracle10g.
(Note: there may be some cases where sort_area_size is used in Oracle utilities, but these have not been documented, even with pga_aggregate_target.)
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 – this 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, "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 sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. 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:
|_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 unsupported parameters and they should not be used unless you have tested their behavior on your own database and you are willing to accept full responsibility for any issues.
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, 64 bit 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
The system has a pga_aggregate_target setting of 1 gigabyte and the undocumented parameters are at their default settings.
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 above, the following settings would increase the default sizes for large sorts and hash joins while limiting those for parallel sorts.
- pga_aggregate_target = 4g
- _pga_max_size = 400m
- _smm_px_max_size = 333m
With these hidden parameters set we see significant size increase for serial sorts and a throttling effect for parallel queries and sorts. To see a reproducible, artificial test case demonstrating sort throttling, Mike Ault has prepared a 230 page artificial test case: Validation of Sort Sizes in a Linux Oracle10g Database. However, bear in mind that it only valid for a specific release of Oracle10g, on a specific hardware and OS environment, and not using any optional features such as the MTS.
- A RAM sort or hash join may now have up to the full 200 megabytes (5% of pga_aggegate_target) a 400% increase over a 1 gigabyte pga_aggregate_target setting. With the default settings, only a 200% (100 megabyte size) increase would be possible.
- Parallel queries are now limited to 333 megabytes of RAM (30% of pga_aggegate_target or _smm_px_max_size), such that a DEGREE=4 parallel query would have a maximum of 83 megabytes (333 meg/4) per slave which may actually be less due to internal sizing algorithms that set the memory increments used in setting sort areas. This throttling is to prevent one parallel query using all available memory since _smm_px_max_size would default to 1.2 gigabytes with the setting for pga_aggregate_target at 4 gigabytes.
- You must be careful in setting the PGA_AGGREGATE_TARGET to greater than the available memory, calculate the maximum number of users who would be sorting/hashing and multiple that times the predicted size to get your actual limitations otherwise ORA-4030 errors or swapping may occur.
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. 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.
- From Metalink Note:223730.1 we see that there are hard-limits for physical RAM allocation. "So for example if we have an Oracle instance configured on system with 16G of Physical memory, then the suggested PGA_AGGREGATE_TARGET parameter value we should start with incase we have OLTP system is (16 G * 80%)*20% ~= 2.5G and incase we have DSS system is (16 G * 80%)* 50% ~= 6.5 G."
- 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.
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."
- Metalink Note:223299.1: "If PGA_AGGREGATE_TARGET is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored." . . . "If PGA_AGGREGATE_TARGET is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored. . . If PGA_AGGREGATE_TARGET is set in init.ora, then WORKAREA_SIZE_POLICY defaults to AUTO."
- Metalink Note: 223730.1: "1- When we set the PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to auto then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters."
- Metalink Note: 30918.1: "Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility."
- This Oracle-owned web page describes the PGA sizing rules-of-thumb:
'When you set PGA_AGGREGATE_AREA then no need of setting these parameter, Let me expand that:
if ( version = 9i ) then if ( pga_aggregate_target is set and workarea_size_policy is auto ) then if ( connection is dedicated server ) then these parameters (sort area [retained] size) need not be set else they do need to be set, shared server uses sort area size end if else they do need to be set end if elsif (version > 9i ) then if ( pga_aggregate_target is set and workarea_size_policy is auto ) then these parameters (sort area [retained] size) need not be set else they do need to be set end if end if"
- This Oracle-owned web page verifies that sort_area_size and hash_area_size are not relevant when pga_aggregate_target are set: "In 9i though, you should be using workarea_size_policy = auto and pga_aggregate_target -- then sort_area_size/hash_area_size is not even relevant."
- Reproducible test case: Observations of Sort Sizes in a Linux Oracle10g Database.
This was first published in August 2005