What is the COST?

If you use the Cost Based Optimizer (CBO) in your Oracle database, you may be surprised to know that decreasing the ‘COST’ of a query does not necessarily mean increased performance.

The following is a summary of a long thread on AskTom about this topic:

The ‘COST’ of a query is Oracle’s estimate of the completion time for a query. But the estimate for the completion time is based on Oracle’s statistical guesses about your data (which may be wrong), the model that Oracle uses for your hardware resource availability (which may be wrong) and the assumptions that Oracle makes about the actions of the run-time execution (which, surprisingly, may be wrong). Consequently, the predicted cost is not always a good estimate of the execution time of the query. Just to add a little confusion, the unit of measurement used is not seconds (or centi-seconds), but an abstract unit equivalent to the assumed time to perform a single block read on your platform.

So, The cost number can/will flucuate depending on parameters we can modify directly (e.g. optimizer_index_cost_adj) or the parameters we can modify indirectly (e.g. gather recent statistics). In some cases when the cost decreases the query will run faster (be more efficient), in other cases it may not. So just because I can decrease the cost, doesn’t mean I’m decreasing work (PIO). And just because one query may have a lower cost then another doesn’t mean it works less. But what was true yesterday isn’t true today, and what’s true today won’t be true tomorrow; with each new release the cost becomes a better indicator of work to be done, but today it isn’t perfect enough to rely on exclusively.