Dan,
I have outlined some scenarios when the optimizer may quit using
hash joins as the join method. Please let us know if any of
these are applicable. Also, I am assuming that you have not
upgraded your version of Oracle and/or changed anything
significant since your last run of the query.
Events that might cause the behavior change:
- Significant reduction of values of hash_area_size and/or
hash_multiblock_io_count.
- If running 7.3.x, 733_PLANS_ENABLED is set to FALSE.
- HASH_JOIN_ENABLED is set to FALSE
- OPTIMIZER_INDEX_CACHING is set to a high value say 99.
- Change in the amount of data and/or the distribution of data
in the tables, which makes the optimizer re-think its "hash
join" strategy -- unlikely, but just threw that in, just in
case.
All of the aforementioned parameters can be set/changed at the
session level. On the flip side, if you set a high value for
HASH_AREA_SIZE (e.g. 100 Mb.) and HASH_MULTIBLOCK_IO_COUNT (same
value as DB_FILE_MULTIBLOCK_READ_COUNT), set HASH_JOIN_ENABLED
to TRUE, and OPTIMIZER_INDEX_CACHING to 0 (default) at the
session level, you will encourage the optimizer to use hash
joins over nested loops. Normally, if you have all of the
aforementioned parameters set to the recommended values, you
will not need a hint to force the optmizer to use hash joins.
It just will do it automatically, but your mileage may vary,
based on some other factors.
Hope that helps,
Gaja.
- Dan.Hubler_at_midata.com wrote:
>
>
> Thanks for the reply.
> The optimizer has always been set to CBO and the tables have
> been analyzed
> (10%)
> on a weekly basis for many months. Just last week, we did
> analyze
> (compute) on the
> tables in question, with no change in the results.
>
> I should have included that information in my original post.
>
>
>
>
>
>
> Diane Whitehead
> <Diane.Whitehead_at_palmerharvey.co.uk>@fatcity.com on
> 06/26/2000 09:57:08 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> Sent From the mail file of: Dan Hubler
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
> If your optimizer is set to cost based and the tables
> concerned have been
> analyzed between the change in execution plan, that could
> account for the
> alteration.
>
> > -----Original Message-----
> > From: Dan.Hubler_at_midata.com [SMTP:Dan.Hubler_at_midata.com]
> > Sent: Monday, June 26, 2000 3:16 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Hash join not being used
> >
> >
> > We have a query that is a good candidate for a hash-join
> operation.
> > As a matter of fact, when we force it to use a hash-type
> join (with a
> > hint), the
> > elapsed time of the query goes from 90 minutes to about 2
> minutes.
> >
> > However, we are unable to force it to use a hash-join,
> without using the
> > hint.
> >
> > What perplexes us is that a month ago, the query was using a
> hash-join on
> > its
> > own, without our intervention.
> >
> > Any clues as to why the optimizer would change its mind and
> stop using a
> > hash-join?
> >
> >
> >
> > --
> > Author:
> > INET: Dan.Hubler_at_midata.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> You may
> > also send the HELP command for other information (like
> subscribing).
> --
> Author: Diane Whitehead
> INET: Diane.Whitehead_at_palmerharvey.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
> subscribing).
>
>
>
> --
> Author:
> INET: Dan.Hubler_at_midata.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
subscribing).
Gaja Krishna Vaidyanatha | gajav_at_yahoo.com
Brio Technology | (972)-304-1170
"Opinions and views expressed are my own and not of Brio Technology"
Received on Mon Jun 26 2000 - 11:35:12 CDT
