Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: hash join

Re: hash join

From: Andrew Babb <andrewb_at_mail.com>
Date: Thu, 08 Apr 1999 15:41:39 +0800
Message-ID: <370C5DB3.9967685B@mail.com>


In addition to Piotr's comments, when the tables are being analyzed, do they have the normal data population. If these tables are populated during the day, and some batch job purges the table at night, then you need to make sure that the analyze is performed when the data is populated.

It is quite possible for bad statistics to generate a bad execution plan.

To solve, try and add hints to the query, and if the query is from within Oracle Forms or Reports, where hints are not generally allowed, generate a view with the hint embedded and query the view.

Andrew

Piotr Kolodziej wrote:

> llei_at_ros.com wrote in message <7eg13f$ahs$1_at_nnrp1.dejanews.com>...
> >We are running choose base optimizer and analyze tables and indexes everyday.
> >A sql statement is running very very slow(more than 8 hours). The execution
> >plan shows that it uses hash join instead of the indexes to join 2 big
> >tables(full table scan on those two tables). After we delete the statistics,
> >it use the indexes and it runs only 20 minutes. Does anyone know how to
> >improve the hash join?
>
> There are several instance/session parameters which allows tunning hash
> joins, especially hash_area_size and hash_multiblock_io_count.
> Refer to Server Reference and Tunning Manual.
> But maybe using HJ for particular query is not the best execution path.
> If querey performance was improved after you deleted statistics, using
> /*+rule */ or /*+use_nl */ hint would eliminate the need of deleting
> statistics. If you are not able to change the query, the only choice
> is disabling HJ at the instance level.
>
> --
> Piotr Kolodziej pkol_at_otago.gda.pl
> Just my private opinion.
Received on Thu Apr 08 1999 - 02:41:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US