RE: Manual vs Auto PGA

From: Lawie, Duncan <duncan.lawie_at_credit-suisse.com>
Date: Tue, 2 Jan 2007 14:29:09 -0000
Message-ID: <07150B51CE30744CBBB58E696139708D0A8F37B8@elon11p32002.csfp.co.uk>

Individual sessions can't normally take more than 5% of the total value of pga_aggregate_target.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of blr_dba Sent: 30 December 2006 15:01
To: 'ORACLE-L'
Subject: Manual vs Auto PGA

Hi Gurus.

My query is using big hash joins. I had set PGA_AGGREGATE_TARGET=4GB. But when I observed the pga statistics, I found that the max. pga used by a session is 300M. That was the only user session running.

Later I used manual PGA and defined the hash_area_size=2GB explicitly. Then oracle started using the whole hash_area and the query ran faster.

Would like to know why oracle did not allocated teh desired hash_area_size when I used auto PGA?

Are there any situations where we should use manual pga?

Anything I missed?

Deep

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 02 2007 - 08:29:09 CST