執行計畫中cost計算方法

標籤：執行計畫   oracle   cost   最佳化   

概念：

blevel：二元高度=索引高度-1

clustering_factor：叢集因子，掃描index scan得出的要掃描的表中block數，clustering_factor<=table blocks

索引掃描的計算公式：

cost =

blevel +

ceil(leaf_blocks *effective index selectivity) +

ceil(clustering_factor * effective table selectivity)

SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T';LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR----------- ---------- -----------------        112          1               776

SELECT b.num_rows, a.num_distinct, a.num_nulls, utl_raw.cast_to_number(high_value) AS high_value, utl_raw.cast_to_number(low_value) AS low_value
, b.num_rows - a.num_nulls AS "NUM_ROWS-NUM_NULLS", utl_raw.cast_to_number(high_value) - utl_raw.cast_to_number(low_value) AS "HIGH_VALUE-LOW_VALUE"
FROM dba_tab_col_statistics a, dba_tables b
WHERE a.owner = b.owner
AND a.table_name = b.table_name
AND a.owner = ‘SCOTT‘
AND a.table_name = upper(‘TEST‘)
AND a.column_name = ‘OBJECT_ID‘

NUM_ROWS   NUM_DISTINCT NUM_NULLSHIGH_VALUELOW_VALUENUM_ROWS-NUM_NULLSHIGH_VALUE-LOW_VALUE

50736 507351538202 5073553818

effective index selectivity=(limit-low_value)/(high_value-low_value)

SQL> select (1000-2)/(53820-2) selectivity from dual;SELECTIVITY-----------0.018543982

SQL> SELECT OWNER FROM TEST WHERE OBJECT_ID<1000;已選擇953行。執行計畫----------------------------------------------------------Plan hash value: 1810195980---------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |---------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |   941 | 10351 |    19 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |   941 | 10351 |    19 ||*  2 |   INDEX RANGE SCAN          | IDX_T |   941 |       |     4 |---------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"<1000)

1.回表io=ceil(clustering_factor * effective table selectivity)=19-4=15

2.blevel +ceil(leaf_blocks *effective index selectivity)