Re: Why does Oracle sort hashjoin output before parallel insert?

Just one more thought.

If you get a parallel insert, then the distribution after the hash join will be a range-based distribution, so perhaps the intermediate result set (between the hash processes and the insert processes) accumulates in a temporary segment that is labelled as a sort segment because some sorting has gone into producing the payload for each insert slave.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
news:i1jh409evbfjduca2c5nu4uoffqf09qd4o_at_4ax.com...


> Jonathan, thanks for your reply.

>

> I've done some further testing to be able to respond to your questions

> (responses inline).

>

> >

> >Are there any indexes on the table, and

> >if so are they local or partitioned.

> >

>

> Let's first rule out indexes: there are none.

>

> >Your notes don't include any comment about

> >what happens on the second hash join in the

> >UNION ALL - does this take place before

> >any inserts, or do you

> >    hash, insert, hash, insert ?

> >

>

> The second hash join is very quick and hard to capture. I've tested

> with the UNION ALL and second SELECT removed: I'm still getting the

> SORT segments.

>

> >Where are you getting the figures from about

> >sorting ?  Is this from v$sesstat rows sorted

> >figures, v$sort_usage labelling temp segments,

> >or are you just assuming that the temp segments

> >in use are sort segments.

> >

>

> Apart from the HASH segments that are always present, with parallel

> dml enabled there are also records in V$SORT_USAGE with SEGTYPE =

> 'SORT' linked to the slave sessions of the refresh with

> v$SESSION.SADDR = V$SORT_USAGE.SESSION_ADDR.

>

> At some point the records that are written to the SORT segments are

> recorded in V$SESSTAT (statistic sort(rows)) for the slaves that

> manage the SORT segment.

>

> >Two features that may be relevant:

> >a)  When data moves from one set of parallel

> >    slaves to another, it does so through "table queues".

> >    If it is not possible for the target slaves to  execute

> >    as the "table queues" are filled, then the "table queues"

> >    are dumped to sort segments.  This usually happens in

> >    a multi-layer join/group/order query with only two

> >    sets of slaves - it happens that the first set of slaves

> >    is still performing an operation when the second set

> >    of slaves wants to feed results to a third (recycled first)

> >    set of slaves.

> >

> Does this mean that there is no sort and the SORT segments are only

> used as a container?

>

> The mview is refreshed again this weekend. I'm planning to write a

> monitor tool that logs relevant statistics every few minutes. That

> should determine if the sort phase is present or not (contemporary

> reads and writes going on). Maybe I can also make a guess if the

> serial insert is quicker than the parallel insert.

>

> Jaap.