Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Archive for the ‘Parallel Execution’ Category

Interpreting Parallel MERGE Statement

with one comment

This post is inspired by one of my colleagues, who had small difficulty while interpreting parallel MERGE execution plan.

This is the step-by-step explanation I gave him.

1. Create tables.

SQL> create table t1
  2  as
  3  select
  4  	      level as c1,
  5  	      level as c2,
  6  	      rpad('x',100) as c3
  7  from
  8  	      dual
  9  connect by level <= 10000
 10  ;

Table created.

SQL> create table t2
  2  as
  3  select
  4  	      level as c1,
  5  	      level as c2,
  6  	      rpad('x', 100) as c3
  7  from
  8  	      dual
  9  connect by level <= 10000
 10  ;

Table created.

2. Would this MERGE statement run in parallel?

SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6  	      update set t1.c2 = t1.c2
  7  when not matched then
  8  	      insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

Explained.

-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

The answer is NO. As you notice from the operation #2, the MERGE part itself runs in serial. It is SELECT part that runs in parallel.

3. The reason would be parallel dml not enabled. Let’s see what happens if we have parallel dml enabled.

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for
  2  merge /*+ parallel */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6  	      update set t1.c2 = t1.c2
  7  when not matched then
  8  	      insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

-------------------------------------------------------
| Id  | Operation                  | Name     | Rows  |
-------------------------------------------------------
|   0 | MERGE STATEMENT            |          |  9356 |
|   1 |  MERGE                     | T1       |       |
|   2 |   PX COORDINATOR           |          |       |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |  9356 |
|   4 |     VIEW                   |          |       |
|*  5 |      HASH JOIN OUTER       |          |  9356 |
|   6 |       PX BLOCK ITERATOR    |          |  9356 |
|   7 |        TABLE ACCESS FULL   | T2       |  9356 |
|   8 |       BUFFER SORT          |          |       |
|   9 |        PX RECEIVE          |          | 11234 |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 11234 |
|  11 |          PX BLOCK ITERATOR |          | 11234 |
|  12 |           TABLE ACCESS FULL| T1       | 11234 |
-------------------------------------------------------

Still, the MERGE part runs serially.

4. Maybe parallel merge needs more specific HINT, which is quite natural because the MERGE statement has two tables involved. Let me make the PARALLE hint more specific.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6  	      update set t1.c2 = t1.c2
  7  when not matched then
  8  	      insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

----------------------------------------------------
| Id  | Operation                       | Name     |
----------------------------------------------------
|   0 | MERGE STATEMENT                 |          |
|   1 |  PX COORDINATOR                 |          |
|   2 |   PX SEND QC (RANDOM)           | :TQ10003 |
|   3 |    MERGE                        | T1       |
|   4 |     PX RECEIVE                  |          |
|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10002 |
|   6 |       VIEW                      |          |
|*  7 |        HASH JOIN OUTER BUFFERED |          |
|   8 |         BUFFER SORT             |          |
|   9 |          PX RECEIVE             |          |
|  10 |           PX SEND HASH          | :TQ10000 |
|  11 |            TABLE ACCESS FULL    | T2       |
|  12 |         PX RECEIVE              |          |
|  13 |          PX SEND HASH           | :TQ10001 |
|  14 |           PX BLOCK ITERATOR     |          |
|  15 |            TABLE ACCESS FULL    | T1       |
----------------------------------------------------

Now, the MERGE part really runs in parallel!

5. One more thing you should be careful about. Let me create an index on the target table t1 and see what happens on the plan.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6  	      update set t1.c2 = t1.c2
  7  when not matched then
  8  	      insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

Now we have a new operation called INDEX MAINTENANCE and need one more table queue(TQ10004) to parallelize index maintenance itself. But why do we have index maintenance here?

  • Parallel MERGE is composed of parallel update and parallel insert.
  • Parallel insert is direct path insert.
  • Indexes need to be maintained after direct path insert.

