Database Reference
In-Depth Information
To achieve an efficient parallelization, it's critical that the amount of work is evenly distributed among all
slave processes. In fact, all slave processes belonging to a set have to wait until all slave processes of the same set
have finished. Simply put, a parallel operation is as fast as the slowest slave process. If you want to check the actual
distribution for a SQL statement, you can use either Real-time Monitoring (refer to Chapter 4) or the
v$pq_tqstat
dynamic performance view. Basically, the view provides one row for each slave process and for each
PX SEND
and
PX
RECEIVE
operation in the execution plan. Just be careful that information is provided only for the current session and
only for the last SQL statement successfully executed in parallel. Let's take a look at an example based on the output
generated by the
px_tqstat.sql
script. The mapping between the two outputs is performed with the
TQ
column of the
execution plan and the
dfo_number
and
tq_id
columns of the
v$pq_tqstat
view. Just remember, as explained before,
that the execution plan shows the information about the producers. For example,
Q1,00
maps to
dfo_number
equals 1
and
tq_id
equals 0. In addition,
PX SEND
operations map to producers, and
PX RECEIVE
operations map to consumers:
SQL> SELECT * FROM t t1, t t2 WHERE t1.id = t2.id;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | | | Q1,01 | PCWP | |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | | | Q1,00 | P->P | PART (KEY) |
| 6 | PX BLOCK ITERATOR | | 1 | 2 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | T | 1 | 2 | Q1,00 | PCWP | |
| 8 | PX PARTITION HASH ALL | | 1 | 2 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | T | 1 | 2 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------
3 - access("T1"."ID"="T2"."ID")
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
2 FROM v$pq_tqstat
3 ORDER BY dfo_number, tq_id, server_type DESC, process;
DFO_NUMBER TQ_ID SERVER_TYP PROCES NUM_ROWS BYTES
---------- ---------- ---------- ------ ---------- ----------
1 0 Producer P002 29042 3136278
1 0 Producer P003 70958 7673358
1 0 Consumer P000 20238 2188357
1 0 Consumer P001 79762 8621279
1 1 Producer P000 20238 4376714
1 1 Producer P001 79762 17242534
1 1 Consumer QC 100000 21619248
That output gives you the following information:
•
Operation 5 has sent 29,042 rows with the slave process P002 and 70,958 rows with the slave
process P003.
•
Operation 4 has received the data sent by operation 5: 20,238 rows with the slave process P000
