SQL Optimization Techniques - Troubleshooting Oracle Performance - page 376

SQL> UPDATE ol$hints
2 SET ol_name = 'P_OUTLINE_EDITING'
3 WHERE ol_name = 'P_OUTLINE_EDITING_HINTED';
Here is the list of hints associated with the private stored outline after the swap. The only difference is that the
index hint as been replaced by the full hint:
SQL> UPDATE ol$hints
2 SET ol_name = 'P_OUTLINE_EDITING'
3 WHERE ol_name = 'P_OUTLINE_EDITING_HINTED';
HINT_TEXT
-------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
To make sure that the in-memory copy of the stored outline is synchronized with the changes, you should
execute the following PL/SQL call:
SQL> execute dbms_outln_edit.refresh_private_outline('P_OUTLINE_EDITING')
Then, to activate and test the private stored outline, set the use_private_outlines initialization parameter to
TRUE , or to the name of the category to which the private stored outline belongs. Note how the full table scan in the
execution plan and the message in the Note section both confirm the use of the private stored outline. For example:
SQL> ALTER SESSION SET use_private_outlines = TRUE ;
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 1970;
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic +note'));
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Note
-----
- outline "P_OUTLINE_EDITING" used for this statement
Once you're satisfied with the private stored outline, you can publish it as a public stored outline with the
following SQL statement:
SQL> CREATE PUBLIC OUTLINE outline_editing FROM PRIVATE p_outline_editing;