Posts Tagged ‘execution plan’
The most powerful way to monitor parallel execution – v$pq_tqstat
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.
Dion Cho - Oracle Performance Storyteller 