Re: Manual vs Auto PGA

Up to 10gR1, you had to fiddle with hidden parameters (_smm... and
_pga_max_size) to allow more than 200MB per area (when using
pga_aggregate_target), in 10gR2 this limit was *10
rgds

On 1/3/07, Christian Antognini <Christian.Antognini@xxxxxxxxxxxx> wrote:


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

Other related posts: