Determine PGA sort & hash sizes per session



My OraFAQ Blog

Contact me

Show PGA sort area and hash area sizes for each session.

&hwm*(2048576+p1.value+p2.value) pga_size, 
&hwm*(2048576+p1.value+p2.value)/(1024*1024) pga_mb 
   v$parameter p1,
   v$parameter p2
where = 'sort_area_size'
and = 'hash_area_size'

The PGA_MB column gives the number of megabytes of memory Oracle will use for each connected database session. Multiply this by your expected peak number of sessions to figure out how much RAM to set aside for your sessions; alternatively, look at how much RAM you can spare for sessions, divide by PGA_MB, and set the initialization parameter SESSIONS to not exceed that value.

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...