Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Manual vs Auto PGA

RE: Manual vs Auto PGA

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 3 Jan 2007 12:14:01 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF8B59A3@MSXVS04.trivadis.com>


Deep

> 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?

I guess you are in 9i. Are you? If yes give a look to ML note 147806.1. There are limitations... In 10g they have been relaxed and the memory manager is able to provide much more memory if necessary.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 03 2007 - 05:14:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US