 |
|
Values for the other_tag Column in plan_table
Oracle Tips by Burleson Consulting |
To fully understand a parallel execution
plan, you must review the possible values for the other_tag
column in the plan_table. This column is used by Oracle to
provide additional detail about the type of parallel operation that
is being performed during the execution of the query. Here are the
possible values for other_tag:
-
Parallel_to_serial This
is usually the first operation in the execution plan, and it is
where a parallel full-table scan is passed to the query
coordinator for merging.
-
Parallel_from_serial
This is a condition where the parallel processes wait for a serial
operation to complete. In many cases, this is a warning that your
query may not be optimized, since the parallel processes are not
allowed to begin immediately upon execution of the query.
-
Parallel_to_parallel
This tag is seen in cases such as a sort merge join where a
parallel full-table scans is immediately followed by a parallel
disk sort in the TEMP tablespace.
-
Parallel_combined_with_parent
This is a parallel full-table scan that is combined with an
index lookup, as is the case with a nested loop join.
-
Parallel_combined_with_child
This is a case where a parallel full-table scan is combined
with a child operation.
-
Serial This is a linear
operation such as an index scan.
WARNING: Always investigate an
other_tag value of parallel_from_serial. This is because
your query may have a bottleneck whereby the parallel query slaves
are waiting unnecessarily for a serial operation, such as an index
range scan.
Now that you see the basics of parallel
execution plans, let’s explore some of the typical implementations
of parallel join operations.
Nested Loop Joins with Parallel Query
In a nested loop join, Oracle normally uses
indexes to join the tables. However, you can create an execution
plan that will invoke a nested loop join that performs a parallel
full-table scan against one of the tables in the join. In general,
you should perform the parallel full-table scans on only one of the
tables that are being joined, and this is normally the driving table
for the query. As you will recall, in the CBO the driving table is
the first table in the from clause.
Here is an example of a query execution plan
that is forced to perform a nested loop join with a parallel query
on the emp table. Note that we have invoked the ordered
hint to direct the CBO to evaluate the tables in the order they are
presented in the where clause.
select
/*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
Here is the execution plan for this query. There we
see the parallel operations on the emp table.
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
OTHER_TAG
----------------------------------------------------------------------
SELECT STATEMENT
329
NESTED LOOPS
1
PARALLEL_TO_SERIAL
TABLE ACCESS
FULL EMP
1
PARALLEL_COMBINED_WITH_PARENT
TABLE ACCESS
BY INDEX ROWID BONUS
2
PARALLEL_COMBINED_WITH_PARENT
INDEX
RANGE SCAN ENAME_BONUS_IDX
1
Depending on the query, a parallel nested
loop join will often provide excellent performance. However,
depending upon the characteristics of the data in your tables, you
may find that a parallel sort merge join or hash join will offer
faster response time.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
