执行计划-8：成本、时间等

沃趣技术 2020-02-27

1933

作者林锦森 · 沃趣科技数据库技术专家

出品沃趣科技

现在是时候将更多的关注从主体转移到执行计划给到我们的数字方面的信息。在这个章节，我们将只研究优化器做出的预测(解释计划)，推迟对实际运行时数据(v$sql_plan_statistics_all)的调查。

Getting Started

作为参考的例子，我们从一个非常简单的查询和计划开始：

explain plan for
select
        t1.id, t2.id
from
        t2, t1
where
        t2.n1 = 15
and     t1.n1 = t2.n2
;

select * from table(dbms_xplan.display(null, null, 'projection'));

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   225 |  4500 |    35   (6)| 00:00:01 |
|*  1 |  HASH JOIN                   |       |   225 |  4500 |    35   (6)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |    15 |   180 |    16   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T1    |  3000 | 24000 |    18   (6)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N1"="T2"."N2")
   3 - access("T2"."N1"=15)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "T2"."ID"[NUMBER,22], "T1"."ID"[NUMBER,22]
   2 - "T2"."ID"[NUMBER,22], "T2"."N2"[NUMBER,22]
   3 - "T2".ROWID[ROWID,10]
   4 - "T1"."ID"[NUMBER,22], "T1"."N1"[NUMBER,22]

复制

如果你之前接触过这个系列的前几章，你能很快的找到这个语句执行的顺序，3，2，4，1，0:我们先执行索引范围扫描(第3行)，之后在t2表中找到所需要的数据(第2行)，并在内存中建立hash表，之后对t1表做全表扫描(第4行)并探测内存表，找到匹配的数据(第1行)并传递给最终用户(第0行)。这篇文章中我们将花时间解释数据方面的数据---Rows，Bytes，Cost(%CPU)和Time列。

一个很重要的点需要记住，就是执行计划预测的值只是针对那一行的(当你从视图v$sql_plan_statistics_all查看行源执行统计信息时，情况就不一样了)，第二点，同样也很重要，从子操作到父操作，Cost和Time的计量不断叠加，最后一点，有很多缺陷使得对这些数据做出不准确的解释---四舍五入使问题变得模糊，一些数字丢失了，一些数字出现在错误的行上，计划中的一些数字与在相应的10053中找到的数字不一致。由于各种原因，你可能会发现在某些情况下(我将在后面的几期文章中向您展示一些非常简单的情况)，我所给出的描述似乎是不完整的。

Basic explanation

这里是这些列的简单说明：

Rows预测的一个操作需要向它父操作传递的行数。

Bytes预测的一个操作需要向它父操作传递的字节数。

Cost 此操作一次执行所需的资源预测—包括此操作一次完成所需的每个派生操作的每次执行所需的资源。括号中的数字(%CPU)是可以归因于CPU活动的资源使用的百分比。正如我们将在下面看到的，有两种解释成本的方法。

Time 仅执行此操作一次所需的预计运行时间(小时:分钟:秒)。与成本一样，一次执行操作的时间包括完成一次操作所需的所有子操作的所有执行所花费的时间。

我一直重复使用“预测”这个词和“一次执行”这个短语，但重要的是要记住预测可能(通常)是错误的;优化器所做的预测的一部分——有时是隐藏的一部分——是“这个操作将执行多少次”，所以一个操作的一次执行的数字成为理解整个计划的总数的一个重要方面。

我认为有两种方法可以解释“成本”一栏。多年来，我一直认为优化器的cost数字代表了预期的运行时间。如果你从执行计划输出中获取cost数据，将其乘以系统统计表(aux_stats$)中的单个块读取时间(sreadtim)，然后—考虑到舍入—你生成的结果将是执行计划中报告的时间。或者，如果你不想相信cost代表时间，你可以通过对比这个查询对资源的影响相当于多少(实际的)单块随机读取。

Example

了解了上面的内容，我们可以在例子上应用检测。这是个十分简单的例子，因为每行都只执行一次，我们按照执行的顺序来，3，2，4，1，0.

