|
|
|
|
| Re: sql hint [message #466977 is a reply to message #466890] |
Wed, 21 July 2010 19:17   |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Not sure I completely agree with you Michel. Can you provide some examples of hints where functionality has changed between releases/patches? I would have said they were at least as stable as regular syntax. The only ones I can think of are RULE and FIRST_ROWS, but they haven't changed functionality, they've just been deprecated.
The main disadvantage of hints is that you eliminate possible access paths from the Cost Based Optimizer. A plan that is bad today may be good in the future because your data changes. Or perhaps in a later release/patch, an even better execution plan will become available, but cannot be used because of your hint.
The approach I take when I get a bad execution plan is to try to work out WHY Oracle chose it. Assuming statistics are thorough and up to date, it is usually because it estimated some non-selective predicate as selective (and used an index on it) or because it estimated some selective predicate as non-selective, and chose NOT to use an index on it.
You can use "gentle" hints, such as NO_INDEX(tab), to discourage Oracle from using bad indexes. Or even better CARDINALITY(tab nnn) to tell Oracle how many rows you think a table will contribute to the result set. These hints don't exclude as many potential access paths, so they still give Oracle an opportunity to choose more optimal plans later on.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: sql hint [message #467024 is a reply to message #467015] |
Thu, 22 July 2010 01:11 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
But wouldn't it be fair to say that SQL execution plans are just as unstable with or without hints? An optimal plan may also change when a new release/patch provides a rewrite/optimisation that the CBO prefers. I don't see how this behaviour is any more prevalent in the presence of hints. As an example, Cost Based Transformations in 10g caused a lot of previously stable queries - both hinted and unhinted - to go awry.
If I was upgrading the server to a release with a new optimization feature, it wouldn't just be the hinted queries I would be concerned about.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: sql hint [message #467242 is a reply to message #467037] |
Fri, 23 July 2010 00:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Fairly specific case, and not really a complaint that can be directed at hints in general.
>> Not stable (but for a very few of them)
In fact, if you think about the general classes of hints: optimization goals, access path, join operation, parallel, join order; Join Order is the only type of hint I can conceive that might exhibit this problem - with all of the others the original plan should always still be one of the alternative plans the optimizer can choose. Even though the optimizer may find a better plan, it should never do worse than the original plan with all other hint classes.
In fact, of the Join Order hints, this wouldn't happen to LEADING, so it's really only a very specific problem with the ORDERED hint and Query Transformation.
I'm still not sure I'd be prepared to level the accusation of "unstable" against hints in general.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
