基于全表扫描计算总体成本cost方法

最新推荐文章于 2022-12-24 16:09:15 发布

blockheading

最新推荐文章于 2022-12-24 16:09:15 发布

阅读量884

点赞数

分类专栏： Oracle Performance

本文链接：https://blog.csdn.net/u013169075/article/details/72887642

版权

Oracle Performance 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

CBO生成执行计划时需要估算cost值（成本计算），而这种估算是利用对象和系统统计信息，使用特定的公式算法来计算得出的。我们看看使用非工作量统计信息（noworkload）进行全表扫时，CBO是如何计算的。

1. 什么是工作量系统统计信息和非工作量统计信息？
CBO在进行成本计算时，需要估算从磁盘中读取一定块数所消耗的时间，这势必需要知道CPU的处理性能、磁盘寻道/传输速度这些相关的系统信息。收集这部分内容也是“收集统计信息”中的一部分，使用过程DBMS_STATS.GATHER_SYSTEM_STATS来收集，收集的信息存放在aux_stats$表中。
 DBMS_STATS.GATHER_SYSTEM_STATS (
   gathering_mode   VARCHAR2 DEFAULT 'NOWORKLOAD',
   interval         INTEGER  DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);
收集系统统计信息有两种方式，通俗地来讲就是如下两种情况：
第一种：假设我们的系统现在很忙，我们利用现有的实际负载进行相关统计信息的收集，这是工作量统计信息（workload）。实现方式是：（gathering_mode=>'INTERVAL',interval=>30）
第二种：假设我们的系统现在非常空闲，此时我想收集系统统计信息，但是此时并没有会话来产生磁盘读写，那何以知道并收集磁盘处理信息的能力呢？答案是：让oracle自动在后台运行一些任务来产生磁盘读写负载，进而收集系统统计信息，这就是非工作量统计信息（noworkload）。单单从名字的字面意译上也能了解它们之间的区别。实现方式是：(gathering_mode=>'noworkload')

两种方式收集的系统统计信息存放在aux_stats$表中，官方并没有对外正式公布该表。每种方式收集的信息放在由pname字段指定地特定行。2.中将会分析分别如何存放。同时我们又要知道，当aux_stats$中没有工作量统计信息时，使用非工作量统计信息，否则使用工作量统计信息。非工作量统计信息总是存在的，即使你删除了非工作量系统统计信息，则在oracle重启后将会自动收集非工作信息。

2. 在接下来的讨论中，我们将会用到以下参数和字段名，这里需要知道：
参数：
    db_file_multiblock_read_count ：定义在全表扫时一次可以最多读取的数据块数，在OLTP系统中建议在4-16，太大并没有益处。
    db_block_size：数据库块的大小

字段名（来自aux_stats$表）：
1）Noworkload Statistics Stored in the Data Dictionary
Name                              Description
CPUSPEEDNW                The number of operations per second (in millions) that one CPU is able to process.
IOSEEKTIM                      Average time (in milliseconds) needed to locate data on the disk. The default value is 10. 
IOTFRSPEED                    Average number of bytes per millisecond that can be transferred from the disk. The default value is 4,096.

2） Workload Statistics Stored in the Data Dictionary
Name                             Description
CPUSPEED                     The number of operations per second (in millions) that one CPU is able to process
SREADTIM                     Average time (in milliseconds) needed to perform a single-block read operation
MREADTIM                    Average time (in milliseconds) needed to perform a multiblock read operation
MBRC                             Average number of blocks read during a multiblock read operation
MAXTHR                        Maximum I/O throughput (in bytes per second) for the whole system
SLAVETHR                      Average I/O throughput (in bytes per second) for a parallel processing slave
统计信息有什么用：例如记录IO的寻道时间、IO传送数据的速度、CPU处理速度这些信息可以用于CBO预估执行计划所做的消耗，进而确定执行计划。

3）其它：
#SRds - number of single block reads
#MRds - number of multi block reads
    
