Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: hash join
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