Thanks JL
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b41uga$1mc$1$830fa7a5_at_news.demon.co.uk...
>
> 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 ? ]
>
>
>
Received on Tue Mar 04 2003 - 04:52:26 CST
