oracle执行计划(4)--COST成本全表扫描成本
转自 http://blog.csdn.net/zengmuansha/article/details/7489822
公式:
According to the CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +~~~~~~~~~~~~~~~~~~~~~~~~~~~~来自于cost-based oracle书中
#CPUCycles / cpuspeed
) / sreadtim
where
#SRDs - number of single block reads -–单块读取次数
#MRDs - number of multi block reads –-多块读取次数
#CPUCycles - number of CPU Cycles --目前执行计划中的CPU_COST
sreadtim - single block read time --单块读取时间
mreadtim - multi block read time --多块读取时间
cpuspeed - CPU cycles per second --每秒执行的标准操作
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
建立测试表
SQL> create table t1 (a int);
Table created
Sql>
declare
begin
for i in 1..10000 loop
insert into t1 values(i);
end loop;
end;
/
SQL>COMMIT;
SQL> select blocks from user_tables where table_name='T1';
BLOCKS
----------
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL> exec dbms_stats.gather_table_stats('ZENGFANKUN','T1');
PL/SQL procedure successfully completed
SQL> select blocks from user_tables where table_name='T1';
BLOCKS
----------
20
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter db_file_mu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> set autotrace traceonly
SQL> select * from t1;
已选择10000行。
已用时间: 00: 00: 00.21
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9926 | 29778 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 9926 | 29778 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 713.978494
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 21.046
MREADTIM 45.384
CPUSPEED 1042
MBRC 12
MAXTHR 54754304
SLAVETHR
9 rows selected
上面的系统信息需要用SYS帐号运行
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(‘start);
……. –运行一些超长查询 全表扫描
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(‘stop’);
MRDS= BLOCKS/ MBRC=20/12=1.6667
如果没有统计信息采用默认计算公式方法:
MBRC=db_file_multiblock_read_count=16
MRDS=20/16=1.25
sreadtim=IOSEEKTIM+db＿block_size/IOTFRSPEED=10+8192/4096=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db＿block_size/IOTFRSPEED=10+16*8192/4096=42
转换下公式
Cost = (#SRds +#MRds * mreadtim / sreadtim +(#CPUCycles / cpuspeed) / sreadtim)
=(0+MRDS*45.384/21.046+(CPUCycles/713.978494)/21.046)
=(1.6667*45.384/21.046)+(1631329/713.978494/21.046)
=3.594+108.564 ????
因为sreadtim是millisecond 毫秒级 换成微妙参与CPU部分运算
=(1631329/713.978494/21046)
=0.108564
=>
=3.594+0.108564=3.702564
由于 _table_scan_cost_plus_one = true 所以加1
3.702564+1=4.702564
Ceil(4.702564)=5 --四舍五入函数
全表扫描最终成本公式:
Cost = ceil((SRds +MRds * mreadtim / sreadtim +(CPU_COST /cpuspeednw)/sreadtim*1000)+1)
默认公式:
= ceil((0 +1.25 * 42 / 12 +(1631329/713.978494)/12*1000)+1)
=ceil((4.375+2284.84333/12000)+1)
=ceil((4.375+0.19040)+1)
=ceil(5.5654)
=6
偏大了!
