Thursday, August 5, 2010

_smm_max_size and _pga_max_size

I always try to not to tinker with the hidden parameters and only use them if Oracle Support asks me to do so.

But just stumbled upon a great blog post by one of my favorite Oracle blogger Charles Hooper, in which he discusses two valuable and should-be-used-frequently hidden parameters.

_smm_max_size= Maximum workarea size for one session

_pga_max_size= Maximum PGA size for one session

AS PGA_AGGREGATE_TARGET is a soft limit for all the sessions, any runaway session can consume and cross that limit. Also whenever sort_area_size with in PGA is consumed, then temporary tablespace is used.

By using _smm_max_size, you may restrict a session to use that much memory for sorting with in a PGA.

By using _pga_max_size, you may restrict a session to use that much memory for the whole PGA. That keeps your PGA available for other sessions too.

1 comment:

筱朝宜財 said...

河水永遠是相同的,可是每一剎那又都是新的。............................................................