in ,

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


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:



Author Profile

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

What do you think?

400 Points
Upvote Downvote

Breaking changes coming to the iOS WebView in Apache Cordova

Tuning advisors

How to use SQL Access Advisor?