Oracle 全表扫描成本计算方法和公式

最新推荐文章于 2021-04-14 14:33:33 发布

SeanData

最新推荐文章于 2021-04-14 14:33:33 发布

阅读量1.6k

点赞数 3

分类专栏： Oracle 性能优化 Oracle 数据库管理文章标签： oracle 全表扫描 table scan 成本计算

本文链接：https://blog.csdn.net/Seandba/article/details/74552914

版权

Oracle 数据库管理同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

Oracle 性能优化

9 篇文章 0 订阅

订阅专栏

看这个博客之前，建议先看一下这篇文章，了解下系统统计信息以及工作量模式和非工作量模式。

http://blog.csdn.net/seandba/article/details/74441036

测试环境是：11.2.0.4.0，下面列出模型公式是一位朋友提供，我并没有都验证，下面例子也是基于我的测试环境所作。

有一点需要说明下mbrc（multi block read count，即1次多块读能读取的块数），在工作量模式下，系统会收集，但是在非工作量模式下，系统使用隐含参数_db_file_optimizer_read_count的值来代替，也就是 mbrc=_db_file_optimizer_read_count，网上很多说是 mbrc=db_file_multiblock_read_count，测试结果也正确，我猜测要么是他的环境这两个参数相等，要么是我们数据库版本的差别。

基于下面的公式我们可以看出，11g的table scan成本分为io和cpu两部分
io部分主要是看表有多少块，一次多块读能读多少块，需要读多少次
cpu部分主要是看整个扫描需要多少cpu周期数
11g中不管是工作量模式还是非工作量模式，全表扫描成本计算公式是一样的，区别在于某些参数工作量模式下是统计出来的，非工作量模式是基于其他参数计算出来的。

table scan成本运算：
9i：model = io
mbdivisor = 1.6765 * power(db_file_multiblock_read_count,0.6581) --db_size=8k
tsc cost = blocks / mbdivisor

10g以后：model=io
mbdivisor = 1.6765 * power(_db_file_optimizer_read_count,0.6581) --db_size=8k
tsc cost = blocks / mbdivisor

