Modifying Stored Outlines
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Modifying Stored Outlines

  1. #1
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100

    Modifying Stored Outlines

    I use stored outlines a lot on 8i and 9i, to coax a "wonderful" 3rd party application into using better explain plans on specifc sqls.

    On 8i at least, I have to manually update the ol$hints table, swapping the plans for the bad application sql with the better hint sql.

    I'm now running into queries that I can't directly affect with hints, unless I reorder the tables in the from clause. What I'm wanting to try is:
    select .... from detailtable a, headertable b
    where a.lesserkey=:1
    and a.realkey = b.realkey
    and b.realkey = :2;
    To be swapped with the outline for:
    select /*+ use_nl(headertable) ordered index(headertable,realkeyidx) */...
    from headertable b, detailtable a
    ....
    so that the headertable drives the loop. I haven't tried this yet, swapping plans for two slightly different sqls. I'm going to test this out, but does anyone have any thoughts or experience with doing this?
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  2. #2
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    Not exactly, but...

    Don't know if this will help or not, but I've used the OEM gui to create stored outlines. Unfortunately I have a vendor app that has started having performance problems and I've created 3 or 4 outlines in the past few weeks.

    If you can use the gui, you are able to select the join order of the tables involved, then you can choose the join type (nested loops, hash, etc) and also the access method. It has been a little buggy with me (version 9.2) at times though. Sometimes it doesn't use the index I enter unless I change the join order first, then select the index I want, and then change the join order back. Strange, but other than that it has been working and has helped me solve my performance issues.

    Just remembered this is part of the Tuning Pack in OEM so I realize many people don't want to pay for this. Never had to modify the tables directly, sorry...

  3. #3
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    Thanks, have used the gui before, and when it works it's great. Does allow the nested loops order to be swapped around under 9i, though the one sql I'm working with is too complicated; outline mgr reverts back to the original plan.

    On 8i the gui doesn't allow any editing, so I still have to manually do DML to change the outlines.

    Still haven't tried swapping plans yet, been too busy...
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width