oracle實驗記錄 (oracle b*tree index訪問Cost計算(1))

 

計算所用公式
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/，如需轉載，請註明出處，否則將追究法律責任。