Mark it!!!
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
1. The first line of the formula represents the number of block visits needed to descend through the index (excluding the cost of actually hitting the first leaf block you want).
2. The second line of the formula represents the number of leaf blocks that you will have to walk along to acquire all the rowids matching a given set of input values. The effective index selectivity corresponds to the entry labelled ix_sel in the 10053 trace file.
3. The third line represents the number of visits to table blocks that you will have to make to pick up the rows by way of the selected index. The effective table selectivity corresponds to the thing that used to be labelled tb_sel in the 10053 trace file, but ends up being labelled (more accurately) as ix_sel_with_filters in the 10g trace file. This line often generates the biggest component of the cost, and introduces the biggest error in the calculation of the cost of using a B-tree index.
-----------------example-----------
from:Cost Based Oracle Fundamentals page:88
[cc lang='sql']CREATE INDEX t1_i1 ON t1(date_ord, seq_ord);
BEGIN
dbms_stats.gather_table_stats(
USER,
't1',
CASCADE => TRUE,
estimate_percent => NULL,
method_opt => 'for all columns size 1'
);
END;
/
SELECT
blocks,
num_rows
FROM
user_tables
WHERE
table_name = 'T1';
BLOCKS NUM_ROWS
---------- ----------
749 26000
SELECT
index_name,
blevel,
leaf_blocks,
clustering_factor
FROM
user_indexes
WHERE
table_name = 'T1'
;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
T1_I1 1 86 1008
SELECT COUNT(small_vc)
FROM t1
WHERE date_ord = TRUNC(SYSDATE) + 7
;
IN this CASE, we are after 1 DAY OUT OF 26—a selectivity OF 3.846% OR 0.03846
COST = 1 +
CEIL(0.03846 * 86) +
CEIL(0.03846 * 1,008)
= 1 + 4 + 39 = 44[/cc]
VS
[cc lang='sql']SET autotrace traceonly EXPLAIN
SELECT COUNT(small_vc)
FROM t1
WHERE date_ord = TRUNC(SYSDATE) + 7
;
SET autotrace off
Execution PLAN (9.2.0.6 autotrace)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (COST=44 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (COST=44 Card=1000 Bytes=13000)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (COST=5 Card=1000)[/cc]
--------
The important reference for adjust the table(index)'s statistics:
http://psoug.org/reference/dbms_stats.html
This work is licensed under a CC A-S 4.0 International License.