Oracle 11g R2 INDEX FAST FULL SCAN 成本計算

SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
-
 

create index i_test_owner on test(owner);

SQL> create index i_test_owner on test(owner);

Index created.

INDEX FAST FULL SCAN只需要掃描葉子塊，并且采用多塊讀，所以查詢LEAF_BLOCKS

SQL>  select leaf_blocks from user_indexes where index_name='I_TEST_OWNER';

LEAF_BLOCKS
-----------
         22

SQL> explain plan for select count(owner) from test;

Explained.

SQL> select cpu_cost from plan_table;

  CPU_COST
----------
   1356672
  
SQL>  select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             2500
CPUSPEEDNW                     2696.05568
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC                                   12
MREADTIM                               30
SLAVETHR
SREADTIM                                5

9 rows selected.

因為MBRC不為空，所以CBO會采用工作量模式計算Cost
  
INDEX FAST FULL SCAN 成本計算公式如下：

Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed /1000
       ) / sreadtime
      
#SRds - number of single block reads
#MRds - number of multi block reads
#CPUCyles - number of CPU cycles

sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

Cost = (
       #SRds * sreadtim +                            ---SRds=0
       #MRds * mreadtim +                          ---MRds=Leaf_Blocks/MBCR=22/12, mreadtim=30
       CPUCycles / cpuspeed / 1000         ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
       ) / sreadtime

所以人工計算的成本等于：
SQL> select ceil(22/12*30/5)+ceil(1356672/2500/5/1000)+1 from dual; ---+1是因為 _table_scan_cost_plus_one設置為true

CEIL(22/12*30/5)+CEIL(1356672/2500/5/1000)+1
--------------------------------------------
                                          13
                                                                                   
SQL> select count(owner) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1992658997

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     5 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_TEST_OWNER | 10000 | 50000 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

現(xiàn)在采用非工作量統(tǒng)計來計算Cost

SQL> begin
   dbms_stats.set_system_stats('CPUSPEED',0);
   dbms_stats.set_system_stats('SREADTIM',0);
   dbms_stats.set_system_stats('MREADTIM',0);
   dbms_stats.set_system_stats('MBRC',0);
end;
/
  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
       (select value
          from v$parameter
         where name = 'db_file_multiblock_read_count') *
  2    3    4    5         (select value from v$parameter where name = 'db_block_size') /
  6         (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
  7    from dual;

  mreadtim
----------
        42
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
       (select value from v$parameter where name = 'db_block_size') /
       (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
  from dual;  2    3    4

  sreadtim
----------
        12
       
SQL> select cpu_cost from plan_table;

  CPU_COST
----------
   1356672

根據(jù)成本計算公式

Cost = (
       #SRds * sreadtim +                            ---SRds=0
       #MRds * mreadtim +                          ---MRds=Leaf_Blocks/db_file_multiblock_read_count=22/16, mreadtim=42
       CPUCycles / cpuspeed / 1000         ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2696.05568
       ) / sreadtime
      
那么手工計算的Cost等于：

SQL> select ceil(22/16*42/12)+ceil(1356672/2696.05568/12/1000)+1 from dual;

CEIL(22/16*42/12)+CEIL(1356672/2696.05568/12/1000)+1
----------------------------------------------------
                                                   7
SQL> set autot trace
SQL> select count(owner) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1992658997

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     5 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_TEST_OWNER | 10000 | 50000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

從實驗中可以看到，INDEX FAST FULL SCAN 在11gR2中的成本算法依然和9i,10g一樣，沒有變化。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]