Oracle needs to maintain the indexes after direct path insert completes. This is controlled by _idl_conventional_index_maintenance hidden parameter, which you’d never have a chance to modify.

SQL> select * from table(tpack.param('_idl_conventional_index_maintenance'));

NAME                                VALUE
----------------------------------- ----------------------------------------
Name #1                             _idl_conventional_index_maintenance
  Value                             TRUE
  Is Default                        TRUE
  Sess Modifiable                   false
  Sys Modifiable                    false
  Description                       enable conventional index maintenance fo
                                    r insert direct load

The INDEX MAINTENANCE operation does not disappear even when the index is disabled, but I believe that Oracle would skip the maintenance for the disabled indexes in runtime.

SQL> alter index t1_n1 unusable;

Index altered.

SQL> explain plan for
  2  merge /*+ parallel(t1) */ into t1
  3   using (select c1, c2 from t2) t2
  4   on (t1.c1 = t2.c1)
  5  when matched then
  6  	      update set t1.c2 = t1.c2
  7  when not matched then
  8  	      insert(c1, c2) values(t2.c1, t2.c2)
  9  ;

---------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  |
---------------------------------------------------------------
|   0 | MERGE STATEMENT                    |          |  9356 |
|   1 |  PX COORDINATOR                    |          |       |
|   2 |   PX SEND QC (RANDOM)              | :TQ10004 |  9356 |
|   3 |    INDEX MAINTENANCE               | T1       |       |
|   4 |     PX RECEIVE                     |          |  9356 |
|   5 |      PX SEND RANGE                 | :TQ10003 |  9356 |
|   6 |       MERGE                        | T1       |       |
|   7 |        PX RECEIVE                  |          |  9356 |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10002 |  9356 |
|   9 |          VIEW                      |          |       |
|* 10 |           HASH JOIN OUTER BUFFERED |          |  9356 |
|  11 |            BUFFER SORT             |          |       |
|  12 |             PX RECEIVE             |          |  9356 |
|  13 |              PX SEND HASH          | :TQ10000 |  9356 |
|  14 |               TABLE ACCESS FULL    | T2       |  9356 |
|  15 |            PX RECEIVE              |          | 11234 |
|  16 |             PX SEND HASH           | :TQ10001 | 11234 |
|  17 |              PX BLOCK ITERATOR     |          | 11234 |
|  18 |               TABLE ACCESS FULL    | T1       | 11234 |
---------------------------------------------------------------

I hope this helped him to understand how to interpret the execution plan of parallel DML . :)

Written by Dion Cho

December 10, 2010 at 4:19 am

Posted in Parallel Execution

Automating tkprof on parallel slaves

with one comment

One thing very disappointing on parallel execution is the fact that the trace files of slave sessions are scattered as different files in the background dump directory.

So I built up my own script which automates the execution of tkprof on those scattered trace files.

1. pq_trace.sql generates the series of tkprof command with the input of coordinate session id.

set heading off
set timing off
set feedback off
set verify off
set serveroutput on size 100000

spool generate_pq_tkprof.sql

define __QCID = &1

declare
  v_dir     varchar2(4000);
  v_inst    varchar2(4000);
  v_trcfile varchar2(4000);
  v_cmd     varchar2(4000);
begin
  -- get dump directory
  for v in (select banner from v$version where rownum = 1) loop
    select value into v_dir from v$parameter
        where name = 'background_dump_dest';
  end loop;
  
  -- get instance name
  select value into v_inst from v$parameter
      where name = 'instance_name';
      
  -- generate series of tkprof command
  for s in (select s.sid, p.spid, rownum-1 as server#
        from v$px_session s, v$px_process p
        where
            s.sid = p.sid
            and s.qcsid in (&__QCID)
            and s.sid <> s.qcsid) loop
      
      v_trcfile := v_dir||'\'||v_inst||'_'||'p'||lpad(to_char(s.server#),3,'0')||
              '_'||s.spid||'.trc';
      v_cmd := 'ho tkprof ' || v_trcfile || ' trc' || '_' || s.server# || '.out';
      dbms_output.put_line(v_cmd);
                 
  end loop;
