Monitor parallel execution
After the parallel execution, this is the best chance to query the execution information.
For Example, to analyze the execution of following parallel SQL statement.
select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
After execution,
hlr@ORCL> select * from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 13
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 13
Server Threads 6 0
Allocation Height 3 0
Allocation Width 1 0
Local Msgs Sent 362 303740
Distr Msgs Sent 0 0
Local Msgs Recv'd 368 303782
Distr Msgs Recv'd 0 0
11 rows selected.
hlr@ORCL> select DFO_NUMBER, TQ_ID, SERVER_TYPE, NUM_ROWS ,BYTES,process from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- --------------- ---------- ---------- --------
1 0 Consumer 17151 100454 P001
1 0 Consumer 17242 100969 P002
1 0 Consumer 17257 101058 P000
1 0 Producer 1971 9955 P004
1 0 Producer 29565 174989 P005
1 0 Producer 20114 117537 P003
1 1 Consumer 893 8107 P002
1 1 Consumer 2914 26341 P001
1 1 Consumer 0 60 P000
1 1 Producer 611 5494 P003
1 1 Producer 2593 23493 P005
1 1 Producer 603 5521 P004
1 2 Consumer 3807 19040 QC
1 2 Producer 2914 14545 P001
1 2 Producer 893 4475 P002
1 2 Producer 0 20 P000
16 rows selected.
hlr@ORCL>
Obviously, from the v$pq_tqstat, the DOP is downgrade to 3, and have two slave sets. From the v$tq_sesstat, we can see that what’s the process. How these processes cooperated together to generated the result. In this sample, P003, P004,P005 generate the data and feed to P000,P001,P002, totally twice. Then P000,P001,P002 feed the data to QC, the query coordinator. Also, we can find that P000 at the first time received 17257 rows, but at the second time it received 0 rows, and lastly, it product 0 rows to QC. Why?
Let’s look at the execution plan for further investigation.
hlr@ORCL> explain plan for select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id; Explained. hlr@ORCL> @utlxplp PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1177066807 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3867 | 147K| 13 (8)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 3867 | 147K| 13 (8)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 3867 | 147K| 13 (8)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 53332 | 677K| 7 (0)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| PRODUCTS | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 3867 | 98K| 5 (0)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| COSTS | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("C"."PROD_ID"="P"."PROD_ID") Note ----- - dynamic sampling used for this statement 27 rows selected. hlr@ORCL>
This is a hash-hash parallel distribute, using hash joining. firstly, Slave set(Q1,00) scan products table and send the results to slave set(Q1,02), and slave set(Q1,01) scan the costs table and send results to slave set(Q1,02), which slave in slave set(Q1,02) to send to is dependent on the hash value of join key. as the table costs only have two distinct values for prod_id, so the rows in costs only have two target slave to send to, resulting one slave of the slave set (Q1,02) receive 0 rows. After the slaves in (Q1,02) received rows from the other two slave sets, they hash join the rows and send the result to QC, obviously, one slave will have no rows to process.
This is why 0 rows in the above output.
Also, you can enable the trace for parallel execution, through this way:
alter session set events '10132 trace name context forever';
As a summary, we can monitor the parallel executions through v$ views, execution plan and trace event.
[...] Another principle question: how to monitor the parallel execution except the execution plan? how about the trace, V$ views, or other things? Refer to my next next article for answers! [...]