Two useful hidden parameters: _smm_max_size and _pga_max_size.

You may have heard of the great “Burleson” vs “Lewis” controversy about the spfile/init hidden pga parameters. You can have a glimpse at the battlefield on http://www.jlcomp.demon.co.uk/untested.html (Jonathan Lewis side) and to be fair on the opposite side http://www.dba-oracle.com/art_so_undocumented_pga_parameters.htm. If you have the courage to fight your way in the intricacies of the arguments, and also taking into account that 1) this war only seems to apply to 9i 2) the above url (at least the one from Don Burleson) may have been rewritten since comments were made by Jonathan Lewis, you may be left with a sense of misunderstanding as I was when I went through it.

1) Is it worth fiddling with those undocumented parameters ?

The answer is yes (at least for me). Not that I am a great fan of setting those parameters for fun in production, it is just that I encountered thrice in a year a good reason for setting them.

2) How does it work ?

I tried to understand from the various Metalink and searches across the web what their meaning was, and I then verified the values hoping not to miss something.

I’ll avoid a discussion over parallel operations, as the settings are more complex and depends upon the degree of parallelism. I’ll spend some times investigating this but for now I’ll stick with what I tested.

1. Context

The advent of automatic pga management (if enabled!) in Oracle 9i was meant to be a relieve to the *_area_size parameters dictating how much a sort area could reach before the temp tbs would be used. Basically, the sort area sizes where acting as a threshold: your sort was performed in memory if the required sort memory was smaller than the threshold, and it went on disk if larger. The trouble with this strategy was that the sort area had to be small enough to accommodate many processes sorting at the same time, but on the other hand a large sort alone on the instance could only be using up to the sort area size before spilling on disk. The sort area sizes mutualized under the pga umbrella just removed these shortcomings. However, the Oracle designers had to cope with the possibility of a process hogging the sort memory, leaving no space for others. This is why some limitations to the sort memory available to a workarea and to a single process were put in place, using a couple of hidden parameters:

_smm_max_size: Maximum workarea size for one process

_pga_max_size: Maximum PGA size for a single process

The sorts will go on disk if any of those two thresholds are crossed.

2. Default values

9i (and probably 10gR1, which I did not test):

_pga_max_size: default value is 200MB.

_smm_max_size : default value is the least of 5% of pga_aggregate_target and of 50% of _pga_max_size. A ceiling of 100MB also applies. The ceiling is hit when the pga_aggregate_target exceeds 2GB (5% of 2GB = 10MB) or

when

_pga_max_size is set to a higher value than the default AND pga_aggregate_target is lower than 2GB.

10gR2

pga_aggregate_target now drives in most cases _smm_max_size:

pga_aggregate_target <=500MB, _smm_max_size = 20%* pga_aggregate_target

pga_aggregate_target between 500MB and 1000MB, _smm_max_size = 100MB

pga_aggregate_target >1000MB, _smm_max_size = 10%* pga_aggregate_target

and _smm_max_size in turns now drives _pga_max_size: _pga_max_size = 2 * _smm_max_size

A pga_aggregate_target larger than 1000MB will now allow much higher default thresholds in 10gR2: pga_aggregate_target set to 5GB will allow an _smm_max_size of 500MB (was 100MB before) and _pga_max_size of 1000MB (was 200MB).

You can get the hidden parameter values by querying x$ksppcv and x$ksppi as follows:

select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_smm_max_size’;

select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_pga_max_size’;