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

Just to clarify, server_set tells what set of slaves each process belongs to.

> Johnathan,
>
> I saw 24 slaves, two sets of 12. I got this info from v$px_session:
>
> select
> decode(px.qcinst_id,NULL,s.username,
> ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
> decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
> to_char( px.server_set) "Slave Set",
> to_char(s.sid) "SID",
> decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
> px.req_degree "Requested DOP",
> px.degree "Actual DOP"
> from
> v$px_session px,
> v$session s
> ,v$px_process p
> where
> px.sid=s.sid (+)
> and
> px.serial#=s.serial#
> and
> p.sid (+)= px.sid
> order by 5,1 desc
> /
>
>
> One set is doing sorting, one waiting in active state, but without
> racking up any IO.
>
> I like the index theory, but don't believe we had indexes. I will
have
> to go back and reproduce, since I did not have time to dig in at the
> moment of the problem. Good info, though, makes sense.
>
> Oracle support just called and verified what I said, and are now
filing
> a documentation bug. They did not have a firm explaination.
>
> My best guess is that the initial set of query processes is creating
a
> temp table sorted by primary key or partition key to facilitate an
easy
> lookup for the second set.
>
> It makes sense to think of the first set of processes using a
temporary
> table to communicate with the second set, rather than trying to do a
> handoff of rows between two sets of slaves.
>
> This also explains why serialization of the target removes the need
to
> sort.
>
> I think the question is, why would it beneficial for oracle to use
disk
> as an intermediate means of communication between the processes? Is
it
> related to performance, or the fact that Oracle does not have a
robust
> enough intra-process communication between parallel processes to
handle
> this type of processing?
>
>
> Thanks,
> Jack
>
>
>
> >
> > 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).
> >
> >
> >
>
> Thanks,
>
> Jack Silvey
>
>
> >
> > 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).
> >
> >
> >
>
> Thanks,
>
> Jack Silvey
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jack Silvey
> INET: oracle-l_at_warehousedba.com
>
> 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).
>
>
>

Thanks,

Jack Silvey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack Silvey
  INET: oracle-l_at_warehousedba.com

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