ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (oracle b*tree index访问Cost计算(1))

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

原创 Linux操作系统 作者:fufuh2o 时间:2009-09-15 22:37:25 0 删除 编辑

 


计算所用公式
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

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

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

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

------

|   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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427840