本文主要是介绍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 |
-----------------------------------------------------------------------------
这篇关于11GR2下基于CBO全表扫描cost计算的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!