Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost
From: Grzegorz <fgrzegorzof_at_finteria.pl>
Date: Fri, 29 May 2009 18:35:52 +0200
Message-ID: <gvp2t7$moa$1_at_news.task.gda.pl>
johnbhurley_at_sbcglobal.net pisze:
>> 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
>
> Are you using bind variables?
Date: Fri, 29 May 2009 18:35:52 +0200
Message-ID: <gvp2t7$moa$1_at_news.task.gda.pl>
johnbhurley_at_sbcglobal.net pisze:
> On May 29, 12:14 pm, Grzegorz <fgrzegor..._at_finteria.pl> wrote:>> because of query plan changing in the same session (from NL Index RS to
>> Hi,
>> I'm doing some investigations
>> 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
>
> Are you using bind variables?
Well yes :) and I've got histograms (deadly combo).
But this is not related, because You can do that wo BV just manipulating
hash_area_size . No need for hard parse (sql modification/flush).
Regards.
GG
Received on Fri May 29 2009 - 11:35:52 CDT