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”.