Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: Jonathan Lewis <>
Date: Fri, 29 May 2009 17:56:08 +0100
Message-ID: <>

"Grzegorz" <> wrote in message news:gvp1l6$jls$
> Hi,
> I'm doing some investigations
> because of query plan changing in the same session (from NL Index RS to
> HJ FTS), and got question about Hash_Join_Area_Size when
> work_area_policy=auto.
> Is that possible that the same session can have different
> HJAS for same query ?
> When auto pga allocation is in place its quite possible in my opinion.
> Looks like depending on instance workload (in PGA terms) some queries may
> switch between NL Index RS and HJ FTS in the same session.
> I'm on .
> Regards.
> GG

The cost calculation for the hash join is based on a number derived from the pga_aggregate_target parameter, not from the amount of memory currently available; so load doesn't affect the plan. As JBH implied, a difference in the input bind variables (especially where histograms are involved) could be the reason why you see different plans at different times from the same session.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Fri May 29 2009 - 11:56:08 CDT

Original text of this message