What are outlines and how these are used?

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

outlines

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.

outlines

In below snapshot I didn’t use Index hint still I got optimum execution.

outlines

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.

outlines

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

Outlines can be granted using following command:

GRANT CREATE ANY OUTLINE TO XYZ_USER;

GRANT EXECUTE_CATALOG_ROLE TO XYZ_USER;

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j