3. 总cost≈io_cost+ cpu_cost/（cpuspeednw*sreadtim*1000）
CBO计算cost（成本）时需要用IO的开销加上CPU的开销，这样的加法是符合我们的逻辑的，但二者的计算单位不同，不能直接相加得出总的cost。这需要将cpu_cost转换为每秒钟可以执行的单块读的数量（这种转换是等价的），"cpu_cost/（cpuspeednw sreadtim*1000）”式子中的分母就是用来实现这样的一个转换（每个字符串的含义在1.中有解释）。接下来我们逐步公式中分析每一个分子式是如何计算得出的。
note：上述算法是使用非工作量统计信息时的值，如果使用工作量的统计信息时需要用cpuspeed替代cpuspeednw。

4. io_cost
io_cost=#SRds+(#MRds*mreadtim) / sreadtim
io_cost开销按照正常的逻辑思考应为：单块读的次数（#SRds）+多块读的次数（#MRds），但多块读的代价显然和单块读的代价不同，所以查询优化器通常根据单块读来换算开销。因此我们需要将多块读转换为单块读（这种转换同样应该是等价的）：(#MRds*mreadtim) / sreadtim：
所以，io_cost=#SRds+(#MRds*mreadtim) / sreadtim

#SRds：以单块读方式读到的数据块数就是单块读的次数，这个值由CBO来决定。
#MRds ：
1）使用非工作量系统统计信息时：
     #MRds=Blocks/db_file_multiblock_read_count(即多块读的次数除以每次读的数据块数量)
2）使用工作量统计信息时：
    #MRds=Blocks/mbrc （mbrc：多块读时平均每次读的数据块的数量，因为不可能每次多块读的数量都是db_file_multiblock_read_count参数定义的值。但使用非工作量统计信息时，aux_stats$表中mbrc字段值为空，所以只能使用db_file_multiblock_read_count参数替代） 

sreadtim:
1）使用非工作量系统统计信息时：
    sreadtim=ioseektim+db_block_size/iotfrspeed
2）使用工作量统计信息时：
    在aud_stats$表中sreadtime字段的值
              
mreadtim：
1）使用非工作量系统统计信息时：
    mreadtim=ioseektim+（db_file_multiblock_read_count *db_lock_size）/iotfrspeed
2）使用工作量系统统计信息时：
    aud_stats$表中mreadtim字段的值，但以下两种情况下CBO不在使用aud_stats$表中的值
    当sreadtim、mreadtim或mbrc不可用时
    当mreadtim小于或等于sreadtim时
    此时cbo将使用前面的sreadtim公式计算sreadtim，使用如下的公式计算mreadtim
         mreadtim=ioseektim+（mdrc *db_lock_size）/iotfrspeed
note：其实对于使用非工作量统计信息和工作量统计信息时，计算公式的逻辑是一样，只是二者在aux_stats$表中对应的字段不同，所以应灵活替换。接下来的探讨中，将不在讨论使用工作量系统统计信息的情况，全部是使用非工作量统计信息时的情况。

到此，得知IO的消耗应为(使用非工作量统计信息)：
io_cost= #SRds + (#MRds*mreadtim) / sreadtim
#MRds=Blocks/db_file_multiblock_read_count
sreadtime=ioseektim+db_block_size/iotfrspeed
mreadtim=ioseektim+（db_file_multiblock_read_count *db_lock_size）/iotfrspeed

后三者的计算公式中所用的多数分子式都可以在aud_stats$视图中查到，db_block_size，db_file_multiblock_read_count 则是初始化参数，Blocks则是在一个执行计划中将扫描的块数，由CBO根据对象的统计信息估算确定。同时我们可以发现，在使用非工作量统计信息时，db_file_multiblock_read_count在公式中以分母的形式存在，如果该参数设置过大，io_cost值会很小，CBO认为多块读的代价很小，在选择执行计划时会更倾向于使用全表扫描，影响执行高效的执行计划的产生。

5.  cpu_cost/（cpuspeednw* sreadtim*1000）
cpu_cost：执行计划的信息会存放在plan_table表中，而该表中的cpu_cost字段的值就是我们所需的值。
cpuspeeddnw：在aux_stat$表中可以查到该字段值，其含义在最开始已经解释。
sreadtime：在4.2中已经计算得出
note：上述是使用非工作量统计信息下的公式，如果使用工作量统计信息，则用cpuspeed替代cpuspeednw.

