Database Reference
In-Depth Information
Table 8-1. XPlan Column Description
Column Description
ID A number assigned to each step in the execution plan.
Operation Name of the internal operation performed. In the first row generated, the column contains SELECT
STATEMENT . Other possible values in the first row include
INSERT STATEMENT
DELETE STATEMENT
UPDATE STATEMENT
Name
Name of the object (TABLE, INDEX, PARTITION, etc.) on which the specific operation/action is performed.
Rows
Number of rows returned during the operation.
Bytes
Estimate by the query optimization approach of the number of bytes accessed by the operation.
TempSpc
Temporary space, in bytes, used by the operation as estimated by the query optimizer's approach.
Cost
(%CPU)
Cost of the operation as estimated by the optimizers query approach. Cost is not determined for table
access operations. The value of this column does not have any particular unit of measurement; it is
merely a weighted value used to compare costs of execution plans. The value in parenthesis is the
percentage of CPU utilized in the operation.
Time
Elapsed time in seconds of the operation as estimated by query optimization.
TQ, nn
The table queue number used in the operation to communicated between the slaves.
IN-OUT
This is the table queue type. Some of the important notations to understand in the previous output are
P->P—Data is sent from one parallel operation to another.
P->S—Data is sent from a parallel operation to a serial operation.
S->P—Data is sent from a serial operation to a parallel operation.
PQ
Distrib
Contains the method in which rows are mapped to the query servers. Some of the values of this column
include
HASH : Maps rows to individual PX servers using a hash function on the join key. This method helps to achieve
an equal distribution of work for individual PX servers. Used for PARALLEL JOIN or PARALLEL GROUP BY .
RANGE : Maps rows to query servers using ranges of the sort key. Individual PX servers work on a range of
data so that the QC does not have to do any sorting, but only to present the individual parallel servers.
Used when the statement contains an ORDER BY clause.
BROADCAST : Broadcasts the rows of the entire table to each query server. This is normally used when
there is a large range on the number of rows contained between the tables involved in the operation.
Instead of redistributing rows from both result sets, the database sends the smaller result set to all PX
servers to guarantee the individual servers are able to complete their join operation. The small result set
may be produced in serial or parallel.
QC(ORDER) : The QC consumes the input in order, from the first to the last query server. Used when the
statement contains an ORDER BY clause.
QC(RANDOM) : The QC consumes the input randomly. Used when the statement does not have an ORDER
BY clause.
KEY : Key redistribution ensures result sets for individual key values to be clumped together. This is an
optimization that is primarily used for partial partition-wise joins to ensure only one side in the join has
to be redistributed.
ROUND ROBIN : This is normally the final redistribution before data is sent to the requesting process.
Certain redistribution steps contain an additional suffix to the preceding verbs in RAC environments,
called LOCAL . LOCAL redistribution is an optimization method used in RAC environments to minimize
interconnects traffic for inter-node parallel queries (IPQ).
 
 
Search WWH ::




Custom Search