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 - Productioncreate index i_test_owner on test（owner）;SQL> create index i_test_owner on test（owner）;Index created.INDEX FAST FULL SCAN只需要扫描叶子块，并且采用多块读，所以查询LEAF_BLOCKSSQL> select leaf_blocks from user_indexes where index_name="I_TEST_OWNER";LEAF_BLOCKS
-----------
         22SQL> 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                                59 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 cyclessreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per secondCost = （
       #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设置为trueCEIL（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 |
--------------------------------------------------------------------------------------现在采用非工作量统计来计算CostSQL> 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    7PL/SQL procedure successfully completed.SQL> show parameter db_file_multiblock_read_countNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16SQL> 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根据成本计算公式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一样，没有变化。Oracle grant用户授权通过设置SQLPLUS ARRAYSIZE（行预取）加快SQL返回速度相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）