RE: Hash join not being used

In addition to Gaja's list, I have found that moving a database to a server with a different configuration can cause this as well.

Upgrading by adding CPU's would possibly have this effect as well, but I haven't seen this one.

I have seen this happen when moving a database to a similar but differently configured server, with the Oracle configuration being identical. ( it caused a sharp increase in aspirin consumption )

Jared

On Mon, 26 Jun 2000, Gaja Krishna Vaidyanatha wrote:

> 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:
>
> 1) Significant reduction of values of hash_area_size and/or
> hash_multiblock_io_count.
> 2) If running 7.3.x, 733_PLANS_ENABLED is set to FALSE.
> 3) HASH_JOIN_ENABLED is set to FALSE
> 4) OPTIMIZER_INDEX_CACHING is set to a high value say 99.
> 5) 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"
>
> __________________________________________________
> Do You Yahoo!?
> Get Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Author: Gaja Krishna Vaidyanatha
> INET: gajav_at_yahoo.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).
Received on Mon Jun 26 2000 - 19:22:11 CDT