Question: How does Oracle manage
a SQL plan outline. I have heard of optimizer plan
stability but I do not understand how to display a stored
outline or store a stored outline. How do I display a
stored outline?
Answer: Oracle
stored outlines have been in-use since Oracle8i for
"freezing" SQL execution plans, and you can also use
"optimizer plan stability" to swap a bad execution plan with
a good execution plan.
Also see my notes on
swapping stored outlines and
this article on swapping stored oulines. Also see
my SQL
plan management notes.
This snippet shows how to display the plan outline fr a
SQL statement.
SQL> -- Get the outline plan for query
with index hint
SQL> explain plan into plan_table for
2 select /*+index(test testi) */ * from test;
Explained.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,
'OUTLINE'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 2400950076
--------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost
--------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 31471 | 7191K|
1190
| 1 | TABLE ACCESS BY INDEX
ROWID| TEST | 31471 | 7191K| 1190
|
2 | INDEX FULL SCAN
| TESTI | 31471 | |
80
--------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OWNER"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_unnest_subquery'
'false')
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
