Why does Oracle prefer CPU cost to less I/O while generating execution plans?
I have been much into query tuning for last 1 month. Couple of times, I have seen that though CPU cost of a plan is low, the query essentially does not consume least time using this plan. By specifying an index hint, I was able to find a plan that caused much less I/O and hence the query took much less time. A day later when Oracle seemed to have collected new statistics on underlined tables and indexes, I found that query started using new indexes which again caused query to take more time to execute.
My question are:
1. How can we explicitly ask oracle to use an execution plan based on least I/O?
2. Even in oracle 11g, does oracle not automatically pick an execution plan that will cause least I/O?