end;
/

spool off
set heading on
set timing on
set feedback on
set verify on
set serveroutput off

ed generate_pq_tkprof


2. The output of pq_trace.sql script is stored as generate_pq_tkprof.sql as following:

ed generate_pq_tkprof.sql

ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p000_2792.trc trc_0.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p001_704.trc trc_1.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p002_4892.trc trc_2.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p003_2728.trc trc_3.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p004_1244.trc trc_4.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p005_2736.trc trc_5.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p006_4508.trc trc_6.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p007_2432.trc trc_7.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p008_3916.trc trc_8.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p009_656.trc trc_9.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p010_5308.trc trc_10.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p011_4880.trc trc_11.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p012_4288.trc trc_12.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p013_4528.trc trc_13.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p014_5852.trc trc_14.out
ho tkprof C:\ORACLE\ADMIN\UKJA10\BDUMP\ukja10_p015_2716.trc trc_15.out



3. Following is a simple demonstration.
# Session A (session id = 130)

alter session set tracefile_identifier = &1;

@trace_on 10046 8

select /*+ parallel(t1 8) */ count(*) from t1, t1
where rownum <= 500000
;

@trace_off



# Session B
While session A executes parallel query

 @pq_trace 130   -- 130 is id of session A



After the parallel query of session A completes.

UKJA@ukja102> @generate_pq_tkprof

TKPROF: Release 11.1.0.6.0 - Production on Thu Mar 12 16:41:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
...


UKJA@ukja102> ed trc_5.out

select /*+ parallel(t1 8) */ count(*) from t1, t1
where rownum <= 500000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     14.18      58.32          0         16          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     14.18      58.32          0         16          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0   COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
      0    PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
      0     PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
 500000      COUNT STOPKEY (cr=16 pr=0 pw=0 time=52514746 us)
 500000       MERGE JOIN CARTESIAN (cr=16 pr=0 pw=0 time=32514719 us)
    345        SORT JOIN (cr=0 pr=0 pw=0 time=894433 us)
  10000         PX RECEIVE  (cr=0 pr=0 pw=0 time=701281 us)
      0          PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0           PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0            TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)
 500000        BUFFER SORT (cr=16 pr=0 pw=0 time=13119697 us)
   1452         PX BLOCK ITERATOR (cr=16 pr=0 pw=0 time=118476 us)
   1452          TABLE ACCESS FULL T1 (cr=16 pr=0 pw=0 time=53795 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PX Deq: Execution Msg                           6        0.05          0.09
  PX Deq: Table Q Normal                          3        0.09          0.09
  PX Deq Credit: send blkd                        2        0.00          0.00
  PX qref latch                                   2        0.00          0.00



These kinds of automation techniques are really time and even life savers. :)

Written by Dion Cho

March 12, 2009 at 8:00 am

How was my parallel query executed last night? – AWR!

leave a comment »

In the previous post, I discussed on how to use v$pq_tqstat view to investigate parallel execution.

Now the question is “How was my parallel query executed last night? Well, was it executed in parallel anyway?”.

The answer is AWR – The king of historical data.

Oracle stores the sql plan and statistics for top SQLs as discussed at this post. Even with the limitation of the number of top SQLs(30 per criteria), parallel queries are more likely to be captured than non-parallel ones.

See following test case.

1. Create objects

UKJA@ukja102> create table t1(c1 int, c2 char(100));
Table created.
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, 'x'
  3  from dual connect by level <= 10000;
10000 rows created.

2. Create start snapshot and execute parallel query.

UKJA@ukja102>
UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> select /*+ parallel(t1 10) */ count(*)
  2  from t1
  3  ;
  COUNT(*)
----------
     10000
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  6aubqkg2xfv20, child number 0
-------------------------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
   - dynamic sampling used for this statement
