Method R Discussion GroupMethod R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle. |
||
Discuss Method R products, techniques, and events. Ask new questions or post your own responses. |
What if the row source operation consuming the most time is not tied to a predicate?
If that's the case, then you need to determine if
1) it is consuming the most time because it *should* have a predicate applied to it (for example, sometimes the optimizer applies predicates in the "wrong" place or sometimes we don't code the query to be able to correctly apply a predicate) 2) it is doing an operation that is time consuming by nature (for example, a large sort may be the largest consumer of time because it ends up being a disk sort) 3) if the operation that's taking so long could be less time-consuming if the optimizer chose a different operation (for example, the optimizer chose a HASH JOIN when a NESTED LOOPS join method would be better) 4) your query is experiencing the effects of a bug for that particular operation (this is rare, but possible). Once you know where the time is being spent, then it's going to take a bit of comparison testing to determine if there is a better alternative. You can review optimizer trace data (event 10053) to help you see which plan choices the optimizer considered and how it costed them and perhaps be able to track down a problem with statistics or some parameter setting that caused the optimizer to improperly cost the plan operation that is accounting for the excessive time. The key is that you were able to locate the problem specifically to a particular row source operation and don't have to waste time and effort working on "the wrong thing". |
Powered by FogBugz