Re: direct distributed insert causes massive sorting on target, why?

Could you clarify what you mean by 'two sets of slaves' ? Does this mean you got 24 slaves ? Do you get any clue about how these may be related by looking at v$px_sesstat ?

This may be related in some way to the fact that when you do a direct insert on a table, Oracle still has to do ordinary index maintenance - so it sorts the incoming data for each index in turn because this improves the probability of reducing the UNDO and REDO overhead from a 'per row' cost to a 'per block' cost.

PX does do some funny things because there is insufficient communication between slaves at the same level, which may be why you don't see this effect so dramatically when you serialise. Serially, Oracle may determine something about the data that it doesn't determine in parallel because it has to work on the basis that 'one of the other slaves may ... ' which causes a generic solution to be applied rather than a special case that a serial process could see.

Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 26 December 2002 05:50
why?

>All,
>
>In our 8.1.7.4 warehouse, we are attempting to copy records from a
>partitioned table in one domain to a partitioned table in another
>domain (via a database link) like so:
>
>alter session enable parallel dml;
>
>insert /*+ append parallel(a,12) */ into tablea a
>select /*+ full(b) parallel(b,12) */ * from tableb_at_dblnk b;
>
>However, unexpectedly, this causes two sets of parallel processes to
be
>spawned on the target. One of these sets goes to work immediately,
>building huge sort segments in their temporary tablespace, and the
>others sit idle. Since we are extracting 250m+ records, we are
blowing
>out temp tablespace. (The sort segments being created are not the
same
>as the temporary segments that are written by CTAS or index creation
>statements, instead, they are true sort segments.)
>
>I would expect this behavior from a large query that needed to sort
>(since these types of queries can spawn two sets of PQ processes -
one
>for record retrieval, one for sorting) however, this is just an
insert
>of all the records with no criteria in the select statement.
>
>Why would a direct distributed parallel DML insert as select (I guess
>we could call this a DDPDMLIAS? :>)) cause an initial run of sorting?
>
>My working hypothesis is that Oracle is following its usual rules of
>distributed transactions and bringing the source resultset to the
>target for manipulation, building it into temp segments and then
using
>that as a source for inserts, much as it does in a typical
distributed
>query. Perhaps a driving_site hint would help here.
>
>However, that does not explain why I was able to cause it to stop it
>from doing the massive sorts by serializing the target insert.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).