SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
创建手动管理的表空间，blockssize 8k
create tablespace test datafile
'/oradata/june/test.dbf' size 50m autoextend on maxsize 200m
uniform size 1m segment space management manual blocksize 8k;
创建测试用户test,默认表空间 test
SQL> create user test identified by oracle default tablespace test;
create table test as select * from dba_objects where 1=0 ;
alter table test pctfree 99 pctused 1;
pctfree 保留空间 用于update
SQL> insert into test select * from dba_objects where rownum<2;
1 row created.
SQL> commit;
Commit complete.
alter table test minimize records_per_block;
SQL> insert into test select * from dba_objects where rownum<1000;
999 rows created.
SQL> commit;
Commit complete.
现在使用了1000个块
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;
SQL> select owner,blocks from dba_tables where owner='TEST' and table_name='TEST';
OWNER BLOCKS
------------------------------ ----------
TEST 1000
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
1000
SQL> alter system set db_file_multiblock_read_count=16;
System altered.
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 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1003 consistent gets
79 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
COST=220
全表扫描成本计算公式:
成本的计算方式如下：
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 CPU时钟周期数
sreadtim - single block read time 一次单块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒)
mreadtim - multi block read time 一次多块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒)
cpuspeed - CPU cycles per second CPU频率(单位MHZ)--每秒钟CPU做多少个轮训
全表扫描多块读 那么#SRds=0
#MRds=16,每次I/O读16个块
mreadtim =1000/16
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)来计算成本，所有的系统全是 用的 非工作量。
计算mreadtim - multi block read time 一次多块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒)
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
ioseektim:寻道寻址的时间
db_file_multiblock_count*db_block_size=16*8K=128K ---一次I/O的数据量
db_file_multiblock_count*db_block_size/iotftspeed=多块读耗时时间
iotftspeed:I/O传输速度
mreadtim(多块读耗时)=寻道寻址的时间+多块读耗时
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(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"
from dual; 2 3 4 5 6 7
mreadtim
----------
42
多块读的耗时有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
单块读耗时12毫秒
操作系统CPU 和磁盘信息Oracle都可以获取到
CPUCycles 等于 PLAN_TABLE里面的CPU_COST---这个ORACLE未解密，无法知道怎么计算的
SQL> explain plan for select count(*) from test;
Explained.
SQL> select cpu_cost from plan_table;
CPU_COST
----------
7271440
cpuspeed 等于 CPUSPEEDNW= 2696.05568
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.
成本的计算方式如下：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed
) / sreadtime
#SRds * sreadtim =0 单块读次数为0
#SRds * sreadtim=(number of single block reads 单块读次数) * 12
#MRds * mreadtim =(number of multi block reads) * 42=1000/16 * 42=2625
CPUCycles / cpuspeed=7271440/2696.05568=2697/1000=2.697 ---每秒转换为每毫秒/1000
sreadtime=12
那么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 = (
#MRds * mreadtim +
CPUCycles / cpuspeed
) / sreadtime
Cost =
#MRds +（
CPUCycles / cpuspeed
) / sreadtime
最终的成本计算公式=Cost = #MRds (忽略CPU的情况下,就是多块读的次数=1000/16)
说明减少 物理 io扫描次数,是SQL优化的核心思想,
Cost = #MRds * mreadtim/ sreadtime
#MRds - number of multi block reads 多块读I/O次数