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 <[EMAIL PROTECTED]> > 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@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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 <[EMAIL PROTECTED]> > 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@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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
