Database Reference
In-Depth Information
You're also able to manually create a stored outline by referencing a cursor in the library cache. The
following example, an excerpt of the output generated by the
outline_from_sqlarea.sql
script, shows how to
select the cursor in the library cache and to create the stored outline through the
create_outline
procedure in
the
dbms_outln
package:
SQL> SELECT hash_value, child_number
2 FROM v$sql
3 WHERE sql_text = 'SELECT * FROM t WHERE n = 1970';
HASH_VALUE CHILD_NUMBER
---------- ------------
308120306 0
SQL> BEGIN
2 dbms_outln.create_outline(hash_value => '308120306',
3 child_number => 0,
4 category => 'test');
5 END;
6 /
■
the
create_outline
procedure doesn't create a stored outline based on the execution plan associated to
the referenced cursor. instead, it takes the text of the SQL statement associated to the cursor and reparses it. therefore,
the execution plan associated to the stored outline isn't necessarily identical to the one associated to the cursor. For
example, a different execution environment can easily lead to another execution plan.
Caution
The
create_outline
procedure accepts only the three parameters shown in the example. This means that the
name of the stored outline is automatically generated. To find out the system-generated name, you have to query a
view like
user_outlines
. Here's an example of query that returns the name of the last-created stored outline:
SQL> SELECT name
2 FROM user_outlines
3 WHERE timestamp = (SELECT max(timestamp) FROM user_outlines);
NAME
-----------------------------
SYS_OUTLINE_13072411155434901
Changing the system-generated name to something more useful is advisable. The next section describes, among
other things, how to do that.
Altering Stored Outlines
To change the name of a stored outline, you have to execute the
ALTER OUTLINE
statement:
ALTER OUTLINE SYS_OUTLINE_13072411155434901 RENAME TO outline_from_sqlarea