6. 总结：
最后我们可以得出总的消耗为：
    1）使用非工作量的统计信息时：
       cost=io_cost+ cpu_cost/（cpuspeednw*sreadtim*1000）
    2）使用工作量的统计信息时：
        cost=io_cost+ cpu_cost/（cpuspeed*sreadtim*1000）

--创建模拟表
SYS@PROD1> create table t (id number) tablespace segman;

Table created.

SYS@PROD1> insert into t values (1);

1 row created.

SYS@PROD1> alter table t minimize records_per_block; 

Table altered.

SYS@PROD1> insert into t select rownum from dba_objects where rownum<2000;

1999 rows created.

SYS@PROD1> commit;

Commit complete.

SYS@PROD1> exec dbms_stats.gather_table_stats('SYS','T',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE);

PL/SQL procedure successfully completed.

SYS@PROD1> select owner,table_name,blocks from dba_tables where table_name='T' and owner='SYS';

OWNER			       TABLE_NAME			  BLOCKS
------------------------------ ------------------------------ ----------
SYS			       T				      1000

SYS@PROD1> show parameter db_file_multiblock

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count	     integer	 16
SYS@PROD1> show parameter db_block_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_size			     integer	 8192

--收集非工作量统计信息
SYS@PROD1> exec dbms_stats.gather_system_stats('NOWORKLOAD');

PL/SQL procedure successfully completed.

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

PNAME		       PVAL1
-------------------- -------
CPUSPEEDNW		 1022
IOSEEKTIM		  12
IOTFRSPEED	       53797
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

9 rows selected.

SYS@PROD1> set autot on
SYS@PROD1> select count(*) from t;

  COUNT(*)
----------
      2000


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    77   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| T	  |  2000 |    77   (2)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
       1012  consistent gets
	  0  physical reads
	  0  redo size
	422  bytes sent via SQL*Net to client
	419  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  3  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
	  
SYS@PROD1> explain plan for select count(*) from t;

Explained.

SYS@PROD1> select cpu_cost from plan_table;

  CPU_COST
----------
   7421440

cpuspeednw=1022
ioseektim=12
iotfrspeed=53797
db_file_multiblock_read_count=16
db_lock_size=8192

cost=io_cost+ cpu_cost/（cpuspeednw*sreadtim*1000）
   = [#SRds + (#MRds*mreadtim) / sreadtim]+ cpu_cost/（cpuspeednw*sreadtim*1000）
其中#SRds=0
       #MRds=1000/16=62.5
        mreadtim= ioseektim+（db_file_multiblock_read_count *db_lock_size）/iotfrspeed
                     =12+(16*8192)/53797
                     =14.4364184
       Sreadtim= ioseektim+db_block_size/iotfrspeed
              =12+8192/53797
              =12.1522761
       Cpu_cost=SQL> explain plan for select count(*) from t;  
                          Explained.
                          SQL> select cpu_cost from plan_table;
                          CPU_COST
                           ----------
                       = 7421440	   
cost=(0+62.5*14.4364184/12.1522761)+7421440/(1022*12.1522761*1000)=74.8450599

约等于75，可能由于中间计算位数丢失有点偏差，理论上应该是76。
--oracle使用了隐含参数_table_scan_cost_plus_one，该参数使得在table full scan和index fast full scan的时候会将cost+1

参考资料来源：http://www.fx114.net/qa-161-159231.aspx

blockheading

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
基于全表扫描计算总体成本cost方法

CBO生成执行计划时需要估算cost值（成本计算），而这种估算是利用对象和系统统计信息，使用特定的公式算法来计算得出的。我们看看使用非工作量统计信息（noworkload）进行全表扫时，CBO是如何计算的。1. 什么是工作量系统统计信息和非工作量统计信息？CBO在进行成本计算时，需要估算从磁盘中读取一定块数所消耗的时间，这势必需要知道CPU的处理性能、磁盘寻道/传输速度这些相关的系统信息。收
复制链接

扫一扫