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:
CREATE OR REPLACE OUTLINE ADPARALLEL_ORIG
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2;

4. Create an outline for the hinted sql
CREATE OR REPLACE OUTLINE ADPARALLEL_HINT
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:
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,’ADPARALLEL_HINT’,’ADPARALLEL_ORIG’,’ADPARALLEL_ORIG’,’ADPARALLEL_HINT’)
WHERE OL_NAME IN (‘ADPARALLEL_ORIG’,’ADPARALLEL_HINT’);
commit;

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

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:
    /*+
    BEGIN_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.

References:Note:92202.1

About these ads

15 Responses to “How to force hints using Outlines”

  1. Srini said

    Navdeep,

    Great job on outlines. Keep it up.

    Thanks,
    Srini Thatipamula

  2. Srini
    Thanks for your encouraging words.

    regards
    Nav

  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?
    Rgds
    Rashmi

  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)…

    thanks
    kishore

  5. Ramesh Konagala said

    Excellent.

  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.

    regards
    Nav

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

  8. Pawan said

    Navdeep,
    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.

    Regards
    Pawan

  9. samuel said

    iyDGzr kdgHq9bt4FuaL6c1dp85Uf

  10. samuel said

    bnkMoI kdgHq9bt4FuaL6c1dp85Uf

  11. wajid said

    Navdeep,

    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.

    -Waks

  12. bastian said

    great job!

  13. Saravana said

    Good Job

  14. Nena said

    Also good website for oracle apps dba

    http://goo.gl/LvOvhf

  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.

    Thanks
    Pratheek

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

 
Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: