Jurij, Deleting stats help instead gathering stats

[sreddy]

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...

[Orca777]

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

[sreddy]

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...

[Umusic]

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