How to Create and Modify a Stored Outline Oracle Tips by Burleson Consulting

The best way to show how to use stored outlines to change the execution plan for a SQL statement is to illustrate the procedure with a simple example. Let’s start with a simple query to display the sum of all salaries for each department.

select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

Here is the output from this query:

DNAME          LOC             SUM(SAL)
-------------- ------------- ----------
ACCOUNTING     NEW YORK            8750

Find the Fastest Execution Plan

Now let’s take a look at the execution plan for this query. Note that it uses all_rows optimization and does a full-table scan even though there is an index on the emp table:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     5
  SORT
GROUP BY                                                             1
    NESTED LOOPS
OUTER                                                                1
      TABLE ACCESS
BY INDEX ROWID                 DEPT                                  1
        INDEX
RANGE SCAN                     DEPT_DEPT                             1
      TABLE ACCESS
FULL                           EMP                                   2

Of course, this query will run faster if we direct the SQL to use the emp index to access the emp rows. When we reexecute the query with a rule hint, we see the full-table scan disappear.

select /*+ rule */
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

Let’s assume that we have done a timing of this query, and it is faster with rule-based optimization because it uses the dept_dept index instead of a full-table scan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
  SORT
GROUP BY                                                             1
    NESTED LOOPS
OUTER                                                                1
      TABLE ACCESS
BY INDEX ROWID                 DEPT                                  1
        INDEX
RANGE SCAN                     DEPT_DEPT                             1
      TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
        INDEX
RANGE SCAN                     DEPT_EMP                              1

If we assume that the emp and dept tables are very large, then changing the SQL to use the index will improve the performance of the query. Of course, you should always verify your hint by reexecuting the SQL with the SQL*Plus set timing on command to ensure that the hint improves performance. Now let’s create our first stored outline.

Create the Stored Outline for the Original Query

Outlines are created using the CREATE OUTLINE command. The syntax for this command is:

CREATE [OR REPLACE] OUTLINE
   outline_name
[FOR CATEGORY category_name]
ON
   sql_statement;

Where:

• outline_name This is a unique name for the outline. Automatic outlines are stored as SYS_OUTLINE-nnn, where nnn is a large unique number.

• [FOR CATEGORY category_name] This optional clause allows more than one outline to be associated with a single query by specifying multiple categories, each named uniquely.

• ON sql_statement This specifies the SQL statement for which the outline is prepared.

To illustrate the syntax, here we create a stored outline for the original SQL.

create or replace outline
   cbo_sql
on
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

The query to display stored outline plans is very different in format than the explain plan utility. Here is the code to display the execution plan inside the ol$hints table.

outline.sql

set echo off;
set verify off;
set feedback off;

column hint format a40;

select
   sql_text
from
   Remote DBA_outlines
where
   name = upper('&1');

select distinct
   lpad(' ',2*(level-1))||hint_text hint,
   hint#,
   table_tin,
   stage#
from
   outln.ol$hints
start with
   hint#=1
connect by prior
   hint# = hint#-1
and
   ol_name = upper('&1')
order by
   stage#,
   hint#
;

Now when we query the stored outline, we see a very different form of the execution plan:

SQL> @outline cbo_sql

SQL_TEXT
----------------------------------------------------------------
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+)


HINT                                     HINT#  TABLE_TIN     STAGE#
----------------------------------- ---------- ---------- ----------
                  NOREWRITE                 10          0          1
                NOREWRITE                    9          0          2
NO_EXPAND                                    1          0          3
  PQ_DISTRIBUTE(EMP NONE NONE)               2          1          3
    USE_NL(EMP)                              3          1          3
      ORDERED                                4          0          3
        NO_FACT(EMP)                         5          1          3
          NO_FACT(DEPT)                      6          2          3
            FULL(EMP)                        7          1          3
              FULL(DEPT)                     8          2          3

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.