|
|
|
|
Part 1 of this topic was covered in the May issue of The Pipelines Newsletter.
Using Stored Outlines
Now that the outlines have been stored, it is time to use them. Using a stored outline is as simple as setting the USE_STORED_OUTLINES initialization parameter to your category name. To enable this session to use the stored outline already created, issue the following:
ALTER SESSION SET use_stored_outlines=employee_application;
To turn off the use of stored outlines, set the parameter to FALSE:
ALTER SESSION SET use_stored_outlines=FALSE;
When an outline is enabled for your session, the optimizer looks in your category for the SQL statement just issued. If it finds it, then it uses the stored execution plan for that SQL statement. If the category does not contain your SQL statement, the DEFAULT category is examined. If the SQL statement is in the DEFAULT category, that stored outline is used.
It is often helpful to determine if a category is being used. The OUTLINE_CATEGORY column of the V$SQL view will contain the category name if the stored outline is being used for that SQL statement.
Administration of Stored Outlines
There are a few more topics related to administering stored outlines. To drop a stored outline, use the DROP OUTLINE command as follows:
DROP OUTLINE employee_application;
The category name is supplied. The outline will be removed from the OL$ and OL$HINTS table. Use the export utility to create a backup of your stored outline, as described above, before dropping it.
The ALTER OUTLINE command can be used to rename a category.
ALTER OUTLINE employee_application CHANGE CATEGORY TO emp_outline;
Note: Doing the above operation will cause your application to not be able to find the stored outline it is looking for unless you change the USE_STORED_OUTLINE parameter value as well.
DBMS_OUTLN
The DBMS_OUTLN supplied package can also be used to administer stored outlines. One of the problems with stored outlines is that it can be difficult to determine if the outline is even being used by any applications. A query of the OUTLINE_CATEGORY of V$SQL can be done to see if any cursors in the Shared Pool are currently using the outline category. Unfortunately, any older cursors will be aged out and a risk is run of deciding that the absence of a category in V$SQL means that the category has not been used, when it has! The DBA_OUTLINES view contains a column called USED to determine if the outline has ever been used or not. This flag can be cleared with the following procedure in DBMS_OUTLN:
exec dbms_outln.clear_used('employee_application');
Once the flag has been cleared, wait some sufficient period of time before checking the flag again to see if the outline category has been used since the flag was last reset.
If there are stored outlines that have not been used, use the DROP_UNUSED procedure to remove them from the database.
exec dbms_outln.drop_unused;
Alternatively, it is possible to drop stored outlines by category name with the DROP_BY_CAT procedure.
exec dbms_outln.drop_by_cat('employee_application');
The above is similar to the DROP OUTLINE command. Again, it is advisable to make a backup copy before dropping any stored outlines.
The DBMS_OUTLN procedure allows for a way of creating the stored outlines with a third method. The CREATE_OUTLINE procedure will create a stored outline for a SQL cursor currently in the Shared Pool. You will need the HASH_VALUE and CHILD_NUMBER from V$SQL for the SQL statements that you want to add to a stored outline category.
exec dbms_outln.create_outline('ky7r4nkloqqwm', 0,'employee_category');
Additionally, a category can be renamed with the UPDATE_BY_CAT procedure. The earlier example of renaming a category can be accomplished with DBMS_OUTLN as follows:
exec dbms_outln.update_by_cat('employee_application", 'emp_outline');