Is there any relationship between the cost factor in the explain plan and query execution time. I have come across situations in both ways, i.e. high cost and low execution time, low cost and high execution time. What parameters do we need to consider in tuning a query high cost or low cost?
The CBO builds a decision tree, estimating the costs of each possible execution plan. The costs are set by the CPU_cost or I/O_cost parameters. And the CBO estimates the costs, as best it can with the existing statistics. Yes, the costs are real, and a low cost should correlate to faster execution speed if you are using first rows optimizer mode. For all rows the costs optimizer server resources and the lowest cost should have the smallest resource usage.
The parameter OPTIMIZER_INDEX_COST_ADJ will manipulate the cost to favor the use of the index for a query. What is the advantage of this CBO, and when is it best to go for the index and when is it best to go for the FTS? Having this parameter to a non-default value will favor index paths, which might be a rejected plan by CBO under normal circumstances. Moreover, the use of index path via this parameter may show the cost in the explain plan as proportionate of the set value. In this case, under what circumstances is the parameter advisable, or whether or not this parameter can add any value in query tuning?
It's well understood that the default for OICA (100) is too high for many online databases. As a general rule, most OLTP database will use index access if OICA is set between 20 and 35. Remember, there is NOTHING WRONG with full-table scans unless they cause unnecessary I/O. For example, if an index access costs 6 I/Os, and the full-table scans uses 20 I/O, then the index access will return the rows faster (first_rows). On the other hand, the full-table scan might execute the query with less resource consumption (all_rows).
Dig Deeper on Oracle database design and architecture
Related Q&A from Don Burleson
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading
What is table partitioning and what are its advantages and disadvantages? Also, what is bulk collection? What are hints? Continue Reading
In Oracle 9i, is there a way to identify the specific row being locked in a particular table? I have seen much code that identifies the object being ... Continue Reading