Christian Bilien’s Oracle performance and tuning blog

May 1, 2007

Two useful hidden parameters: _smm_max_size and _pga_max_size.

Filed under: Oracle — christianbilien @ 8:07 pm

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’;

About these ads

6 Comments »

  1. Thanks for your article! I am working on a rather large sort, and I found the information really helpful – a real lifesaver! I have a question for you: we currently have PGA_AGGREGATE_TARGET set to 4.2G and _pga_max_size 1G _smm_max_size 500M. I’m sure the sort I’m running cannot be performed in memory at those levels, but instead of seeing disk sorts, I’m getting 4030 errors:
    -4030 ORA-04030: out of process memory when trying to allocate
    8192 bytes (sort subheap,sort key)
    Any idea why this might be happening? We’re running Oracle 9.2.0
    Thanks very much again for the great info!
    -Steve

    Comment by Steve K — May 17, 2007 @ 4:14 pm

  2. Hi Steve,

    Sorry for not replying until now: I found your comment in the spam area while routinely checking for spams to delete. I am not sure why wordpress classified it that way.

    As I’m sure you guessed, you are running out of memory. Did you solve your problem ?

    If no: which Unix platform/version/oracle version are you running on ? What are your RAM and sga size ? What is the ulimit ?

    Christian

    Comment by christianbilien — May 31, 2007 @ 4:06 pm

  3. Christian,
    Thanks for the reply. Still having memory issues. We’re running Oracle 9.2.6.0 on HP/UX 11.0. I believe the RAM on the box is about 12G. I get the 4030 erros with the following: PGA_AGGREGATE_TARGET = 8.2G, _pga_max_size = 4.2 G and _smm_max_size = 2.1G (apparently the maximum for Oracle 9.2).

    When the settings are as follows, I do not get the errors: PGA_AGGREGATE_TARGET = 4.2G, _pga_max_size = 1G, _smm_max_size=500M. But I do get alot of disk sorting with these settings that results in unacceptable performance.

    I’m not sure what the ulimit is (probably the default), but this is the only query running on the instance when I get the 4030s.

    Any ideas/help would be greatly appreciated.

    Thanks again for a great article!
    Steve

    Comment by steve k — July 29, 2007 @ 5:36 am

  4. Steve,

    1. What are your SGA parameters ? It is likely that sga + allocated sort areas > memory. sga gets allocated upfront, but not the pga.
    2. Check swap area I/Os: use vmstat or Glance
    3. Check memory size : dmesg| grep Physical

    Christian

    Comment by christianbilien — July 29, 2007 @ 8:25 am

  5. Hello Christian,
    You have written that those params are driven from pga_aggregate_target.
    But if i have following results from your query :

    NAME VALUE VALUE_F
    _pga_max_size 1073741824 1,073,741,824.000
    _smm_min_size 1024 1,024.000
    _smm_max_size 524288 524,288.000
    _smm_px_max_size 536870912 536,870,912.000

    It means that (because of _smm_max_size=524288) i will have workarea operations spilled to disk if their size will be greater than 500 kB ?
    Or this _smm_max_size is default value and my workarea max size will be 10%* pga_aggregate_target in case of pga_aggregate_target=5GB ?

    Best Regards Arek Masny

    Comment by Anonymous — May 19, 2009 @ 4:29 pm

  6. […] Work Area Memory Size Used By Sessions? Oracle Database Reference 11.2: pga_aggregate_target Cristian Bilien: two useful hidden parameters _smm_max_size and _pga_max_size MOS Note 1372904.1: ORA-600 [kcblin_3] when _smm_max_size is greater than […]

    Pingback by PGA tour: Maximize workareas for ETL in Oracle 11.2.0.3 — December 12, 2013 @ 10:25 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: