Stored outlines – Laurent Schneider

Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp SQL> select empno from emp where ename='SCOTT'; EMPNO ---------- 7788

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.

SQL> create or replace outline o for category emp_scott on select empno from emp where ename='SCOTT';


Outline created.
SQL> create unique index i on emp(ename)

  tablespace my_slow_tape_drive;
Index created.
SQL> set timi on

SQL> set autot on exp

SQL> select empno from emp where ename='SCOTT';

     EMPNO

----------

      7788
Elapsed: 01:45:59.95

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 2 1 INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older 😉 ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline

SQL> alter session set use_stored_outlines=emp_scott;


Session altered.
Elapsed: 00:00:00.00

SQL> select empno from emp where ename='SCOTT';

     EMPNO

----------

      7788
Elapsed: 00:00:00.01
Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1

   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text from v$sql where sql_text like 'select%SCOTT%'; LAST_LOAD_TIME OUTLINE_C SQL_TEXT ------------------- --------- ---------------------------- 2008-10-16/09:42:30 select empno from emp where 2008-10-16/09:46:50 EMP_SCOTT select empno from emp where

The plan using the outline is now used

2 thoughts on “Stored outlines”