Outlines categories are nothing but group of hints associated with a particular SQL statement. Oracle chooses optimal execution plan on it’s own while executing. There comes a time when we want to preserve execution plan. That is nothing but I want an execution with /*+ xyz – – – */ this kind of hint only. What is working fine today may not be OK tomorrow. If I maintained multiple plans then I can easily use it one by one depending on my business need.
By Using an stored outline I can force optimizer to choose sub-standard execution plan and it continuously required to monitor the performance of my execution plan over time.
To create a public or private outline, you must have the CREATE ANY OUTLINE system privilege.If you are creating a copy outline from a source outline, you must also have the role.You can enable or disable the use of stored outlines dynamically for an individual session or for the system:
Enable the USE_STORED_OUTLINES parameter to use public outlines.
Enable the USE_PRIVATE_OUTLINES parameter to use private stored outlines.
Here first I am executing sample SQL statement without using hint. Lets have a look on execution plan.
I have sample data for accidents due to traffic violations in Unite states that I have loaded in MST_TRAFFIC_VIOLATIONS
Optimizer have chosen optimum execution plan after I created Index. Even when I don’t specify hint optimizer will take it as it’s optimum execution plan for it.
In below snapshot I specified index hint.
In below snapshot I didn’t use Index hint still I got optimum execution.
I can store multiple hints on single outline category. The automatic creation of outlines is controlled using the CREATE_STORED_OUTLINES parameter than can be set at session or instance level using the following commands
CREATE OUTLINE outline name FOR CATEGORY category name
ON SELECT /*+ xyz */ ;
You can specify any one of the following statements: SELECT, DELETE, UPDATE, INSERT … SELECT, CREATE TABLE … AS SELECT.
Just have a look on below outline category one by one.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.
Here I queried DATE_TIME_STOP outline:
Outlines can be granted using following command:
GRANT CREATE ANY OUTLINE TO XYZ_USER;
GRANT EXECUTE_CATALOG_ROLE TO XYZ_USER;