Q

Questions about plan stability

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

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.
This was last published in December 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close