parallel dml and set transaction quirk

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Sat, 25 May 2002 09:58:19 -0800
Message-ID: <F001.0046B9BF.20020525095819@fatcity.com>

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). Received on Sat May 25 2002 - 12:58:19 CDT