I have been thinking about it for a while. We have couple of tables of size each 10+ million records. Whenever we get bad query performance or too much cost I will try gathering the stats for all indexed columns. But none of the times it helped me. When I get frustated and delete the stats as gathering stats screwed my query performance further... Query comes back quick...
I don't know whats going onn really. May be you can help me understand whats exactly happening with optimizer in this specific scenario...
Probably your Query runs better in RULE-Based Mode;
So access the table with /*+RULE*/-Hint
In our production-Environment very often the hint increases performance due to the CBO likes to scan tables full in some situations for ecample with the OR or NOT -Operator or <>-Operator.
Nope. When I use hint RULE I don't get much performance for the same queries... Let me investigate further on Optimizer intelligence for diffrent scenarios...
Now this is an interesting thread, reading some of the other problems i am shocked that the companies had trusted these guys with the databases...
My two pence worth on the subject.
We have several tables with 20 Million + rows...the thing is that with stats the optimizer makes a calculated guess to determine the correct path for execution, do note that i said a calculated guess. Its not entirely correct. It takes into account several things like for eg:
What sort of values do u have for these? The third one is specially useful if the indexes are not used in your queries. Set it to a lower value to force index scan rather than full table scans.
Hope this helps and get back to me on your optimizer behaviour findings.
Click Here to Expand Forum to Full Width