Is there an event one can set to check the operation of the hash join -
specifically whether or not it is dumping those intermediate sets ?
[and what's the first catastrophe point ? ]
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b41qcc$4c9$1$8300dec7_at_news.demon.co.uk...
>
> Hold on -
>
> That wasn't my advice - it was a hypothetical
> reason why someone else might have seen
> a performance improvement when they tried it.
>
>
> I think Joel Garry has highlighted a most
> important point - your statistics appear to be
> a few weeks out of data.
>
> Hash joins (particularly multi-table hash joins)
> suffer two catastrophe points in their performance
> curves, and the second one appears only if real life
> and the stored statistics are far enough out of line
> with each other.
>
> Check the SQL for lost joins, and also for the
> table order that you think would be sensible
> and the number of rows that you think should
> be picked up from each table.
>
> Then check the execution plan, and see if
> the join order and the CARDINALITY for
> each TABLE line matches your expectation.
> My primary guess is still that Oracle is generating
> one (or more) intermediate result sets that is
> close to a cartesian join between two of the tables,
> and having to dump it to disc.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon one-day tutorials:
> Cost Based Optimisation
> Trouble-shooting and Tuning
> Indexing Strategies
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____UK_______April 8th
> ____UK_______April 22nd
>
> ____USA_(FL)_May 2nd
>
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Rick Denoire" <100.17706_at_germanynet.de> wrote in message
> news:0ro76vgr6p6qimjsagojp09k3rif0i4106_at_4ax.com...
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> >
> > >...(Apart from the fact that decreasing
> > >the hash_area_size MIGHT just be enough to push
> > >Oracle from doing a hash join to using a different
> > >join mechanism).
> >
> > OK, I will diminish hash_area_size and see what happens.
> > Seems to be a kind of shortcut solution.
> >
> > Rick Denoire
>
>
Received on Tue Mar 04 2003 - 04:06:50 CST
