Why does Oracle prefer CPU cost to less I/O while generating execution plans?

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

1-- An execution plan profile can be assigned to a particular query - the one you like.
2-- Oracle chooses a "reasonable" execution plan depending on performance statistics, there are no warranties.

Thanks PAVB. By assigning execution profile to a query, do you mean using INDEX/NO_INDEX hints? I have tried searching assigning execution profiles to a query but the searches returned results relevant to user profiles only. Is assigning execution profile to queries a new feature that I may not be aware of?

Thanks PAVB. By assigning execution profile to a query, do you mean using INDEX/NO_INDEX hints? I have tried searching assigning execution profiles to a query but the searches returned results relevant to user profiles only. Is assigning execution profile to queries a new feature that I may not be aware of?

PAVB, I was able to bring down the execution time of the query that was taking long to execute, that had low CPU cost but that had high I/O. Using Oracle generated plan, its consistent gets were around 4,00,000. When I hinted query to use a specific bitmap index, the consistent gets came down to around 1,75,000. At this moment, the query started using one more index that the query was not using earlier in its execution plan. When I hinted the query not to use that extra index, the consistent gets came down dramatically to 3000. Now, this serves my purpose well and the query executes in almost 1 second - down from 20 seconds!!
So, the wider questions remains how in this world without using hit and trial, I can figure out a plan that has least IO!!

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.