数据库高效优化：架构、规范与SQL技巧
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

2.2　成本

在对SQL语句进行优化的过程中，对于成本的理解非常重要。因为Oracle绝大多数情况下就是使用基于成本的优化器对SQL语句制定执行计划的。只有对成本有更深层次的认识，才能理解优化器的行为，也更容易找出产生较差执行计划的原因。但对于成本及其计算方法，Oracle公司并没有开放很多资料，因而只能从一些公开的资料揣摩其工作原理、计算方法等。

下面会对成本的基本概念、计算方法加以简单说明。后面会结合一个SQL案例，阐述如何计算一个成本。

2.2.1　基本概念

成本是指花费在单数据块读取上的时间，加上花费在多数据块读取上的时间，再加上所需的CPU处理时间，然后将总和除以单数据块读取所花费的时间。也就是说，成本是语句的预计执行时间的总和，以单数据块读取时间单元的形式来表示。

成本的概念也是在不断演化中的，在不同的Oracle版本中是不同的。在Oracle 8i的版本中，成本是考虑了I/O子系统所做的请求数，并没有考虑到CPU资源的使用开销以及多数据块访问和单数据块访问的不同。在Oracle 9i中，引入了对CPU成本的计算，此外也加入了对单数据块和多数据块I/O请求的不同的考虑。到了Oracle 10g，又引入了对数据分布特征、缓存数据块等因素的考虑。

2.2.2　计算公式

成本的具体计算公式如下：

Cost = (#SRDs * sreadtim +#MRDs * mreadtim +#CPUCycles /cpuspeed) / sreadtim

公式说明：

·#SRDs：单数据块读取的次数。

·#MRDs：多数据块读取的次数。

·#CPUCycles：CPU时钟频率。

·sreadtim：随机读取单数据块的平均时间，单位为毫秒。

·mreadtim：顺序读取多数据块的平均时间，也就是多数据块平均读取时间，单位为毫秒。

·cpuspeed：代表有负载CPU速度，CPU速度为每秒钟CPU周期数，也就是一个CPU一秒能处理的操作数，单位是百万次/秒。

2.2.3　计算示例

下面通过一个例子，说明如何通过上述公式计算一条SQL语句的运行成本。在此特别强调一下，成本的计算非常复杂，Oracle官方也没有公布其具体的算法。在计算中，受影响的因素也比较多。下面的示例，仅仅作为一个参考，简单描述了计算过程。

下面的示例是在Oracle 10gR2的版本中进行的，此版本的成本计算中既包含了I/O成本，也包含了CPU成本。下面的计算中就包含了两个部分的计算过程。

1）准备工作：

create table t1 as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname=>'HF',tabname=>'T1',estimate_percent=>100);
//创建了一个测试表

2）优化器计算成本：

select * from t1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 51054 |  4636K|   200   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| T1   | 51054 |  4636K|   200   (1)| 00:00:03 |
--------------------------------------------------------------------------
//对于上述这条SQL语句，优化器采用了全表扫描的执行方式，其估算的成本为200

3）10053 Trace：在开始计算之前，先对上述SQL语句进行一次10053的Trace。通过这个跟踪事件可以观察到CBO是如何选择执行计划的。关于这个跟踪事件的具体用法，可参见本书后面的讲解。在后面的计算过程中，我们可以参看这个跟踪事件的输出。

alter session set events '10053 trace name context forever';
select * from t1;
alter session set events '10053 trace name context off';

4）系统统计信息：先来查看一下计算公式，在公式中指标Sreadtim、Mreadtim、cpuspeed跟具体的物理硬件有关。在Oracle数据库中，可通过收集系统级的统计信息得到相关的数据（关于系统的统计信息，可参看后面的统计信息部分）。如果数据库没有收集相应的信息，则此时处于NOWORKLOAD状态，这种情况下可通过几个新的统计参数折算得到我们需要的指标。

在10053的跟踪事件中，我们可以找到相关的部分：

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats            
  CPUSPEED: 1251 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)

从上面输出中可见，这条语句执行时是使用NOWORKLOAD的状态，即此时没有收集系统的统计信息。CPUSEED已经给出，此外还给出另外两个统计参数IOTFRSPEED、IOSEEKTIM。我们所需要的指标可以通过如下关系进行折算。在计算中，还涉及另外两个系统参数：一个是块大小，由db_block_size参数设定，当前系统为8K；另外一个是一次多数据块读取的块数，由db_file_multiblock_read_count参数设定，当前系统为8。

Sreadtim = ioseektim + db_block_size/iotrfrspeed
      = 10 + 8192/4096 = 12
Mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size/iotfrspeed
      = 10 + 8*8192/4096 = 26

5）对象统计信息：在优化器计算成本时，还需要参考对象级的统计信息。我们可以通过数据字典查看，也可以在10053的Trace文件中找到。在此跟踪输出中，相关部分如下。

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 51054  #Blks:  723  AvgRowLen:  93.00      
//从上面的输出中可见，表T1的块数为723。对应于全表扫描而言，需要读取723个8K的数据块。

6）计算I/O成本：前面提到过，成本的计算分为两个部分，分别为I/O和CPU。下面简单看一下I/O的计算过程。前面提到的计算公式如下。

Cost = (
        #SRDs * sreadtim +
        #MRDs * mreadtim +
        #CPUCycles /cpuspeed
        ) / sreadtim

简单变换一下：

Cost = (
        #SRDs +
        #MRDs * mreadtim/sreadtim +
        #CPUCycles/(cpuspeed * sreadtim)
        )

其中前两行为I/O成本，暂不考虑最后一行，因为这条语句为全表扫描，使用的是多数据块读取的方式，所以，I/O成本计算值考虑到第二行即可。

IO_Cost = #MRDs * mreadtim/sreadtim
        = ceil(723/8) * 26 / 12
          = 197.17
//系统总共需要读取723个数据块，每次读取8个块，共需要ceil(723/8)=91次

7）计算CPU成本：

CPU_Cost = #CPUCycles/(cpuspeed * sreadtim)
        = 25059861/(1251*12000) 
           = 1.67
//总的CPU处理次数是从10053中得到的，后面会说明。整体CPU成本为1.67

8）验证成本：下面解读一下10053的成本计算，可与上面我们手工计算的部分进行对比。

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: T1  Alias: T1     
    Card: Original: 51054  Rounded: 51054  Computed: 51054.00  Non Adjusted: 51054.00 
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  199.67  Resp: 199.67  Degree: 0      //CPU成本为199.67 - 198 = 1.67
Cost_io: 198.00  Cost_cpu: 25059861            //IO成本为198
Resp_io: 198.00  Resp_cpu: 25059861
  Best::AccessPath: TableScan
       Cost: 199.67  Degree: 1  Resp: 199.67  Card: 51054.00  Bytes: 0

从10053可见，优化器计算的I/O成本为198.00（对应于Cost_io）。这一点和计算得到的197.17非常接近。考虑到系统中有隐含参数，计算成本时一般向上取整。可以认为两者就是一致的。对于CPU成本计算，Cost_cpu: 25059861就是前边引用的CPUCycles。整体CPU成本为总成本减去I/O成本，即199.67–198＝1.67。这和我们前面计算的完全一致。

本周热推：
数据库云平台理论与实践数据挖掘与机器学习-WEKA应用技术与实践（第二版）Microsoft Power BI 智能大数据分析数字媒体交互设计（初级）：Web产品交互设计方法与案例大学计算机基础（第2版）
上一章目录下一章