oracle实验记录 (oracle b*tree index访问Cost计算(1))

最新推荐文章于 2021-04-10 22:19:04 发布

cuiye1492

最新推荐文章于 2021-04-10 22:19:04 发布

阅读量183

点赞数

文章标签：数据库

计算所用公式
INDEX
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

SQL> create table t1(a int,b int);

表已创建。

SQL> ed
已写入 file afiedt.buf

1 declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7* end;
8 /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL 过程已成功完成。

SQL> create index t1_ind on t1(a);

索引已创建。

SQL> select num_rows from user_tables where table_name='T1';

NUM_ROWS
----------
10000

SQL> select column_name,NUM_NULLS,NUM_DISTINCT,density from user_tab_col_statist
ics where table_name='T1';

COLUMN_NAME                     NUM_NULLS NUM_DISTINCT    DENSITY
------------------------------ ---------- ------------ ----------
A                                       0        10000      .0001
B                                       0        10000      .0001

SQL> select LEAF_BLOCKS,BLEVEL ,clustering_factor from user_indexes where index_
name='T1_IND';

LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
21 1 18

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select a,b from t1 where a>9000 and b>2;

执行计划
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------

------

--------------------------------------------------------------------------------

------

| 0 | SELECT STATEMENT | | 1000 | 7000 | 6 (0)| 00:0

0:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1000 | 7000 | 6 (0)| 00:0

0:01 |

|* 2 | INDEX RANGE SCAN | T1_IND | 1000 | | 4 (0)| 00:0

0:01 |

--------------------------------------------------------------------------------

------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B">2)
2 - access("A">9000)

cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

effective index selectivity=a>9000=(high_value – limit) / (high_value – low_value)=.100010001
有效index选择率就是只算谓词上使用index的选择率，不算谓词上没有INDEX 的这个谓词的选择率

SQL> select (10000-9000)/(10000-1) from dual;

(10000-9000)/(10000-1)
----------------------
.100010001

effective table selectivity=a>9000=.100010001
从10053trace中看ix_sel_with_filters就是有效表选择率

有效表选择率按cost-based oracle中描述
When working out the cost of using an index, the effective table selectivity should be based
only on those predicates that can be evaluated in the index, before you reach the table.
基于那些在接触到表之前就能够在INDEX中行评价的谓词
对于上面这段话简单看执行计划"B">2 是在INDEX接触完表后过滤的谓词所以计算有效表选择率时不应该算上它

1 - filter("B">2)
2 - access("A">9000)~~~~简单说有效表选择率就为PLAN中ACCESS包含的谓词

总cost=1+3+2 分析可以看出index cost=4(扫描分支COST 1，扫描LEAF_BLOCK 3)其它的2是通过rowid从table中读取块成本
SQL> select 1+ceil(21*.100010001)+ceil(18*.100010001) from dual;

1+CEIL(21*.100010001)+CEIL(18*.100010001)
-----------------------------------------
6

看下trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Column (#2): B(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 2 Max: 10001
Table: T1 Alias: T1
    Card: Original: 10000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
    Cost: 6.16 Resp: 6.16 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2392434
      Resp_io: 6.00 Resp_cpu: 2392434
Access Path: index (RangeScan)
    Index: T1_IND
    resc_io: 6.00 resc_cpu: 463104
    ix_sel: 0.10001 ix_sel_with_filters: 0.10001~~~~~~~~~~可以看到有效INDEX 选择率=.10001 有效率表选择率也为.10001
    Cost: 6.03 Resp: 6.03 Degree: 1
Best:: AccessPath: IndexRange Index: T1_IND
         Cost: 6.03 Degree: 1 Resp: 6.03 Card: 1000.00 Bytes: 0

从上面可以看到oracle 算了FTS的COST 为6.16而INDEX COST 为6.03所以最后使用INDEXRANGE

FTS COST
SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     484.974958 speed in millions of operations per second
IOSEEKTIM                              10 disk seek time in milliseconds
IOTFRSPEED                           4096   disk transfer time in bytes per millisecond
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

已选择9行。以上没有信息收集 ORACLE 会用DEFAULT 计算************

计算*使用默认信息**************************
MBRC=db_file_multiblock_read_count=16
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED=42

MDRS=BLOCKS/MBRC
#SRds =0(single-block reads),因为是 FTS 都是多块读取
最早的公式变换一下(除开)~~~~计算FTS的公式
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost
SQL> select blocks from user_tables where table_name='T1';

BLOCKS
----------
20
SQL> show parameter db_file_m

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select 20/16*42/12 from dual;~~~~~~~~~~~~~~~~~~~~COST

20/16*42/12
-----------
      4.375
_optimizer_ceil_cost           TRUE COST 取整=5
由于_table_scan_cost_plus_one      TRUE COST加1 `~~~~~~~~~~~~所以总COST=6（IO部分）

所以计算出FTS的IO部分为 6 CPU部分为0.16 ，INDEX RANG SACN的话IO 部分为6 CPU部分为0.3 所以optimizer经过比较(COST 大小)选择了INDEX RANGE SCAN

简单看一下clustering_factor
简单的说CLUSTERING_FACTOR 用于INDEX 的有序度和表的混乱度之间比较
b*tree index是经过排序的
例如 INDEX中记录的第一个rowid指向表所在DATAFILE 中 BLOCK#1 第1行计数器记为1，第2个rowid 指向 BLOCK#2 由于改变了块所以计数器加1 ，INDEX 第3个rowid
指向BLOCK#2 块没变所以计数器还为2，接着沿INDEX执行第4个rowid 指向BLOCK#1 块又变了计数器加1
计数器对应着CLUSTERING_FACTOR 计数器每次从一个块到另一个新块时候加1 这样CLUSTERING_FACTOR也加一
所以clustering_factor可以描述数据在表中的散布方式
如果clustering_factor接近表中的行数，大多行都不在同一个块中,分布太散
当clustering_factor接近表中的块数，说明数据集中有序

当用INDEX 获取一行以上数据时（INDEX RANGE SCAN），需要遍历INDEX的一部分叫INDEX的 X%，扫描INDEX 时必须逐行的读取表,那么当遍历INDEX 的 X%时，转换表块的次数就

等于clustering_factor 的 X%

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-614733/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-614733/