APPEND PARALLEL – Parallel DML

SQL> create table t1 (x number, y number) nologging;

Table created.

SQL> create table t2 (x number, y number) nologging;

Table created.

SQL> alter table t1 parallel (degree 2);
alter table t2 parallel (degree 2);
Table altered.

SQL>

Table altered.

 

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for insert /*+ APPEND parallel (t1,4) */ into t1
select rownum, rownum
from dba_source, (select *
from dual
connect by level < 11) 2 3 4 5 ;

Explained.
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Plan hash value: 1812084382

---------------------------------------------------------------------------------------------------------
 | Id | Operation | Name | Time | TQ |IN-OUT| PQ Distrib |
 ---------------------------------------------------------------------------------------------------------
 | 0 | INSERT STATEMENT | | 00:00:03 | | | |
 | 1 | PX COORDINATOR | | | | | |
 | 2 | PX SEND QC (RANDOM) | :TQ10001 | 00:00:03 | Q1,01 | P->S | QC (RAND) |
 | 3 | LOAD AS SELECT | T1 | | Q1,01 | PCWP | |
 | 4 | PX RECEIVE | | 00:00:03 | Q1,01 | PCWP | |
 | 5 | PX SEND ROUND-ROBIN | :TQ10000 | 00:00:03 | | S->P | RND-ROBIN |
 | 6 | COUNT | | | | | |
 | 7 | MERGE JOIN CARTESIAN | | 00:00:03 | | | |
 | 8 | VIEW | | 00:00:01 | | | |
 |* 9 | CONNECT BY WITHOUT FILTERING| | | | | |
 | 10 | TABLE ACCESS FULL | DUAL | 00:00:01 | | | |
 | 11 | BUFFER SORT | | 00:00:03 | | | |
 | 12 | VIEW | DBA_SOURCE | 00:00:03 | | | |
 | 13 | UNION-ALL | | | | | |
 |* 14 | FILTER | | | | | |
 |* 15 | HASH JOIN | | 00:00:02 | | | |
 |* 16 | HASH JOIN | | 00:00:01 | | | |
 | 17 | INDEX FULL SCAN | I_USER2 | 00:00:01 | | | |
 |* 18 | HASH JOIN | | 00:00:01 | | | |
 | 19 | INDEX FULL SCAN | I_USER2 | 00:00:01 | | | |
 |* 20 | INDEX FAST FULL SCAN | I_OBJ2 | 00:00:01 | | | |
 | 21 | INDEX FAST FULL SCAN | I_SOURCE1 | 00:00:02 | | | |
 | 22 | NESTED LOOPS | | 00:00:01 | | | |
 |* 23 | INDEX SKIP SCAN | I_USER2 | 00:00:01 | | | |
 |* 24 | INDEX RANGE SCAN | I_OBJ4 | 00:00:01 | | | |
 | 25 | NESTED LOOPS | | 00:00:01 | | | |
 | 26 | NESTED LOOPS | | 00:00:01 | | | |
 | 27 | NESTED LOOPS | | 00:00:01 | | | |
 |* 28 | INDEX FAST FULL SCAN | I_OBJ2 | 00:00:01 | | | |
 |* 29 | FIXED TABLE FIXED INDEX| X$JOXSCD (ind:1) | 00:00:01 | | | |
 |* 30 | INDEX RANGE SCAN | I_USER2 | 00:00:01 | | | |
 |* 31 | INDEX RANGE SCAN | I_USER2 | 00:00:01 | | | |
 ---------------------------------------------------------------------------------------------------------

Reference:

Bottom line: if the PX SEND QC is above (lower line number) the LOAD AS SELECT the slaves are doing the insert, if the LOAD AS SELECT is above the PX SEND QC the query coordinator is doing the insert (and you’ll probably see lots of “PX Deq Credit: send blkd” at that point as the PX slaves wait for the QC to load data into blocks).

https://jonathanlewis.wordpress.com/2013/06/06/parallel-dml/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s