B * Tree Index Range Scan of CBO, cborange
Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/44262353
**************************************** ********************
Ii. CBO's B * Tree Index Range Scan-IRS Algorithm
**************************************** *********************
1. Create an index in the gyj_t1 table
SQL> create index idx_gyj_t1_id on gyj_t1 (id );
Index created.
2. Collect statistics
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 ownname => 'gyj ',
4 tabname => 'gyj _ t1 ',
5 estimate_percent = & gt; 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. Execute SQL statements and generate execution plans. The index range scan cost is 5.
SQL> select name from gyj_t1 where id <500;
499 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3428071533
Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 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 |
Bytes ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("ID" <500)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
73 consistent gets
0 physical reads
0 redo size
17972 bytes sent via SQL * Net to client
886 bytes encoded ed via SQL * Net from client
35 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
499 rows processed
4. Formula
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 (index height)
SQL> select blevel from dba_indexes where index_name = 'idx _ GYJ_T1_ID ';
BLEVEL
----------
1
6. # LB (slice block)
SQL> select leaf_blocks, blevel, clustering_factor from dba_indexes where index_name = 'idx _ GYJ_T1_ID ';
LEAF_BLOCKS
-----------
222
7. ix_sel (index selection rate)
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 (clustering factor)
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 cost
I/O Cost = Index Access I/O Cost + Table Access I/O Cost = 3 + 2 = 5
11. CPU cost
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
Iotfrspeed4096
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 = 'ottfrspeed') "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
