数据库版本oracle11gr2sqlgt; select * from v$version where rownum=1;banneroracle database 11g enterprise edition release
数据库版本oracle11gr2
sql> select * from v$version where rownum=1;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.1.0 - production
创建手动管理的表空间，blockssize 8k
sql> create tablespace test datafile
'/u01/app/oracle/oradata/robinson/datafile/test.dbf' size 50m autoextend on maxsize 200m
uniform size 1m segment space management manual blocksize 8k; 2 3
tablespace created.
创建测试用户test,默认表空间 test
sql> create user test identified by oracle default tablespace test;
user created.
为了简便，授权dba给test
sql> grant dba to test;
grant succeeded.
创建测试表test
sql> create table test as select * from dba_objects where 1=0 ;
table created.
设置pctfree 99
sql> alter table test pctfree 99 pctused 1;
table altered.
sql> insert into test select * from dba_objects where rownum
1 row created.
确保一行一个block
sql> alter table test minimize records_per_block;
table altered.
sql> insert into test select * from dba_objects where rownum
999 rows created.
sql> commit;
commit complete.
收集表统计信息
sql> begin
dbms_stats.gather_table_stats(ownname => 'test',
tabname => 'test',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => dbms_stats.auto_degree,
cascade=>true
);
end;
/ 2 3 4 5 6 7 8 9 10
pl/sql procedure successfully completed.
sql> select owner,blocks from dba_tables where owner='test' and table_name='test';
owner blocks
------------------------------ ----------
test 1000
sql> show parameter db_file_multiblock_read_count
name type value
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
全表扫描的成本等于220
sql> select count(*) from test;
execution plan
----------------------------------------------------------
plan hash value: 1950795681
-------------------------------------------------------------------
| id | operation | name | rows | cost (%cpu)| time |
-------------------------------------------------------------------
| 0 | select statement | | 1 | 220 (0)| 00:00:03 |
| 1 | sort aggregate | | 1 | | |
| 2 | table access full| test | 1000 | 220 (0)| 00:00:03 |
-------------------------------------------------------------------
成本的计算方式如下：
cost = (
#srds * sreadtim +
#mrds * mreadtim +
cpucycles / cpuspeed
) / 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
注意：如果没有收集过系统统计信息，那么oracle采用非工作量统计， 如果收集了，，oracle采用工作量统计的计算方法
sql> select pname, pval1 from sys.aux_stats$ where sname='sysstats_main';
pname pval1
------------------------------ ----------
cpuspeed
cpuspeednw 2696.05568
ioseektim 10
iotfrspeed 4096
maxthr
mbrc
mreadtim
slavethr
sreadtim
9 rows selected.
我这里因为mbrc 为0，所以cbo采用了非工作量(noworkload)来计算成本
#srds=0,因为是全表扫描，单块读为0
#mrds=表的块数/多块读参数=1000/16
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
sql> select (select pval1 from sys.aux_stats$ where pname = 'ioseektim') +
2 (select 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
3 4 5 6 7 from dual;
mreadtim
----------
42
sreadtim=ioseektim+db_block_size/iotfrspeed
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
cpucycles 等于 plan_table里面的cpu_cost
sql> explain plan for select count(*) from test;
explained.
sql> select cpu_cost from plan_table;
cpu_cost
----------
7271440
cpuspeed 等于 cpuspeednw= 2696.05568
那么cost=1000/16*42/12+7271440/2696.05568/12/1000
sql> select ceil(1000/16*42/12+7271440/2696.05568/12/1000) from dual;
ceil(1000/16*42/12+7271440/2696.05568/12/1000)
----------------------------------------------
219
手工计算出来的cost用四舍五入等于219，和我们看到的220有差别， 这是由于隐含参数_tablescan_cost_plus_one参数造成的
sql> select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
from x$ksppi x, x$ksppcv y
where x.inst_id = userenv ('instance')
and y.inst_id = userenv ('instance')
and x.indx = y.indx
and x.ksppinm like '%_table_scan_cost_plus_one%'
/ 2 3 4 5 6 7
name value describ
------------------------------ ---------- ------------------------------
_table_scan_cost_plus_one true bump estimated full table scan
and index ffs cost by one
根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1
那么我把改参数禁止了试一试
sql> alter session set _table_scan_cost_plus_one=false;
session altered.
sql> set autot trace
sql> select count(*) from test;
execution plan
----------------------------------------------------------
plan hash value: 1950795681
-------------------------------------------------------------------
| id | operation | name | rows | cost (%cpu)| time |
-------------------------------------------------------------------
| 0 | select statement | | 1 | 219 (0)| 00:00:03 |
| 1 | sort aggregate | | 1 | | |
| 2 | table access full| test | 1000 | 219 (0)| 00:00:03 |
-------------------------------------------------------------------
这次得到的cost等于219，与计算值正好匹配，现在更改db_file_multiblock_read_count参数
sql> alter session set db_file_multiblock_read_count=32;
session altered.
这个时候 sreadtim=12
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
mreadtim=74
sql> select (select pval1 from sys.aux_stats$ where pname = 'ioseektim') +
(select value
2 3 from v$parameter
4 where name = 'db_file_multiblock_read_count') *
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
----------
74
那么cost等于
sql> select ceil(1000/32*74/12+7271440/2696.05568/12/1000) from dual;
ceil(1000/32*74/12+7271440/2696.05568/12/1000)
----------------------------------------------
193
sql> set autot trace
sql> select count(*) from test;
execution plan
----------------------------------------------------------
plan hash value: 1950795681
-------------------------------------------------------------------
| id | operation | name | rows | cost (%cpu)| time |
-------------------------------------------------------------------
| 0 | select statement | | 1 | 193 (0)| 00:00:03 |
| 1 | sort aggregate | | 1 | | |
| 2 | table access full| test | 1000 | 193 (0)| 00:00:03 |
-------------------------------------------------------------------
与计算的cost相匹配，从实验种可以得出，在11gr2中，全表扫描计算cost的方式依然和9i/10g一样，没有变化。
相关链接