Re: Use of TEMP tablespace other than sorting

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