CBO's b*tree Index Range scan-irs algorithm

Source: Internet
Author: User
Tags create index sorts

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.