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 first published in December 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close