Re: Hash join not being used

--0-1804289383-962041128=:11301
Content-Type: text/plain; charset=us-ascii

 Here are a couple of possible culprits.

   One of the tables in the query was re-analyzed.    Periodic analyze script changed the method, percent, or number of rows it uses to analyze the tables.    Histograms were changed on one of the tables in the query.    Index added or dropped on one of the tables in the query.    Change in any of the following init.ora parameters:

      hash_area_size
      sort_area_size (if hash_area_size is being defaulted)
      hash_join_enabled
      hash_multiblock_io_count
      compatible? (not sure about this one)

   New version of oracle
   A database or table reorg (i.e. exp/imp)

If you really want to force to use a hash join without a hint, and if you're on version 8i, try a stored outline.

  Dan.Hubler_at_midata.com wrote:

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).


---------------------------------
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
--0-1804289383-962041128=:11301
Content-Type: text/html; charset=us-ascii



<P> Here are a couple of possible culprits.</P>

<OL>

<LI>One of the tables in the query was re-analyzed.</LI>

<LI>Periodic analyze script changed the method, percent, or number of rows it uses to analyze the tables.</LI>

<LI>Histograms were changed on one of the tables in the query.</LI>

<LI>Index added or dropped on one of the tables in the query.</LI>

<LI>Change in any of the following init.ora parameters:</LI>

<OL>

<LI>hash_area_size</LI>

<LI>sort_area_size (if hash_area_size is being defaulted)</LI>

<LI>hash_join_enabled</LI>

<LI>hash_multiblock_io_count</LI>

<LI>compatible? (not sure about this one)</LI></OL>

<LI>New version of oracle</LI>

<LI>A database or table reorg (i.e. exp/imp)</LI></OL>

<P>If you really want to force to use a hash join without a hint, and if you're on version 8i, try a stored outline.<BR></P>

<P>&nbsp; <B><I>Dan.Hubler_at_midata.com</I></B> wrote: <BR></P>

<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"><BR>We have a query that is a good candidate for a hash-join operation.<BR>As a matter of fact, when we force it to use a hash-type join (with a<BR>hint), the<BR>elapsed time of the query goes from 90 minutes to about 2 minutes.<BR><BR>However, we are unable to force it to use a hash-join, without using the<BR>hint.<BR><BR>What perplexes us is that a month ago, the query was using a hash-join on<BR>its<BR>own, without our intervention.<BR><BR>Any clues as to why the optimizer would change its mind and stop using a<BR>hash-join?<BR><BR><BR><BR>-- <BR>Author: <BR>INET: Dan.Hubler_at_midata.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com !

(note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Get Yahoo! Mail - Free email you can access from anywhere!