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

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

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   



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值