Originally posted by mike2000
thanks for your advises.

so you are saying that the db_file_multiblock_read_count is too high so that Oracle think FTS is cheaper so that it do full table scan instead of using index. By changing the db_file_multiblock_read_count =16, do you think Oracle will try to use indexes somehow. The thing is that before the changes I made today, sometime Oracle use index and sometime it do FTS and it's very inconsistent.

Nope by db_file_multiblock_read_count would cause the optimizer to think that the cost of a full table scan is cheaper thats it..if you lower it from 32 to 16 then the cost based optimizer would now start thinking that the full table scan are costlier then before..your indexes would be used or not is another question all together..you will have to alter the paramter and take another statspack report and see..and post it here..

regards
Hrishy