11g：model=io+cpu
工作模式：
    tsc cost = io cost + cpu cost
    io cost = 1 + ceil((blocks / mbrc) * (mreadtim / sreadtim))
    cpu cost = round(#cpucycles / cpuspeed / 1000 / sreadtim)
    Cost = (#SRds * sreadtim + #MRds * mreadtim + CPUCycles / cpuspeed ) / sreadtim

非工作模式：
    mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iotftspeed
    sreadtim = ioseektim + db_block_size / iotfrspeed
    mbrc = _db_file_optimizer_read_count --db_file_multiblock_read_count

数据库版本

SEAN@sean> select * from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

建表，不用关注这个表的创建语句

create table t_call_qd nologging as    
with t_nbr as  
(  
select cast('15305'||lpad(level,6,'0') as varchar2(11)) nbr from dual connect by level<=500000  
)  
select rownum id,  
       t1.nbr call_nbr,  
       cast('15305531836' as varchar2(11)) called_nbr,  
       t2.start_time,  
       t2.end_time,  
       2 duration  
 from   
(select sysdate + numtodsinterval(power(level, 2), 'second') start_time,sysdate + numtodsinterval(power(level, 2) + 2, 'second') end_time  
  from dual  
connect by level <= 10) t2,  
t_nbr t1  
;

收集表T_CALL_QD信息

SEAN@sean> exec dbms_stats.gather_table_stats(user,'T_CALL_QD',estimate_percent=>100);

PL/SQL procedure successfully completed.

表T_CALL_QD相关信息，一共有37454个块

SEAN@sean> select table_name,num_rows,blocks,empty_blocks from dba_tables where table_name='T_CALL_QD';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T_CALL_QD                         5000000      37454            0

可见数据库运行在非工作量模式下,因为(SREADTIM,MREADTIM,mbrc)等都为空，说明没有收集过工作量信息

SYS@sean> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     902.641119
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
mbrc
MAXTHR
SLAVETHR

9 rows selected.

在开始之前我们要先看下一个参数db_file_multiblock_count，控制一次多块读时读取的块数，但是11.2.0.4版本的优化器在评估cost时候，使用的是一个隐含参数
_db_file_optimizer_read_count，获取方式为：

[oracle@sean ~]$ sqlplus / as sysdba

SYS@sean> select substr(x.ksppinm, 1, 40) name,
  2         substr(y.ksppstvl, 1, 20) value,
  3         substr(x.ksppdesc, 1, 64) description,
  4         substr(y.ksppstdf, 1, 5) "DEFAULT",
  5         substr(decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'), 1, 5) ses_mod,
  6         substr(decode(bitand(ksppiflg / 65536, 3),
  7                       1,
  8                       'IMMEDIATE',
  9                       2,
 10                       'DEFERRED',
 11                       3,
 12                       'IMMEDIATE',
 13                       'FALSE'),
 14                1,
 15                10) sys_mod,
 16         substr(decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE'), 1, 5) is_adjusted
 17    from sys.x$ksppi x, sys.x$ksppcv y
 18   where x.indx = y.indx
 19     and lower(x.ksppinm) like
 20         lower('%' || nvl('_db_file_optimizer_read_count', 'whoops') || '%')
 21   order by translate(x.ksppinm, ' _', ' ')
 22  /

NAME                           VALUE DESCRIPTION                               DEFAULT  SES_MOD    SYS_MOD      IS_ADJUSTE
------------------------------ ----- ----------------------------------------- -------- ---------- ------------ ----------
_db_file_optimizer_read_count  8     multiblock read count for regular clients TRUE     TRUE       IMMEDIATE    FALSE

下面正式开始计算：

--全表扫描成本计算公式
       (#SRds * sreadtim + #MRds * mreadtim + CPUCycles / cpuspeed / 1000)
Cost = -------------------------------------------------------------------       
                                   sreadtim

       (单块读次数 * 单块读时间 + 多块读次数 * 多块读时间 + cpu周期数 / cpu速度 / 1000)
成本 = ------------------------------------------------------------------------------    
                                 单块读时间
                                       
--对于非工作量模式，没有sreadtim，mreadtim的值，需要先计算出，计算公式如下

                        (_db_file_optimizer_read_count * db_block_size)
mreadtim = ioseektim + ------------------------------------------------- 
                                         iotftspeed

SYS@sean> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
            (select 8 --value
               from v$parameter
              where name = 'db_file_multiblock_read_count') *
            (select value from v$parameter where name = 'db_block_size') /
            (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') mreadtim
       from dual;

  MREADTIM
----------
        26
       

                            (ioseektim + db_block_size)
mreadtim = ------------------------------------------------------------------       
                                   iotfrspeed

SYS@sean> 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;

  sreadtim
----------
        12

CPUCycles 等于 PLAN_TABLE里面的CPU_COST

SEAN@sean> explain plan for select count(*) from T_CALL_QD;

Explained.

SEAN@sean> select cpu_cost from plan_table;

  CPU_COST
----------
1016726414

cpuspeed 等于 CPUSPEEDNW= 902.641119

最后计算结果

       (#SRds * sreadtim + #MRds * mreadtim + CPUCycles / cpuspeed / 1000)
Cost = -------------------------------------------------------------------       
                              sreadtime

       (0 * 12 + (37454/8) * 26 + 1016726414 / 902.641119 / 1000)
Cost = -----------------------------------------------------------      
                                   12
                                                                      
SEAN@sean> select ceil((0 * 12 + (37454/8) * 26 + 1016726414 / 902.641119 / 1000)/12) cost from dual;

      COST
----------
     10238

验证

SEAN@sean> set autotrace traceonly
SEAN@sean> select count(*) from t_call_qd;


Execution Plan
----------------------------------------------------------
Plan hash value: 712447240

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 | 10240   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_CALL_QD |  5000K| 10240   (1)| 00:02:03 |
------------------------------------------------------------------------

执行计划显示全表扫描成本是10240，和10238差2个单位。_table_scan_cost_plus_one参数控制了全表扫描和索引快速全扫描时候成本是否加1，如下可见加1之后总成本就是10239，还是差1个单位。差1的原因目前我还不知道，可能是什么地方ceil、floor、round之类的影响吧

SYS@sean> select substr(x.ksppinm, 1, 40) name,
  2         substr(y.ksppstvl, 1, 20) value,
  3         substr(x.ksppdesc, 1, 64) description,
  4         substr(y.ksppstdf, 1, 5) "DEFAULT",
  5         substr(decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'), 1, 5) ses_mod,
  6         substr(decode(bitand(ksppiflg / 65536, 3),
  7                       1,
  8                       'IMMEDIATE',
  9                       2,
 10                       'DEFERRED',
 11                       3,
 12                       'IMMEDIATE',
 13                       'FALSE'),
 14                1,
 15                10) sys_mod,
 16         substr(decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE'), 1, 5) is_adjusted
 17    from sys.x$ksppi x, sys.x$ksppcv y
 18   where x.indx = y.indx
 19     and lower(x.ksppinm) like
 20         lower('%' || nvl('_table_scan_cost_plus_one', 'whoops') || '%')
 21   order by translate(x.ksppinm, ' _', ' ');

NAME                           VALUE DESCRIPTION                                              DEFAULT  SES_MOD    SYS_MOD      IS_ADJUSTE
------------------------------ ----- -------------------------------------------------------- -------- ---------- ------------ ----------
_table_scan_cost_plus_one      TRUE  bump estimated full table scan and index ffs cost by one TRUE     TRUE       IMMEDIATE    FALSE