The sequence of operations that Oracle performs to execute a SQL statement is called an execution plan. Sometimes it’s useful to look at the execution plan for a statement, in order to determine whether the plan picked by the optimizer is an efficient one. You can use Oracle’s EXPLAIN PLAN statement for this purpose. EXPLAIN PLAN takes a SQL statement, determines the execution plan, and stores that plan in a special table known as the plan table.

The plan table used by EXPLAIN PLAN is most often named PLAN_TABLE. The columns of the table describe various aspects of the execution plan. Two columns are of special importance for parallel execution:

When a query is executed in parallel, make sure that you don’t have any serial execution steps in the execution plan. You can check for that by using EXPLAIN PLAN to view the execution plan of your parallel SQL statements and checking the values for the OTHER_TAG column. The values that you want to see are the following:

Any other value indicates a serial operation, which may represent a bottleneck. The following example shows an execution plan for a statement with just such a bottleneck. Notice the DEGREE values for the two tables involved in the query. You’ll see that these have a drastic effect on the query’s performance:

SQL> SELECT table_name, degree, instances FROM user_tables;

TABLE_NAME                         DEGREE  INSTANCES
------------------------------ ---------- ----------
DEPTTEST                                1          1
EMPTEST                                 4          1

2 rows selected.

SQL> EXPLAIN PLAN SET statement_id = 's1' FOR
  2  SELECT empno, ename, sal, comm, dname FROM
  3  emptest e, depttest d
  4  WHERE e.deptno = d.deptno
  5  ORDER BY empno;

Explained.

SQL> SELECT LPAD(' ',2*(level-1))||operation||' '||options
  2  ||' '||object_name
  3  || DECODE(other_tag,NULL,'','(') || other_tag 
  4  || DECODE(other_tag,NULL,'',')') "Query Plan"
  5  FROM plan_table
  6  START WITH id = 0 AND statement_id = '&stid'
  7  CONNECT BY PRIOR id = parent_id AND statement_id ='&stid';
Enter value for stid: s1
old   5: START WITH id = 0 AND statement_id = '&stid'
new   5: START WITH id = 0 AND statement_id = 's1'
Enter value for stid: s1
old   6: CONNECT BY PRIOR id = parent_id AND statement_id ='&stid'
new   6: CONNECT BY PRIOR id = parent_id AND statement_id ='s1'

Query Plan
--------------------------------------------------------------------
SELECT STATEMENT
  SORT ORDER BY (PARALLEL_TO_SERIAL)
    MERGE JOIN  (PARALLEL_TO_PARALLEL)
      SORT JOIN (PARALLEL_COMBINED_WITH_PARENT)
        TABLE ACCESS FULL EMPTEST(PARALLEL_TO_PARALLEL)
      SORT JOIN (PARALLEL_COMBINED_WITH_PARENT)
        TABLE ACCESS FULL DEPTTEST(PARALLEL_FROM_SERIAL)

7 rows selected.

This execution plan indicates that the table scan of the DEPTTEST table is being performed in serial. You can tell this because the OTHER_TAG for that step is PARALLEL_FROM_SERIAL. The reason for this step is because the DEPTTEST table has DEGREE set to 1 in its definition. The problem, though, is that the results of this serial operation are being fed into a parallel operation. It’s unlikely that a serial table scan will be able to feed a parallel operation fast enough to take full advantage of that parallelism.

One way to fix the problem, and get rid of the PARALLEL_FROM_SERIAL step, is to increase the DEGREE setting for the DEPTTEST table. This example shows how to do this and then shows the resulting change in the execution plan:

SQL> ALTER TABLE depttest PARALLEL (DEGREE 4);

Table altered. 

SQL> EXPLAIN PLAN SET statement_id = 's4' FOR
  2  SELECT empno, ename, sal, comm, dname FROM
  3  emptest e, depttest d
  4  WHERE e.deptno = d.deptno
  5  ORDER BY empno
  6  /

Explained. 

SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
  2  ||' '||object_name
  3  || DECODE(other_tag,NULL,'','(') || other_tag 
  4  || DECODE(other_tag,NULL,'',')') "Query Plan"
  5  FROM plan_table
  6  START WITH id = 0 AND statement_id = '&stid'
  7  CONNECT BY PRIOR id = parent_id AND statement_id ='&stid';
Enter value for stid: s4
old   6: START WITH id = 0 AND statement_id = '&stid'
new   6: START WITH id = 0 AND statement_id = 's4'
Enter value for stid: s4
old   7: CONNECT BY PRIOR id = parent_id AND statement_id ='&stid'
new   7: CONNECT BY PRIOR id = parent_id AND statement_id ='s4'

Query Plan
--------------------------------------------------------------------
SELECT STATEMENT
  SORT ORDER BY (PARALLEL_TO_SERIAL)
    MERGE JOIN  (PARALLEL_TO_PARALLEL)
      SORT JOIN (PARALLEL_COMBINED_WITH_PARENT)
        TABLE ACCESS FULL EMPTEST(PARALLEL_TO_PARALLEL)
      SORT JOIN (PARALLEL_COMBINED_WITH_PARENT)
        TABLE ACCESS FULL DEPTTEST(PARALLEL_TO_PARALLEL)

7 rows selected.

Notice the change in the execution plan. The PARALLEL_TO_PARALLEL value in the last step indicates that the depttest table now will be scanned in parallel. You now have one parallel operation feeding another parallel operation, and the potential serial bottleneck has been removed.