CBO之B*Tree Index Range Scan，cborange
轉載請註明出處：http://blog.csdn.net/guoyjoe/article/details/44262353
************************************************************
二、CBO之B*Tree Index Range Scan - IRS演算法
*************************************************************
1、在表gyj_t1建索引
SQL> create index idx_gyj_t1_id on gyj_t1(id);
Index created.
2、收集統計資訊
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => 'GYJ',
4 tabname => 'GYJ_T1',
5 estimate_percent => 100,
6 method_opt => 'for all columns size 1',
7 degree => DBMS_STATS.AUTO_DEGREE,
8 cascade=>TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
3、執行SQL，產生執行計畫，索引範圍掃描成本為5
SQL> select name from gyj_t1 where id<500;
499 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3428071533
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 499 | 10479 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| GYJ_T1 | 499 | 10479 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_GYJ_T1_ID | 499 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<500)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
17972 bytes sent via SQL*Net to client
886 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
499 rows processed
4、公式
IRS Cost = I/O Cost + CPU Cost
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
5、LVLS （索引高度）
SQL> select blevel from dba_indexes where index_name='IDX_GYJ_T1_ID';
BLEVEL
----------
1
6、#LB （索塊葉塊）
SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_GYJ_T1_ID';
LEAF_BLOCKS
-----------
222
7、ix_sel （索引的選擇率）
1.g) Unbounded open (“col < val”)
Sel = ((val - low_value) / (high_value - low_value)) * A4Nulls
SQL> SET LINESIZE 1000
SQL> select
2 t.num_rows,
3 c.num_distinct,
4 c.num_nulls,
5 utl_raw.cast_to_number(high_value) high_value,
6 utl_raw.cast_to_number(low_value) low_value,
7 (t.num_rows-c.num_nulls) "NUM_ROWS-NUM_NULLS",
8 utl_raw.cast_to_number(high_value)- utl_raw.cast_to_number(low_value)"HIGH_VALUE-LOW_VALUE"
9 from dba_tab_col_statistics c, dba_tables t
10 where c.owner = t.owner
11 and c.table_name = t.table_name
12 and c.owner ='GYJ'
13 and c.table_name = 'GYJ_T1'
14 and c.column_name='ID';
NUM_ROWS NUM_DISTINCT NUM_NULLS HIGH_VALUE LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE
---------- ------------ ---------- ---------- ---------- ------------------ --------------------
100000 100000 0 100000 1 100000 99999
SQL> select((500 - 1) / (100000 - 1)) * ((100000 - 0) / 100000) from dual;
SQL> SELECT ((500 - 1) / (100000 - 1)) * ((100000 - 0) / 100000) FROM DUAL;
((500-1)/(100000-1))*((100000-0)/100000)
----------------------------------------
.00499005
Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
SQL> select 1+CEIL(222*0.00499005) from dual;
1+CEIL(222*0.00499005)
----------------------
3
9、CLUF（聚簇因子）
SQL> select clustering_factor from dba_indexes where index_name='IDX_GYJ_T1_ID';
CLUSTERING_FACTOR
-----------------
356
Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)
SQL> select CEIL(356 * 0.00499005) from dual;
CEIL(356*0.00499005)
--------------------
2
10、IO的成本
I/O Cost = Index Access I/O Cost + Table Access I/O Cost=3+2=5
11、CPU的成本
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
（1）#CPUCycles
SQL> select cpu_cost,OPERATION from plan_table;
CPU_COST OPERATION
---------- ------------------------------
220607 SELECT STATEMENT
220607 TABLE ACCESS
121364 INDEX
（2） cpuspeed
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 2894.14695
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
（3）sreadtim
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value from v$parameter where name = 'db_block_size') /
3 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
4 from dual;
sreadtim
----------
12
（4）CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
select ROUND(121364 / 2894.14695 / 1000 / 12) from dual;
SQL> select ROUND(121364 / 2894.14695 / 1000 / 12) from dual;
ROUND(121364/2894.14695/1000/12)
--------------------------------
0
