11GR2下基于CBO全表扫描cost计算

##########################################################
##11gr2下基于cbo优化器，在不做系统统计信息收集下全表扫描的成本计算####
##########################################################
CBO的成本计算设计到非工作负载下的系统统计信息
CPUSPEEDNW=>系统上每个CPU每秒钟可以执行的标准操作的次数。后缀NW表明这是个非工作负载下的估计
IOSEEKTIM=>在磁盘上定位数据的平均时间。虽然被称为寻道（seek）时间，但实际上是从磁盘检索一个数据块的总时间，包含磁盘的旋转时延、传输时延与寻道时间
IOTFRPEED=>磁盘的IO传输速率（单位字节/毫秒）
实验机其非工作负载下的系统统计信息
SQL> select pname,pval1 from sys.aux_stats$ where pname in('CPUSPEEDNW','IOSEEKTIM','IOTFRSPEED');
PNAME                                                             PVAL1
--------------------------------------------------- ----------           
CPUSPEEDNW                                                   3137
IOSEEKTIM                                                         9
IOTFRSPEED                                                       4096
show parameter db_file_multiblock_read_count
为128，即进行多块读时，每次128个块
默认值为128，建议将多块读的块数设置小点
alter session set db_file_multiblock_read_count=24;
SQL> create table customer as select * from sh.customers;
Table created.

begin
dbms_stats.gather_table_stats(
ownname=>'trsen',
tabname=>'customer',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 1');
end;

select blocks from user_tables where table_name='CUSTOMER';
    BLOCKS
----------
      1485

set autotrace trace explain
SQL> set autotrace trace explain
SQL> select * from customer;
Execution Plan
----------------------------------------------------------
Plan hash value: 2844954298
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 55500 |  9810K|   323   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| CUSTOMER | 55500 |  9810K|   323   (1)| 00:00:04 |
------------------------------------------------------------------------------
==>从执行计划来看，此语句的所耗费的成本是323毫秒

set autotrace off;
==>#SRds=0,因为是全表扫描，并且多块的块数设置的较小单块读为0，全都使用的是多块读
==>#MRds=表的块数/多块读参数
=1485/24=61.875
==>mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
=9+24*8192/4096
=57
==>sreadtim=ioseektim+db_block_size/iotftspeed
=9+8192/4096=11

explain plan for select * from customer;
select cpu_cost from plan_table;
SQL> select cpu_cost from plan_table;
  CPU_COST
----------
  43320338

CPUSPEEDNW=3137
cost=61.875*57/11+43320338/3137/11/1000
=320.625+1.255407250702756
=321.8804072507028
大约为322

SELECT i.ksppinm NAME, v.ksppstvl VALUE, i.ksppdesc describ FROM x$ksppi i,x$ksppcv v
WHERE i.inst_id = USERENV ('Instance')
AND v.inst_id = USERENV ('Instance')
AND i.indx = v.indx
AND i.ksppinm LIKE '%_table_scan_cost_plus_one%';
==>bump estimated full table scan and index ffs cost by one

在table full scan和index fast full scan的时候会将cost+1即322+1=323

将其false
alter session set "_table_scan_cost_plus_one"=false;
SQL>  set autotrace trace explain
SQL>  select * from customer;
Execution Plan
----------------------------------------------------------
Plan hash value: 2844954298
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 55500 |  9810K|   322   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| CUSTOMER | 55500 |  9810K|   322   (1)| 00:00:04 |
-----------------------------------------------------------------------------