Re: parallel dml and set transaction quirk

Parallel DML of degree N is really implemented as N+1 distributed transactions across N+1 different database sessions (the extra "+1" session being the originating session, which becomes the PDML "PX coordinator"). Two-phase commit to complete (or cancel) all of the transactions from the "PX coordinator" session is necessary, thus the need for an explicit initiation of the distributed transaction using the ALTER SESSION ENABLE PARALLEL DML command, which must come before any statement initiating a transaction. Did the ALTER SESSION command fail when SET TRANSACTION came first? It should have...

Putting the SET TRANSACTION after the ALTER SESSION should have have either forced all of inserting PX slaves to work in the indicated RBS (possibly your intention?) or forced just the insignificant "PX coordinator" session only to work in the indicated RBS (probably not the intention!). Were you able to determine which happened?

If the APPEND hint did it's job, you should not have generated any undo/rollback to speak of, rendering the SET TRANSACTION pretty much worthless anyway. An APPEND (a.k.a "direct-path") parallel INSERT creates TEMPORARY segments to insert into, which are then merged into the table upon successful completion of the entire PDML operation. If the PDML fails, then "rollback" consists merely of dropping the now-useless TEMPORARY segments.

Thanks! Great observations and thanks for sharing!

P.S. How about a NOLOGGING hint right after the APPEND hint during the INSERT? Might shave a few seconds off the 16 minute elapsed time, depending on the overall throughput of your LGWR process and the service/wait times on the I/O subsystem underneath the online redo log files? Of course, it's also dependent on how recoverable you want this operation to be!!!

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, May 25, 2002 11:58 AM

> All,
>
> Just had an interesting thing happen that I though I
> would share.
>
> Moving data between two 25 gig tables. Here is the
> script I used:
>
> #!/bin/ksh
> echo 'start of ALV Copy'
>
> sqlplus -S << EOF
> ax00332/pwd
> set transaction use rollback segment batch_rbs;
> alter session enable parallel dml;
> set serveroutput on size 1000000;
> set timing on;
>
> insert /*+ append parallel(b,12) */
> into dwcorp.$1_new b
> select /*+ parallel(a,12) */ * from dwcorp.$1 a;
>
> commit;
> EOF
>
>
> This process showed 12 pq procs doing a select and
> waiting on "pq: send blocked" event, and one process
> doing the insert and waiting on "file open" event.
> These waits persisted for 15 minutes before I killed
> the processes.
>
> The problem was that the "enable parallel dml"
> statement has to be the first statement and cannot
> come after the "set transaction" statement.
>
> Once I put the DML statement before the transaction
> statement, the query spawned off 24 processes and
> finished in 16 minutes.
>
>
> /jack silvey
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jack Silvey
> INET: jack_silvey_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).