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