Database Reference
In-Depth Information
•
Operation 2 has sent to the query coordinator 20,238 rows with the slave process P000 and
79,762 rows with the slave process P001. As a result of the previous distribution, this one is also
suboptimal.
•
Operation 1, which is executed by the query coordinator, has received 100,000 rows.
Each slave process opens its own session to the database instance. This means that if you want to monitor or
trace the processing performed to execute a single SQL statement, you can't focus on a single session. Therefore,
either you're using a tool like Real-time Monitoring that aggregates the execution statistics coming from several
sessions for you, or you have to do it yourself. For example, with SQL trace, every slave process generates its own trace
file (the TRCSESS command-line tool might be useful in such a situation). One of the main problems related to this is
that the query coordinator, because of a limitation in the current implementation, ignores the execution statistics of
the slave processes working for it. The following execution plan generated by the
dbms_xplan
package illustrates this.
Notice how the values of the
Starts
,
A-Rows
, and
Buffers
columns are set to 0 except for the operation executed by
the query coordinator (
PX COORDINATOR
):
SQL> SELECT * FROM table(dbms_xplan.display_cursor('6j5z013saaz9r',0,'iostats last'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100K| 16 |
| 1 | PX COORDINATOR | | 1 | 100K| 16 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 0 | 0 |
|* 3 | HASH JOIN | | 0 | 0 | 0 |
| 4 | PX RECEIVE | | 0 | 0 | 0 |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 0 | 0 |
| 6 | PX BLOCK ITERATOR | | 0 | 0 | 0 |
|* 7 | TABLE ACCESS FULL | T | 0 | 0 | 0 |
| 8 | PX PARTITION HASH ALL | | 0 | 0 | 0 |
| 9 | TABLE ACCESS FULL | T | 0 | 0 | 0 |
----------------------------------------------------------------------------
When dealing with cursors in the library cache, a possible workaround is to
not
specify
last
in the
format
parameter. In fact, if the SQL statement was executed only once (you can check it by looking at the
Starts
column
for the
PX COORDINATOR
operation), you will see the sum of the work performed by all slave processes. Unfortunately,
that workaround doesn't work when either the execution plan uses several data flow operations or, when, in a RAC
environment, part of the slave processes are allocated from one or several remote database instances. The following
example, which shows the same child cursor as the previous one, illustrates a case where it works:
SQL> SELECT * FROM table(dbms_xplan.display_cursor('6j5z013saaz9r',0,'iostats'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Reads |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100K| 16 | 0 |
| 1 | PX COORDINATOR | | 1 | 100K| 16 | 0 |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 0 | 0 | 0 |
|* 3 | HASH JOIN | | 2 | 100K| 2719 | 2464 |
| 4 | PX RECEIVE | | 2 | 100K| 0 | 0 |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | 0 | 0 | 0 | 0 |
