Practical Apps DBA

Oracle Apps DBA’s Blog

How to force hints using Outlines

Posted by Navdeep Saini on May 18, 2007

Oracle introduced plan stability in form of Outlines in version 8i. However I have always been intrigued as to how to force a hint to a query using outline. This is particularly useful for Apps DBA, as sometimes it is not possible to change the query and put the hint in it, e.g.
If a query is being fired from some executable which cannnot be edited, like INCOIN for Item Import in Oracle Apps.

Also, often, changing sql will lead to invalidations and will require recompilation which means we may need downtime, e.g. if you want to force hint to a query which is being fired from package, you will have to change the package and recompile it and its dependencies.

If you want to force a hint to a query in a running production system and do not have downtime to do it, here is an example how you can achieve it:

Note: Always make sure you get the exact sql from the shared_pool. Outlines are very sensitive and even one blank space will invalidate the outlines.

1. Take the sql from the shared_pool. (Use hash_value in 9i or sql_id in 10g).
e.g. The following sql is in shared_pool:
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

2. This sql sometimes uses index AD_PARALLEL_UPDATE_UNITS_U1 and you have to force use of AD_PARALLEL_UPDATE_UNITS_N1 by using hints….

select /*+ index( ad_parallel_update_units AD_PARALLEL_UPDATE_UNITS_N1FIRST_ROWS) +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

3. Create an outline on the original sql:
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2;

4. Create an outline for the hinted sql
select /*+ index( ad_parallel_update_units AD_PARALLEL_UPDATE_UNITS_N1) FIRST_ROWS) +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2;

5. Update the OUTLN.OL$HINTS table to replace the outline of orig sql with the “hinted” sql:

6. You can drop the “hinted” sql at this point:

7. At this point if the sql: select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2
is parsed again, it will use the outline and will be forced to use index AD_PARALLEL_UPDATE_UNITS_N1, no matter what. You have to make sure that the cursor for this sql is invalidated and query is parsed again, otherwise database will continue using the old plan, which was cached before until next hard parse of this sql. One way to ensure hard parse is to flush shared pool:
alter system flush shared_pool.
However you will have to be careful as this will invalidate all the cursors and will cause hard parsing for all the sqls that are being fired in the database.

8. You can also monitor if the query is using outline or not by running following sqls:

    1. select name,used from dba_outlines where name=’ADPARALLEL_ORIG’
    The column “USED” will show value of ‘USED’ or ‘UNUSED’ accordingly.

    2. Select sql_text,outline_category from v$sql where hash_value=’Your hash value’ — For 9i
    Select sql_text,outline_category from v$sql where sql_id=’Your sql id’ — For 10g

    The column “OUTLINE_CATEFORY” will show some value e.g. DEFAULT in our case as we created outline in default category.
    If the value is null then outlines are not being used. This is the most fool proof and quick way of checking whether the current sql in the sql area is using the outline or not.

    3. Run event 10053 for the sql. Event 10053 traces the CBO Computations. (refer metalink note#225598.1)

    1. alter session set events =’10053 trace name context forever, level 1;
    2. then run explain for the sql e.g.
    explain plan for
    select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

    A trace will be generated in your udump directory and look for words like outline in it. In 10g you will see something like:
    Outline Data:

Note: There is a funny problem until 9i. In order to enable usage of outlines in database you have to alter session or system to enable “use_stored_outlines = {TRUE|category|FALSE}” e.g. to Enable usage of “Default outline category” system-wide you will have to :
ater system set use_stored_outlines = true;
However during next restart of database this value will be lost and you will stop using outline that you have created above. One solution is to create system level “on database startup” trigger and run alter system command in it. This problem is not seen in 10g.


About these ads

16 Responses to “How to force hints using Outlines”

  1. Srini said


    Great job on outlines. Keep it up.

    Srini Thatipamula

  2. Srini
    Thanks for your encouraging words.


  3. Rashmi said

    Hi Nav,
    Very useful article.. :)
    But the only issue I faced was that even in 10g, I had to use the “alter system … “. Any idea why so?

  4. kishore said

    hi Nav,
    its very useful article, and i want to know how to invalidate cursor for sql( for select state,,), i dont want to do alter system (shared pool)…


  5. Ramesh Konagala said


  6. Kishore
    One way to invalidate cursor will be gather stats on it using dbms_stats (fnd_stats for apps) and set the flag “NO_INVALIDATE” to FALSE.


  7. This is very up-to-date information. I’ll share it on Digg.

  8. Pawan said

    Is it required to have hints in both the queries?
    I tried with one query having no hint somehow it didn’t work for me in Orcle 11g.


  9. samuel said

    iyDGzr kdgHq9bt4FuaL6c1dp85Uf

  10. samuel said

    bnkMoI kdgHq9bt4FuaL6c1dp85Uf

  11. wajid said


    I am having the same issue with WF_USER_LOCAL_ROLES table, I know which one is the better execution plan and its hash value. Any way I can just drop the BAD plan from one of the OUTLN tables.

    Also, why does Oracle choose plan randomly, I started facing this issue on select query on WF_USER_LOCAL_ROLES suddenly. I verified the both the plans were loaded at the same time.


  12. bastian said

    great job!

  13. Saravana said

    Good Job

  14. Nena said

    Also good website for oracle apps dba

  15. Pratheek said

    Hi Navdeep

    This is Pratheek, i have one doubt. can you please tell me what is the use of gss(Gather Schema stats). i need some info related to gss.


  16. Yang said

    Good job !

    This is still very helpful with a 11gR2 database at 2015 !
    Thanks a lot.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: