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.
No comments:
Post a Comment