27 rows selected.
UKJA@ukja102>
UKJA@ukja102> select
  2    dfo_number, tq_id, server_type,    process,
  3    num_rows, bytes, avg_latency, waits, timeouts
  4  from v$pq_tqstat
  5  order by 1, 2, 3, 4, 5
  6  ;
DFO_NUMBER      TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES AVG_LATENCY      W
---------- ---------- -------------------- -------------------- ---------- ---------- ----------- ------
         1          0 Consumer             QC                           10        320           0
         1          0 Producer             P000                          1         32           0
         1          0 Producer             P001                          1         32           0
         1          0 Producer             P002                          1         32           0
         1          0 Producer             P003                          1         32           0
         1          0 Producer             P004                          1         32           0
         1          0 Producer             P005                          1         32           0
         1          0 Producer             P006                          1         32           0
         1          0 Producer             P007                          1         32           0
         1          0 Producer             P008                          1         32           0
         1          0 Producer             P009                          1         32           0
11 rows selected.

3. Create second snapshot and have an investigation on the parallel query through AWR – dba_hist_sql_plan and dba_hist_sqlstat.

UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_awr('&sql_id'));
Enter value for sql_id: 6aubqkg2xfv20
old   1: select * from table(dbms_xplan.display_awr('&sql_id'))
new   1: select * from table(dbms_xplan.display_awr('6aubqkg2xfv20'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 6aubqkg2xfv20
--------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
22 rows selected.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select sql_id, px_servers_execs_total, px_servers_execs_delta
  2  from dba_hist_sqlstat    
  3  where sql_id = '&sql_id'
  4         and snap_id = (select max(snap_id) from dba_hist_snapshot)
  5  ;
Enter value for sql_id: 6aubqkg2xfv20
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '6aubqkg2xfv20'
SQL_ID        PX_SERVERS_EXECS_TOTAL PX_SERVERS_EXECS_DELTA
------------- ---------------------- ----------------------
6aubqkg2xfv20                     10                     10

With AWR, we can conclude that the query got executed in parallel with DOP of 10.

Yes, AWR is always fantastic.

The other way would be investigate the dba_hist_active_sess_history view, but it’s not guaranteed to contain all the corresponding sessions. But captured successfully, it would be the most useful historical data.

Written by Dion Cho

February 20, 2009 at 2:37 pm

The most powerful way to monitor parallel execution – v$pq_tqstat

with one comment

There are many documented ways to monitor parallel execution. My favorite one is V$PQ_TQSTAT view.

V$PQ_TQSTAT view provides the most powerful and intuitive information including

  • how many processes were involved
  • how slave processes are networked
  • how rows are distributed among the processes

Following is a simple test case.

1. Create objects

UKJA@ukja102> create table t1(c1 int);

Table created.

UKJA@ukja102> create table t2(c1 int);

Table created.

UKJA@ukja102>
UKJA@ukja102> insert into t1 select level from dual connect by level <= 10000;

10000 rows created.

UKJA@ukja102> insert into t2 select level from dual connect by level <= 10000;

10000 rows created.

UKJA@ukja102>
UKJA@ukja102> commit;

Commit complete.

UKJA@ukja102>
UKJA@ukja102> alter session enable parallel dml;

Session altered.

2. Execution plan of parallel execution. Note that both sections(insert, select) are parallelized.

UKJA@ukja102> explain plan for
  2  insert /*+ parallel(t1 8) */ into t1
  3  select /*+ parallel(t2 8) */ * from t2;

Explained.

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1251029007                                                                                                                                                                             

-----------------------------------------------------------------------------------------------------------------                                                                                      
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                                                                                      
-----------------------------------------------------------------------------------------------------------------                                                                                      
|   0 | INSERT STATEMENT        |          | 10000 |   126K|     2   (0)| 00:00:01 |        |      |            |                                                                                      
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |                                                                                      
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 | 10000 |   126K|     2   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |                                                                                      
|   3 |    LOAD AS SELECT       | T1       |       |       |            |          |  Q1,01 | PCWP |            |                                                                                      
|   4 |     PX RECEIVE          |          | 10000 |   126K|     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |                                                                                      
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 | 10000 |   126K|     2   (0)| 00:00:01 |  Q1,00 | P->P | RND-ROBIN  |                                                                                      
|   6 |       PX BLOCK ITERATOR |          | 10000 |   126K|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |                                                                                      
|   7 |        TABLE ACCESS FULL| T2       | 10000 |   126K|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |                                                                                      
-----------------------------------------------------------------------------------------------------------------                                                                                       

Note                                                                                                                                                                                                   
-----                                                                                                                                                                                                  
   - dynamic sampling used for this statement                                                                                                                                                           

18 rows selected.

UKJA@ukja102>
UKJA@ukja102> commit;

Commit complete.

3. Now execute parallel DML.

UKJA@ukja102> alter session enable parallel dml;

Session altered.

UKJA@ukja102>
UKJA@ukja102> insert /*+ parallel(t1 8) */ into t1
  2  select /*+ parallel(t2 8) */ * from t2;

10000 rows created.

UKJA@ukja102>
UKJA@ukja102> commit;

Commit complete.

4. Monitor through V$PQ_TQSTAT view.

UKJA@ukja102> select dfo_number, tq_id, server_type,  process,
  2    num_rows, bytes, avg_latency, waits, timeouts
  3  from v$pq_tqstat
  4  order by 1, 2, 3, 4, 5
  5  ;

DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES AVG_LATENCY      WAITS   TIMEOUTS                                                                                                    
---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------                                                                                                    
         1          0 Consumer   P000             1249       6341           0         15         12                                                                                                    
         1          0 Consumer   P001             1251       6402           0         15         12                                                                                                    
         1          0 Consumer   P002             1251       6402           0         15         12                                                                                                    
         1          0 Consumer   P003             1251       6402           0         16         13                                                                                                    
         1          0 Consumer   P004             1251       6355           0         15         12                                                                                                    
         1          0 Consumer   P005             1249       6393           0         15         12                                                                                                    
         1          0 Consumer   P006             1249       6393           0         15         12                                                                                                    
         1          0 Consumer   P007             1249       6393           0         16         13                                                                                                    
         1          0 Producer   P008             1320       6747           0          2          0                                                                                                    
         1          0 Producer   P009             1980      10040           0          3          0                                                                                                    
         1          0 Producer   P010             1320       6746           0          2          0                                                                                                    
         1          0 Producer   P011             1320       6748           0          2          0                                                                                                    
         1          0 Producer   P012             1320       6746           0          3          1                                                                                                    
         1          0 Producer   P013             1320       6649           0          2          0                                                                                                    
         1          0 Producer   P014              760       3952           0          3          0                                                                                                    
         1          0 Producer   P015              660       3453           0          2          0                                                                                                    
         1          1 Consumer   QC                  8        856           0         38         13                                                                                                    
         1          1 Producer   P000                1        107           0          3          2                                                                                                    
         1          1 Producer   P001                1        107           0          3          2                                                                                                    
         1          1 Producer   P002                1        107           0          2          1                                                                                                    
         1          1 Producer   P003                1        107           0          3          2                                                                                                    
         1          1 Producer   P004                1        107           0          1          1                                                                                                    
         1          1 Producer   P005                1        107           0          5          2                                                                                                    
         1          1 Producer   P006                1        107           0          2          1                                                                                                    
         1          1 Producer   P007                1        107           0          6          3                                                                                                     

25 rows selected.

The result of execution plan is a must-be to interpret the result of v$pq_tqstat. It would be a good pratice to post the result of explain plan and v$pq_tqstat when discussing how parallel queries/DMLs are being executed.

Written by Dion Cho

February 16, 2009 at 1:48 pm

Follow

Get every new post delivered to your Inbox.

Join 59 other followers