Database Reference
In-Depth Information
Even though a stored outline is applied during the generation of an execution plan, that application doesn't
mean that the execution plan intended to be generated is actually chosen by the query optimizer. This caveat can be
very confusing. It's all the more so because the output of the
dbms_xplan
package, and the
outline_category
column
of the
v$sql
view, show that a stored outline has been used during the parse phase. The following example, which is
an excerpt of the output generated by the
outline_unreproducible.sql
script, illustrates:
SQL> SELECT * FROM t WHERE n = 1970;
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
|* 2 |
INDEX RANGE SCAN
|
I
|
----------------------------------------------------
2 - access("N"=1970)
Note
-----
-
outline "OUTLINE_UNREPRODUCIBLE" used for this statement
SQL> DROP INDEX i;
SQL> SELECT * FROM t WHERE n = 1970;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
TABLE ACCESS FULL
|
T
|
----------------------------------
1 - filter("N"=1970)
Note
-----
-
outline "OUTLINE_UNREPRODUCIBLE " used for this statement
One of the most important properties of stored outlines is that they're detached from the code. Nevertheless,
that could lead to problems. In fact, because there is no direct reference between the stored outline and the SQL
statement, it's possible that a developer will completely ignore the existence of the stored outline. As a result, if the
developer modifies the SQL statement in a way that leads to a modification of its signature, the stored outline will
no longer be used. Similarly, when you deploy an application that needs some stored outlines to perform correctly,
during the database setup you must not forget to install them.
You must be aware that stored outlines aren't dropped when the objects they depend on are dropped. This
isn't necessarily a problem, however. For example, if a table or an index needs to be re-created because it must be
reorganized or moved, it's a good thing that the stored outlines aren't dropped; otherwise, it would be necessary to
re-create them.
