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

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. Received on Fri Mar 05 2004 - 12:51:33 CST