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 32910
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
23563
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_adaptive_multi_user boolean TRUE
SQL> explain plan for select * from test where owner='SYS';
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77444 | 7336K| 7223 (1)| 00:01:27 |
|* 1 | TABLE ACCESS FULL| TEST | 77444 | 7336K| 7223 (1)| 00:01:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
已选择13行
COST=7223
select count(*) from test where owner='SYS';
--986880
全表扫描成本计算公式:
成本的计算方式如下：
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 =32910/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 * from test where owner='SYS';
已解释。
SQL> select cpu_cost from plan_table;
CPU_COST
----------
720716510
720716510
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=32910/16 * 42=86388.75
sreadtime=12
SQL> select ceil(32910/16*42/12 + 720716510/2696.05568/1000/12) from dual;
CEIL(32910/16*42/12+720716510/2696.05568/1000/12)
-------------------------------------------------
7222
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次数