Oracle Performance: Use Stored Outline to Change Execution Plan
Stored Outline: A Tested Method
It's very common to need to change an execution plan. Oftentimes, we insert a sql hint, which is s agreat way. But what if you can't change the code?
One easy method is to create a stored outline--but in a tricky way. We use an outline not to stabilize a plan, but to change it.
Here's the idea: We create 2 stored outlines; one is the exact sql we need to change; the other outline will be for sql having the sql hints to get the correct execution plan. After we have created the 2 outlines, we switch the plans interally, so that the sql without the hints actually gets the exection plan of the sql with the hints.
That is, when the optimizer sees the sql in question, it looks for an outline, and applies the hints that we have provided.
Tricky, huh?
Follow my Instructions!
Use This Template
Here's the template I always use. I have used this nearly a hundred times to fix production problems. In the following scripts, I have used "TBD" as a placeholder for the sql_id. My own habit is to use the last 3 characters of the sql_id.
(1) Create Outline 1
Alter Session Set Current_Schema = [Schema Of Interest];
CREATE OUTLINE SQLTBD FOR CATEGORY XYZ ON {SQL WITH HINT}
(2) Create Outline 2
CREATE OUTLINE SQLTBD_FIX FOR CATEGORY XYZ ON {REGULAR SQL}
(3) Switch the Hints
update outln.ol$hints set ol_name =
decode(ol_name, ‘SQLTBD_FIX’, ‘SQLTBD’, ‘SQLTBD’, ‘SQLTBD_FIX’)
where ol_name in (‘SQLTBD’, ‘SQLTBD_FIX’);
(4) Get Rid of the Unneeded Outline
When we are all done with the "switch," we don't need the extra outline any more. So we just drop it.
drop outline SQLTBD;
(5) Confirm the New Execution Plan is Correct
Naturally, you will want to check to make sure the outline is being executed properly.
I hope you find this method as easy as I have. I literally have used this hundreds of times on production systems.
How did I do?
