SQL优化最佳实践：构建高效率Oracle数据库的方法与技巧
上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。这和我们前面计算的完全一致。

上一章目录下一章