Reprint Please specify source: http://blog.csdn.net/guoyjoe/article/details/44262353
************************************************************
Second, the CBO b*tree Index Range scan-irs algorithm
*************************************************************
1, in the table GYJ_T1 index
Sql> CREATE index idx_gyj_t1_id on gyj_t1 (ID);
Index created.
2. Collect statistical information
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);
Ten END;
11/
PL/SQL procedure successfully completed.
3. Execute SQL, generate execution plan, index range scan cost is 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
Consistent gets
0 physical Reads
0 Redo Size
17972 Bytes sent via sql*net to client
886 bytes received via sql*net from client
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 (Block leaf 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
Ten WHERE C.owner = T.owner
One and c.table_name = T.table_name
and C.owner = ' GYJ '
C.table_name = ' Gyj_t1 '
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 (cluster 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, the cost of IO
I/o cost = Index access I/O cost + Table access I/O cost=3+2=5
11, the cost of the 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
CBO's b*tree Index Range scan-irs algorithm
