2011/2/4 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
> Given that there are no hints, what do I do to solve the problem of a slow
> query suddenly popping up in production? If and when this situation occurs,
> see how quickly the community steps in to help you solve it (and it'd bet it
> will solved be very quickly indeed).
That is EXACTLY what happened to me. I had a query killing my
production box because it was running VERY long by picking the wrong
plan. Turned out it was ignoring the number of NULLs and this led to
it thinking one access method that was wrong was the right one. I had
a patch within 24 hours of identifying the problem, and it took me < 1
hour to have it applied and running in production.
If Oracle can patch their query planner for you in 24 hours, and you
can apply patch with confidence against your test then production
servers in an hour or so, great. Til then I'll stick to a database
that has the absolutely, without a doubt, best coder support of any
project I've ever used.
My point in the other thread is that if you can identify a point where
a hint would help, like my situation above, you're often better off
presenting a test case here and getting a patch to make it smarter.
However, there are places where the planner just kind of guesses. And
those are the places to attack when you find a pathological behaviour.
Or to rewrite your query or use a functional index.