Oracle SQL Tuning_CBO算法02_全表扫描成本计算

全表扫描成本计算 Full Table Scan - FTS:

• Workload System Statistics are gathered
• CPU Costing is enabled

FTS Cost = I/O Cost + CPU Cost
I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))
#MRds = #Blks / MBRC
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)

创建测试表并插入数据

create table fulltable as select * from dba_objects where 1=0 ;

设置pctfree 99,一行一个block
alter table fulltable  pctfree 99 pctused 1;
insert into fulltable  select * from dba_objects where rownum<2;
alter table fulltable  minimize records_per_block;
insert into fulltable  select * from dba_objects where rownum<1000;
commit;

收集表统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CBO',
tabname => 'FULLTABLE',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

查看表的块数(1000)

select owner,blocks from dba_tables where owner='CBO' and table_name='FULLTABLE';

查看参数，得出一次多块读的块数(16)

show parameter db_file_multiblock_read_count

查看执行计划，Cost(220)

explain plan for select count(*) from fulltable;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 2411201521

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   220   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| FULLTABLE |  1000 |   220   (0)| 00:00:03 |
------------------------------------------------------------------------

收集系统统计信息(无需操作，这里只是展示命令，不用真正执行)

无负载模式
 exec dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD');
负载模式
 手工
 exec dbms_stats.gather_system_stats(gathering_mode => 'start');
 exec dbms_stats.gather_system_stats(gathering_mode => 'stop');
 自动
 execdbms_stats.gather_system_stats(gathering_mode => 'interval',interval => 30);

查询系统统计信息

SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     3074.07407
IOSEEKTIM                              10
IOTFRSPEED                           4096
CPUSPEED
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

sys.aux_stats$ 参数名称描述 

CPUSPEEDNW       CPU在无负载模式下的处理速度，即每秒可以完成的机器指令数，单位是百万次，10g默认为1。
IOSEEKTIM        IO寻址时间，即IO寻址需要的时间，单位是毫秒，默认为10。
IOTFRSPEED       IO传输速度，即每毫秒传输的字节数，默认为4096。
CPUSPEED         CPU在负载模式下的处理速度，即每秒可以完成的机器指令数，单位是百万次。
MAXTHR           IO系统的最大吞吐量，单位为每秒字节数。
SLAVETHR         单个并行的最大吞吐量，单位为每秒字节数。
SREADTIM         单块读的平均读取时间，单位为毫秒。 
MREADTIM         多块读的平均读取时间，单位为毫秒。 
MBRC             系统设置多块数据读的块数。

Full Table Scan 成本计算过程

多块读的平均时间 mreadtim(42)

mreadtim = ioseektim + db_file_multiblock_count * db_block_size / iotftspeed

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;

单块读的平均时间 sreadtim(12)

sreadtim = ioseektim + db_block_size / iotfrspeed 

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; 

CPUCycles (7271440)

explain plan for select count(*) from fulltable;
select cpu_cost from plan_table;

IO COST (220)

I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))
SELECT 1+CEIL((1000/16)*(42/12)) FROM DUAL;

CPU COST (0)
 

CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
SELECT ROUND(7271440/3074.07407/1000/12) FROM DUAL;

FTS Cost  (220)

FTS Cost = I/O Cost + CPU Cost = 220 + 0 = 220

隐含参数：_tablescan_cost_plus_one

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%';
   
NAME                           VALUE    DESCRIB
------------------------------ -------- ------------------------------------------------------------
_table_scan_cost_plus_one      TRUE     bump estimated full table scan and index ffs cost by one