Stored Outlines also known as plan stability preserves execution plan regardless of any changes perform in system environment. It stores the execution plan for a particular point in time for a query and maintains it. The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables. [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS are the dictionary views for the above tables, which can be used for finding out the details for the stored outlines.
The plan stability are used to force the optimizer to choose a plan defined by stored outline other than making its own choice.

Note: Its recomended to monitor the affects of the stored outlines on certain intervals. We can not predict, the plan which works perfect now may not be on later stage.

For detail on Stored Outline :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm

Here I will demonstrate the process in short.

Connecting to the database as shri user. Selecting a table dept1 with where clause.

20:16:04 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.02

Finding out the hashvalue for the query.

20:16:10 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- ------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.11

Based on hash value we will find the sql plan for the given query.

20:17:05 SHRI on 14-JUN-11 at orcl >set lin 200
20:17:39 SHRI on 14-JUN-11 at orcl >col OBJECT_NAME for a30
20:17:40 SHRI on 14-JUN-11 at orcl >col OPERATION for a30
20:17:40 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       3
1          0          TABLE ACCESS                   DEPT1                                   3           1

Elapsed: 00:00:00.06

In the given query we can see the query is going for full table scan. Obvious, as there is no index on column deptno. Lets create a index on deptno column of dept1 table.
Creating index as ind_dept_dno.

20:18:25 SHRI on 14-JUN-11 at orcl >create index ind_dept_dno on dept1(DEPTNO);

Index created.

Elapsed: 00:00:00.03

Lets again execute the query,

20:18:35 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.02
20:18:41 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- --------------
3041174022 select * from dept1 where DEPTNO=10

3056837605
select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.08
20:18:44 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       2
1          0          TABLE ACCESS                   DEPT1                                   2           1
2          1          INDEX                          IND_DEPT_DNO                            1           1

Elapsed: 00:00:00.01
20:18:58 SHRI on 14-JUN-11 at orcl >

Now we can see that the execution plan has changed. Its considering the index.
We will start the process with the help of Stored Outlines

Connecting to a new session as sys. For automatic creation of stored outline, we need to set the below parameter. This can be set at session level or instance level.

20:23:56 SYS on 14-JUN-11 at orcl >ALTER SYSTEM SET create_stored_outlines=TRUE;

System altered.

Elapsed: 00:00:00.12
20:23:58 SYS on 14-JUN-11 at orcl >

Providing the required grants to user shri.

20:24:40 SYS on 14-JUN-11 at orcl >GRANT CREATE ANY OUTLINE TO SHRI;

Grant succeeded.

Elapsed: 00:00:00.00
20:24:52 SYS on 14-JUN-11 at orcl >GRANT EXECUTE_CATALOG_ROLE TO SHRI;

Grant succeeded.

Elapsed: 00:00:00.02
20:24:57 SYS on 14-JUN-11 at orcl >

As I want to show a demo, I am going to drop this index, so the process can be smoothly explained.
Dropping index ind_dept_dno.

20:23:06 SHRI on 14-JUN-11 at orcl >drop index ind_dept_dno;

Index dropped.

Elapsed: 00:00:00.08

Checking execution plan for the same query.

20:27:33 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.01
20:27:42 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- -------------------------------------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.09
20:27:44 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       3
1          0          TABLE ACCESS                   DEPT1                                   3           1

Elapsed: 00:00:00.02

Well lets say I want this to be my execution plan regardless of any changes, whether any parameter changes or any index level changes.
Creating a plan stability for the query. To ease the management we will define a category for this stored outline, or else it will be assigned a default category. Outlines can also be created with DBMS_OUTLN.CREATE_OUTLINE procedure.

20:27:54 SHRI on 14-JUN-11 at orcl >create outline shri_dept for category shri_outlines on
22:19:04   2  select * from dept1 where DEPTNO=10;

Outline created.

Elapsed: 00:00:00.41

Finding details of stored outline.

22:19:18 SHRI on 14-JUN-11 at orcl >select name, category, sql_text from user_outlines where category = 'SHRI_OUTLINES';

NAME                           CATEGORY                       SQL_TEXT
------------------------------ ------------------------------ ----------------------------------------------------------
SHRI_DEPT                      SHRI_OUTLINES                  select * from dept1 where DEPTNO=10

Elapsed: 00:00:00.14

Getting out the detail operation for the plan.

22:21:36 SHRI on 14-JUN-11 at orcl >select node, stage, join_pos, hint from user_outline_hints where name = 'SHRI_DEPT';

NODE      STAGE       JOIN_POS   HINT
---------- ---------- ---------- --------------------------------------------------------------------------------
1          1          1          FULL(@"SEL$1" "DEPT1"@"SEL$1")
1          1          0          OUTLINE_LEAF(@"SEL$1")
1          1          0          ALL_ROWS
1          1          0          DB_VERSION('11.2.0.1')
1          1          0          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
1          1          0          IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected.

Elapsed: 00:00:00.20

The Below query shows that the outlines are out used.

22:23:16 SHRI on 14-JUN-11 at orcl >select name, category, used from user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_11061420274282481  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274282480  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274418682  DEFAULT                        UNUSED
SYS_OUTLINE_11061420275440683  DEFAULT                        UNUSED
SHRI_DEPT                      SHRI_OUTLINES                  UNUSED
SYS_OUTLINE_11061422191798412  DEFAULT                        UNUSED
SYS_OUTLINE_11061422213669213  DEFAULT                        UNUSED
SYS_OUTLINE_11061422231628514  DEFAULT                        UNUSED
SYS_OUTLINE_11061422250130615  DEFAULT                        UNUSED

9 rows selected.

Elapsed: 00:00:00.05

Well, I will execute the same query again to check this out.

22:25:01 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.02

Finding out the output.

22:25:35 SHRI on 14-JUN-11 at orcl >select name, category, used from user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_11061420274282481  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274282480  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274418682  DEFAULT                        UNUSED
SYS_OUTLINE_11061420275440683  DEFAULT                        UNUSED
SHRI_DEPT                      SHRI_OUTLINES                  UNUSED
SYS_OUTLINE_11061422191798412  DEFAULT                        UNUSED
SYS_OUTLINE_11061422213669213  DEFAULT                        UNUSED
SYS_OUTLINE_11061422231628514  DEFAULT                        UNUSED
SYS_OUTLINE_11061422250130615  DEFAULT                        UNUSED

9 rows selected.

Elapsed: 00:00:00.03

Still no change. Well this is because we have not enabled query rewrite as well as we have not stated which outline to be used. This can be done at session or instance level.

Connecting to new session as sys.

20:24:57 SYS on 14-JUN-11 at orcl >alter system set query_rewrite_enabled=TRUE;

System altered.

Elapsed: 00:00:00.41
22:26:45 SYS on 14-JUN-11 at orcl >alter system set use_stored_outlines=SHRI_OUTLINES;

System altered.

Elapsed: 00:00:00.04
22:27:17 SYS on 14-JUN-11 at orcl >

Connecting session as shri user.

22:27:20 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.05
22:27:21 SHRI on 14-JUN-11 at orcl >select name, category, used from user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_11061420274282481  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274282480  DEFAULT                        UNUSED
SYS_OUTLINE_11061420274418682  DEFAULT                        UNUSED
SYS_OUTLINE_11061420275440683  DEFAULT                        UNUSED
SHRI_DEPT                      SHRI_OUTLINES                  USED
SYS_OUTLINE_11061422191798412  DEFAULT                        UNUSED
SYS_OUTLINE_11061422213669213  DEFAULT                        UNUSED
SYS_OUTLINE_11061422231628514  DEFAULT                        UNUSED
SYS_OUTLINE_11061422250130615  DEFAULT                        UNUSED

9 rows selected.

Elapsed: 00:00:00.04

The output shows that the query is using the defined execution plan.

22:27:22 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.00
22:30:15 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- ------------------------------------------------------------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.92

22:30:19 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       3
1          0          TABLE ACCESS                   DEPT1                                   3           1

Elapsed: 00:00:00.04

Lets find it out, whether the plan changes after creating index on deptno column.

23:00:06 SHRI on 14-JUN-11 at orcl >create index ind_dept_dno on dept1(deptno);

Index created.

Elapsed: 00:00:00.02
23:00:13 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.01
23:00:22 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- -------------------------------------------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.06
23:00:23 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       3
1          0          TABLE ACCESS                   DEPT1                                   3           1

Elapsed: 00:00:00.02

I can see that, it has not considered the index, even if the index is present for deptno column of dept1 table.
Dropping the outline and then checking out for the new plan.

23:00:28 SHRI on 14-JUN-11 at orcl >exec dbms_outln.drop_by_cat(cat => 'SHRI_OUTLINES');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14

Just checking index existence,

22:59:54 SYS on 14-JUN-11 at orcl >@ind_col
Enter value for tb_name: dept1
old  10: and B.TABLE_NAME like upper('%&tb_name%')
new  10: and B.TABLE_NAME like upper('%dept1%')

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
COLUMN_NAME        COL_POS    COL_LEN CHAR_LENGTH DESC STATUS
--------------- ---------- ---------- ----------- ---- --------
IND_DEPT_DNO                   NORMAL                      SHRI                           DEPT1
DEPTNO                   1         22           0 ASC  VALID

Elapsed: 00:00:00.58

Checking out for query, which should go for index scan.

23:02:11 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.01
23:02:16 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.06
23:02:17 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       3
1          0          TABLE ACCESS                   DEPT1                                   3           1

Elapsed: 00:00:00.01

Still it is showing the same output. So, the optimizer to consider its own plan ,i.e. to go with index range scan. We need to flush shared pool or bounce the database.

23:04:09 SYS on 14-JUN-11 at orcl >shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
23:07:59 SYS on 14-JUN-11 at orcl >startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             255853008 bytes
Database Buffers          272629760 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
23:08:13 SYS on 14-JUN-11 at orcl >

I can see now the plan has changed and now it is going for index scan

23:12:10 SHRI on 14-JUN-11 at orcl >select * from dept1 where DEPTNO=10;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK

Elapsed: 00:00:00.08
23:12:14 SHRI on 14-JUN-11 at orcl >select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%';

HASH_VALUE SQL_TEXT
---------- ---------------------------------------------------------------------------
3041174022 select * from dept1 where DEPTNO=10

3056837605 select hash_value, sql_text from v$sql where sql_text like '%from dept1 where%'

Elapsed: 00:00:00.16
23:12:15 SHRI on 14-JUN-11 at orcl >select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value';
Enter value for hash_value: 3041174022
old   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='&hash_value'
new   1: select id,parent_id, operation, object_name, cost, CARDINALITY from v$sql_plan where hash_value='3041174022'

ID         PARENT_ID  OPERATION                      OBJECT_NAME                          COST CARDINALITY
---------- ---------- ------------------------------ ------------------------------ ---------- -----------
0                     SELECT STATEMENT                                                       2
1          0          TABLE ACCESS                   DEPT1                                   2           1
2          1          INDEX                          IND_DEPT_DNO                            1           1

Elapsed: 00:00:00.03
23:12:17 SHRI on 14-JUN-11 at orcl >

Or

23:15:48 SYS on 14-JUN-11 at orcl >alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
23:15:57 SYS on 14-JUN-11 at orcl >

Thus, the changes to get affected you need to flush the shared pool. You can also go for,

alter system set create_stored_outlines=FALSE;

This switch off the automatic creation of stored outlines.

Advertisements