第三行:索引范围扫描---预测会有15行返回，但是没有返回字节数。我调用dbms_xplan.display请求"projection"的信息，告诉我们计划中每一行生成列的一些信息。在第3行中我们看到返回的唯一列被描述为T2.ROWID[ROWID,10]。我不清楚为什么不将它的大小150(15*10)显示出来，有可能是因为rowid是伪列，而且在dba_tab_columns中没有这个条目，所以无法将avg_col_len这个值给到优化器。范围扫描的成本是1---这是个很小的索引(1个根块和7个叶子块)，优化器假设根块已经被缓存了，之后需要1次物理读找到叶子块。预测的时间就是1*sreadtim-在我的例子中是6毫秒，四舍五入是1秒。

第二行:通过rowid访问表---优化器认为从第三行获得的15个rowid可以让我们从表中获取15行，因为没有任何的过滤条件。根据bytes列的180，我们可以知道每行平均的大小为12字节。检查projection信息，我们可以看到列的信息为"T2"."ID"[NUMBER,22], "T2"."N2"[NUMBER,22]，数据字典中每行的平均大小为4字节，那这里为什么会显示180而不是120呢，也许优化器在计算中错误的包括列T2.N1列，我们在第三行中使用到这个列。这个操作的cost是16，再第三行中再加上15，因为优化器认为这15行分散在15个块中，这些块受到物理读请求的影响。预测时间是16*sreadtim，即96毫秒，也就是1秒。

第四行:对t1表的全表扫描---这个表中有3000行而且没有过滤任何一行。每行平均8字节，所以bytes列显示24000，我们检查projection信息时，会发现返回"T1"."ID"[NUMBER,22], "T1"."N1"[NUMBER,22]，而且数据字典里显示所有的列的avg_row_len是4，这个例子中的算法是符合我们的预期的。cost是18，意味着资源需求等同于18个随机单块读，即使我们知道Oracle会使用多块读扫描表，但是优化器内部还是会有内置的算法在单块读和多块读之间调整。在这个例子中，我们看到6%的cost是由于CPU的使用，这表明扫描表并不只消耗磁盘资源，检查表中的每一行也会消耗大量CPU。如果我们希望有更多的细节，可以直接到plan_table表中查看cost，io_cost和cpu_cost。

第一行：hash join---这里优化器正确的预测了行数，这个连接确实返回了225行(t2中的每一行对应t1中的15行)。但是对于bytes的计算存在异常，预测显示平均每行有20字节，但是projection信息中可以看到，只有平均长度为4的两列，优化器似乎将所有的列(5列)都考虑进来列。cost的计算是所有子操作的相加，所以第一眼看过去，你觉得会是第2行跟第4行的cost相加，但是16+18=34，不是35.这个差异的解释来自于hash join自身的工作。如果我们查看cost，io_cost，cpu_cost，我们可以看到。

   ID OBJECT_NAME       COST   IO_COST  CPU_COST
   -- --------------- ------ --------- ---------
    2 T2                  16         16   120643
    4 T1                  18         17  1108201
    1                     35         33  2131094
复制

可以看到IO的cost是很简单的累加，hash join自身并不引进任何的IO，但是第2行跟第4行的CPU的cost相加为1228844，与第1行的cost不匹配，这说明hash join自身的cost为902250.通过缩放和四舍五入，使得最终的IO cost成本增加了2，hash join的额外CPU也解释了为什么成本的CPU组件仍然是6%：(35-33)/35=0.057。

如果你想知道我为什么要对字节列中的奇怪之处大惊小怪，我可以给你两个原因，首先，对于这个简单的例子来说，可以很方便的知道计划中包含的信息是否有可能是错误的，其次，优化器决定是否使用hash join，根据数据量的大小决定使用哪些表作为内存中的表，哪些是探测表。bytes列的细节可以解释为什么Oracle会选择错误的执行计划。

summary

我们通过一个简单的查询，针对计划中每一行的数字，了解它是怎么来的，代表什么含义。我们看到cost和time是怎么累加的，还了解了成本是如何由I/O成本和CPU成本组成的，并注意到CPU成本可能是总成本的一个重要部分。

| 译者简介

执行计划-8：成本、时间等

林锦森·沃趣科技数据库技术专家

沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验。

评论

相关阅读