Tim,
Thanks for the info. You are right, no rbs required -
this was a holdover from the original script that was
implementing a mod()+commit conditional loop. Should
have slapped nologging in there too, good catch.
Here is a script I devised for associating PQ parent
procs with child processes:
set lines 120
set feed on
set serveroutput on
column spid format a20 heading "parent unix process"
column par_sql_text format a50 word_wrapped
column username format a20
break on par_sid on par_ser on username on
par_sql_text
select se.username,ps.qcsid par_sid, ps.qcserial#
par_ser,
pr.sid child_pq_sid,
substr(sq.sql_text,1,1000) par_sql_text
from v$px_process pr, v$px_session ps, v$session se,
v$process vp, v$sqlarea sq
where pr.sid = ps.sid
and ps.qcsid = se.sid
and ps.qcserial# = se.serial#
and se.paddr = vp.addr
and se.sql_address = sq.address(+)
order by 1,2,3,4,5
/
/jack silvey
- Tim Gorman <Tim_at_SageLogix.com> wrote:
> 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).
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 - 15:38:21 CDT
