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