Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost
Date: Fri, 29 May 2009 17:56:08 +0100
"Grzegorz" <fgrzegorzof_at_finteria.pl> wrote in message
> 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
> 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 184.108.40.206 .
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.htmlReceived on Fri May 29 2009 - 11:56:08 CDT