-
Jurij,
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...
-
Hi;
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.
Orca
-
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:
sort_area_size
db_file_multiblock_read_count
optimizer_index_cost_adj
optimizer_max_permutations
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.
Cheers,
Ali
