Re: Stats and behavior weirdness

Kevin
The idea is you can set those parameter or gather systems stats, in theory are the same, this means
you set the parameters OR gather system stats. Tom Kyte gives an examples in his book Effective Oracle by Design, if I'm not wrong.

I never had time to dig further, but the problems seems to be the size of a table, that cause CBO to choose an incorrect execution path, once that table grows in size, you don't need to set this parameter, I think there shouldbe a relation with the DB_FILE_MULTIBLOCK_READ_COUNT value, because this values *block sizes causes oracle to think is better to do a full scan, but usually even when the table is too small is better to use an index..

Now remember this happens in the reverse way too, in some cases setting this values to 90 and 10 causes good execution plans goes to trash. This is rare but from time to time you see something like this. that's the reason too because I think is better to gather system statistics, but I'm not near to the production database, so I don't dare to do experiments with others production database, but I would suggest you to try to gather system statistics and see if this is better for you.

--
http://www.freelists.org/webpage/oracle-l