Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 29 May 2009 17:56:08 +0100
Message-ID: <XLGdnUnA1as4jr3XnZ2dnUVZ8j-dnZ2d_at_bt.com>


"Grzegorz" <fgrzegorzof_at_finteria.pl> wrote in message news:gvp1l6$jls$1_at_news.task.gda.pl...
> 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 9.2.0.8 .
> 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Fri May 29 2009 - 11:56:08 CDT

Original text of this message