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?

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

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