CBO之B*Tree Index Range Scan - IRS算法

转载请注明出处：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