Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

Donald K. Burleson

Oracle Tips

 

Display the execution plan for parallel queries

 

It is not easy to view the execution plan for SQL that is using parallel query. For parallel queries, it is important to display the contents of the other_tag in the execution plan display from the plan_table.

 

Some SQL professionals keep a special version of plan.sql called pq_plan.sql for displaying details about parallel execution. Here is the script that I use:

 

pq_plan.sql

set echo off
set long 2000
set pagesize 10000

column query       heading "Query Plan" format a80
column other       heading "PQO/Remote Query" format a60 word_wrap
column x           heading " " format a18

select distinct
   object_node "TQs / Remote DBs"
from
   plan_table
where
   object_node is not null
order by
   object_node;

select lpad(' ',2*(level-1))||operation||' '||options||' '
    ||object_name||' '
    ||decode(optimizer,'','','['||optimizer||'] ')
    ||decode(other_tag,'',decode(object_node,'','','['||object_node||']')
     ,'['||other_tag||' -> '||object_node||']')
    ||decode(id,0,'Cost = '||position) query
   ,null  x
   ,other
from
   plan_table
start with id = 0
connect by prior id = parent_id;

 

Here is a sample query with parallel full-table scans. Let’s examine the different display formats for the execution plans for plan.sql and pq_plan.sql.

select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */

   e.ename,

   hiredate,
   b.comm
from
   emp e,

   bonus b

where
   e.ename = b.ename

;

 

Here is the execution plan displayed with pq_plan.sql. Let’s take a look at this output and compare the display formats.

TQs / Remote DBs
----------------------------------------------------------------------
:Q36000
:Q36001
:Q36002


Query Plan
----------------------------------------------------------------------
                   PQO/Remote Query
------------------ ---------------------------------------------------
SELECT STATEMENT   [FIRST_ROWS] Cost = 5


  MERGE JOIN   [PARALLEL_TO_SERIAL -> :Q36002]
                   SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) */
                   A1.C0,A1.C1,A2.C1 FROM :Q36000 A1,:Q36001 A2 WHERE
                   A1.C0=A2.C0

    SORT JOIN  [PARALLEL_COMBINED_WITH_PARENT -> :Q36002]


      TABLE ACCESS FULL EMP [PARALLEL_TO_PARALLEL -> :Q36000]
                   SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ENAME"
                   C0,A1."HIREDATE" C1 FROM "EMP" A1
                   WHERE ROWID BETWEEN :B1
                   AND :B2

    SORT JOIN  [PARALLEL_COMBINED_WITH_PARENT -> :Q36002]


      TABLE ACCESS FULL BONUS [PARALLEL_TO_PARALLEL -> :Q36001]
                   SELECT /*+ NO_EXPAND ROWID(A1) */
                   A1."ENAME" C0,A1."COMM" C1
                   FROM "BONUS" A1 WHERE ROWID BETWEEN :B1 AND :B2

Here you see more detail about the internals of the execution plan, including details about the parallel query execution modes (other_tag in plan_table) and details about the tables that participate in the query. A complete description of these table access methods is beyond the scope of this tip, but you can get complete information in the Oracle Press Book “Oracle High Performance SQL Tuning”.

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.