Database Reference
In-Depth Information
To know whether the query optimizer is using a stored outline, you can take advantage of the functions available
in the
dbms_xplan
package. In fact, as shown in the following example, the
Note
section of their output explicitly
provides the needed information:
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 1970;
SQL> SELECT * FROM table(dbms_xplan.display);
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
1 - filter("N"=1970)
Note
-----
-
outline "OUTLINE_FROM_TEXT" used for this statement
For a cursor stored in the library cache, the
outline_category
column of the
v$sql
view informs whether a
stored outline was used during the generation of the execution plan. Unfortunately, only the category is given. The
name of the stored outline itself remains unknown. If no stored outline was used, the column is set to
NULL
.
A method that can be used to know whether a stored outline is used over a period of time is to reset its utilization
flag with the
clear_used
procedure in the
dbms_outln
package. Then, by checking the flag after a while, you can
determine whether the stored outline was used. However, no detailed information about the utilization (for example,
the number of times or when) is given:
SQL> execute dbms_outln.clear_used(name => 'OUTLINE_FROM_TEXT')
SQL> SELECT used
2 FROM user_outlines
3 WHERE name = 'OUTLINE_FROM_TEXT';
USED
------
UNUSED
SQL> SELECT * FROM t WHERE n = 1970;
SQL> SELECT used
2 FROM user_outlines
3 WHERE name = 'OUTLINE_FROM_TEXT';
USED
------
USED
