Plan stability is a major concern for me right now. Frequently when I update stats, a plan(s) changes and I get into hot water. I'm investigating stored outlines, but I have two concerns and they are: (1) I have a lot of current plans that are terrible and I don't want to lock them in with the others. (2) It's my understanding that for every cursor parsed, using stored outlines, a table must be hit to determine if an outline exists for it. I'm worried about the overhead of this process.
I don't have much experience with stored outlines, but with respect to question 1, you can store outlines just for selected SQL via:
CREATE OUTLINE outline [FOR CATEGORY category] ON statement
To gauge the overhead of using stored outlines, set up a trace of SQL that uses them, and compare it to a trace of the same SQL without using stored outlines. Remember that the lookup of a stored outline may be less expensive than constructing a new optimizer plan.
Dig deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.