RE: Manual vs Auto PGA

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