Re: Use of TEMP tablespace other than sorting

You can set event 10104 to see the
stages of each hash join. The output
is quite verbose - especially when the
join runs into a multipass join.

If you have six tables joining with hash joins, you can have six sets of information intermingled.

You then need to switch on 10046 at level 8 at the same time so that you can see the scattered reads (base tables) and direct reads and writes (temp segment) to get an idea of which part of which activity is doing how much I/O.

The first catastrophe point occurs when
the hash_area_size is too small to hold
the whole hashed data set from the first row source in the join. (one-pass)

The second catastrophe point occurs
when the hash_area_size is too
small to hold even one section (called
a partition - just to confuse the issue) of a sliced-up hash table. This usually occurs only when Oracle thinks the
volume of data in the row source is a
lot smaller than it really is. (multi-pass) Of course, in extreme cases, it is possible for Oracle to determine accurately that the volume of data for one hash VALUE
(let alone hash bucket) is too large to
fit in memory - this would be the result of (a) an unrealistic hash_area_size or
(b) extremely skewed data.

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


"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
news:%U_8a.237$pK2.357_at_news.indigo.ie...


> 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 ? ]