Question: I am tuning an
Oracle SQL query and I've noted that the costs don't match the execution speed
and SQL where the optimizer shows a lower cost actually take more time to
execute. I want to know why in second case the cost is less but time taken
to execute the query is more?
Answer:
It's important to note that the cost figures that are
displayed in an execution plan are not described in any Oracle
documentation, and it's been demonstrated that the plan with the lowest
?cost? number is not always the plan chosen by the optimizer.
Further, the ?cost? figures do not always indicate the ?best:?
execution plan for a query, given the divergent optimizer goals of
first_rows (optimizer for response time) and all_rows
(optimize for minimizing computing resources).
In general, the lower the CBO-calculated cost, the faster the
query will run, but we have to remember that the costs used by the CBO are only
estimates, and the numbers are based on metadata statistics collected by
dbms_stats. The cost based optimizer (CBO) creates these estimates based on
many variables:
- system statistics -
Workload statistics have an impact of the CBO cost estimates.
- costing -
You can have the
CBO costs based on estimated
I/O
costs or estimated CPU costs.
- object statistics - The
table and index analyze with dbms_stats.
- object histograms -
Histograms are used to guess the cardinality of result set sizes.
- CBO parms - settings for
important CBO parameters (hash_area_size, db_file_multiblock_read_count,
optimizer_index_caching, optimizer_index_cost_adj, &c)
Remember, if you have sub-optimal statistics, the CBO can misjudge the costs.
The most common causes for inaccurate CBO costing estimates may include:
- Bad table join order -
sub-optimal table join order
can be caused by missing histograms.
- Inaccurate I/O estimates
-
Oracle uses the estimate of single block read time which can be inaccurate
in system statistics (i.e. dbms_stats.gather_system_stats) are missing.
- Buffer caching estimates -
If you don't set optimizer_index_caching or if the contents of your
data buffers vary greatly, the optimizer can mis-estimate the I/O costs for
a query optimization.
You might also have an issue with a SQL optimizer bug, see how to
display optimizer features
and bug fixes by release.
References on CBO costing:
See my related notes on CBO
cost estimates:
and include the URL for the page.
