BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter