Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘v$pq_tqstat

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 60 other followers