执行计划-12：基数反馈

沃趣技术 2020-07-01

257

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

出品沃趣科技

之前的部分我介绍了3种获得一个查询执行时统计信息的方法，对其中一种方法详细介绍了我们获得了哪些信息以及如何使用它们。这篇文章中，我对于这个方法进行了扩展，来帮助我们确定和解决一些性能问题。这个方法被称为基数反馈(Cardinality Feedback)。

Skewed Data

我有一个没有正确分析过的查询，如下所示：

select
t1.id,
t2.small_vc
from
t1,
t2
where
t1.date_ord >= trunc(sysdate) - 14
and t1.supp_id = 1
and t2.id = t1.id
order by
t1.id
;
复制

我通过设置statistics_level为all来开启数据源执行统计信息，我在内存里获得了以下执行计划：

select * from table(dbms_xplan.display_cursor(null,null,'iostats last +cost'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |   206 (100)|   2800 |00:00:00.23 |     799 |
|   1 |  SORT ORDER BY                 |         |      1 |      1 |   206   (2)|   2800 |00:00:00.23 |     799 |
|   2 |   NESTED LOOPS                 |         |      1 |      1 |   205   (1)|   2800 |00:00:00.21 |     799 |
|   3 |    NESTED LOOPS                |         |      1 |      1 |   205   (1)|   2800 |00:00:00.15 |     560 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1      |      1 |      1 |   202   (1)|   2800 |00:00:00.09 |     200 |
|*  5 |      INDEX RANGE SCAN          | T1_DATE |      1 |  14013 |    41   (3)|  14000 |00:00:00.04 |      40 |
|*  6 |     INDEX RANGE SCAN           | T2_I1   |   2800 |      1 |     2   (0)|   2800 |00:00:00.03 |     360 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2      |   2800 |      1 |     3   (0)|   2800 |00:00:00.02 |     239 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T1"."SUPP_ID"=1)
   5 - access("T1"."DATE_ORD">=TRUNC(SYSDATE@!)-14)
   6 - access("T2"."ID"="T1"."ID")
复制

注意我在调用display_cursor()时添加了额外的参数”+cost”，它会报告优化器认为这些资源的成本是多少，这一小段信息作为E-Rows信息的补充非常有用。我使用了”iostats”选项而不是”allstats”，我不常用这个，但是这是一个消除内存统计信息的方法，内存统计信息会让这个报告显示得更宽但是只包含一点额外的信息，比如”sort order by”操作需要100KB的内存完成最优的工作区操作。

在我们比对预估和实际数据之前，我们可以注意到明显的问题:第4和第5行的E-rows告诉我们，Oracle期望通过索引t1_date获得14000个rowid，但是通过rowid去访问表只返回了一行，这个查询最多的成本(202-41)消耗在访问表上。优化器似乎认为选择一个比较差的索引是执行这个查询最好的选择。来看看第5行的access()和第4行的filter()，我们可能决定(a)扩展列(date_ord)上存在的索引，使其变成为(date_ord,supp_id)，(b)如果列(supp_id)上存在索引，扩展索引使其变成为(supp_id.date_ord),(c)在列(supp_id)上创建索引，因为这个列上应该有索引但是缺失了。

即使没有正确的索引，但是也不影响我们对比真实和预估的数据，因为这可能有助于我们对底层执行做出明智的决定。

这个计划的执行顺序是5，4，6，3，7，2，1，我们也从这个顺序开始比对，记住，在对比E-Rows和A-Rows之前，一定要乘以Starts的值：

5 — index range scan — 预估14013行，执行1次；实际14000行。预测较准确。

4 — table access by rowid — 预估1行，执行1次；实际2800行。预测误差大。

对于第4行我们有两点担心：1.为什么预测误差会这么大？2.错误的预测会对整个执行计划造成不好的影响吗？

6 — index range scan — 预估1行，执行2800次；实际2800行。预测准确。

在经历了第4行错误的预测后，我们又重新正确预测了第6行，这是为什么？因为即使对比E-Rows*Start的值与A-Rows的值相等，但这也只是局部间一行行的比对，局部正确的预测并不代表执行计划整体的正确预测。

这个例子中，如果我们通过表t2里id列的索引去访问表的话，我们一次执行会获得一个rowid，并且只访问很小的buffer就能获取一行，从预测来看是这样的，这个预测也很准确。但是实际上我们需要执行这个操作2800次，这可能不是一个高效率的方式。

报告中我们没有一列是”E-Starts”，如果有的话它的值可能是1(嵌套循环中驱动表的E-Rows的值)，这将帮助我们判断预测中最重要的错误是发生在什么时候。当我们看到A-Rows列时，我们需要知道这些数据的大小是每次执行时的行数，还是总的执行行数。

3 — nested loop — 预估1行，执行1次；实际2800行。预测误差大。

7 — table access by rowid — 预估1行，执行2800次；实际2800行。预测准确。

2 — nested loop — 预估1行，执行1次；实际2800行。预测误差大。

1 — sort order by — 预估1行，执行1次；实际2800行。预测误差大。

我们可以总结下这个查询执行计划的状态，第4和第5行的E-Rows表明优化器知道并不存在高效率的访问t1表的路径，当我们比较第4行中的E-Rows和A-Rows时，我们可以看到优化器对于表t1中的数据并不了解，也是从这行开始整个执行计划的预测变得很不靠谱，因为这是最开始的误差较大的预测。忽略这个最开始的错误，这行后面的每一步(连接和访问方式)都是按照优化器的期望去执行的，每次执行会选择选择性高的索引去访问表并返回小数据量的行数，但是也是从这行开始，我们会潜在的认为这个执行计划是一个不好的执行计划。这就引出了两个关于将带有数据源执行统计信息的执行计划用作突出显示性能问题根本原因的工具的一般要点：

1.执行计划中的任何地方，实际和预估的差距都可能指向数据库基础结构的缺陷，包括选择性较差的索引，统计信息的缺失等。

2.执行计划中，最早出现实际与预估有偏差的地方，往往就是导致优化器选择错误执行计划的原因，我们首先要调查这个错误原因。

在这个例子中，数据存在严重的倾斜。t1表中有数百个供应商ID，但是其中一小部分供应商ID占了很大一部分的数量。因为我没有在列supp_id上创建直方图，导致优化器认为表上不同ID的行数为(num_rows/supp_id.num_distinct)，但是供应商1是我的大货供应商之一，过去两周供应了2,800件货物。

对于这类问题，没有简单、通用的方案，最好的策略就是妥协，但是也取决于很多的变量，所以这里就不详细叙述了。不过，作为对主要主题的补充说明，我会提到一点，因为我没有在这个查询中使用绑定变量，在11.2.0.4版本中，由于t1表上有两个谓词，会导致优化器将首次产生的游标的’is_sharable’值设为’N’，‘use_feedback_stats’的值设为‘Y’，并且再下一次执行时重新优化这条语句。这会产生一个不同的执行计划，计划中包含对t1表的hash join(对t1表的访问仍然使用相同的索引)。

标量子查询缓存

我们遇到的另一类的问题是，数据源执行统计信息可以在标量子查询方面有所帮助，不论是在select列表中或者在where子句中。下面是之前章节的一个例子：

select
        count(*)
from    (
        select  /*+ no_merge */
                outer.*
        from
                emp outer
        where
                outer.sal > (
                        select  /*+ no_unnest */
                                avg(inner.sal)
                        from
                                emp inner
                        where
                                inner.dept_no = outer.dept_no
                )
        )
;    

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |       |   245 |
|   1 |  SORT AGGREGATE       |      |     1 |       |       |
|   2 |   VIEW                |      |   167 |       |   245 |
|*  3 |    FILTER             |      |       |       |       |
|   4 |     TABLE ACCESS FULL | EMP  | 20000 |   156K|    35 |
|   5 |     SORT AGGREGATE    |      |     1 |     8 |       |
|*  6 |      TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
              AVG("INNER"."SAL") FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   6 - filter("INNER"."DEPT_NO"=:B1)
复制

emp表上里20000个员工分布在6个部门。我在第10章节里使用这个查询的变体，并没有像这段SQL一样将结果集简化为带有简单count(*)的内联no_merge()视图，但是查询的关键特性没有改变。问题是相关联的子查询到底执行了多少次？原则上，是“6(部门数量)到20000(员工数量)中任意你想要的数字”。看Cost列，Oracle会基于自己的算法模型期望执行6次子查询：245=35（第4行操作）+6*35(第6行操作)。

所以当你增加一些新的部门，开除一些员工，雇佣一些新员工，调遣一些员工到其他部门，会有什么事情发生？对于数据量大小来说没什么变化，但是对查询性能来说变化很大。最开始这个查询在0.35s内完成，当改变一些员工的dept_no值时，运行时间爆增至3分钟，都消耗在CPU上。下面是改变后的查询的数据源执行统计信息。

----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:03:09.75 |    1467K|
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:03:09.75 |    1467K|
|   2 |   VIEW                |      |      1 |    167 |   9997 |00:03:09.71 |    1467K|
|*  3 |    FILTER             |      |      1 |        |   9997 |00:03:09.63 |    1467K|
|   4 |     TABLE ACCESS FULL | EMP  |      1 |  20000 |  20000 |00:00:00.08 |     220 |
|   5 |     SORT AGGREGATE    |      |   6671 |      1 |   6671 |00:03:09.31 |    1467K|
|*  6 |      TABLE ACCESS FULL| EMP  |   6671 |   3333 |     22M|00:01:38.37 |    1467K|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("OUTER"."SAL">)
   6 - filter("INNER"."DEPT_NO"=:B1)
复制

子句实际执行了6671次而不是6次，因为执行引擎不能存储所有需要的子句结果缓存。这个例子中的查询是人为构造的，用来演示标量子查询的一些问题，正确的方法是阻止子查询展开，但是实际环境中这类问题也会发生，幸运的是，我们可以知道哪里发生了问题，也可以及时重写这些查询。

相同的问题也发生在select列中的标量子查询。如果你select列中有许多标量子查询，同时整个查询花费了很长的时间，你需要怎么找到原因？这个时候数据源执行统计信息就能帮上大忙。基于相同的表，下面是一个查询和带有执行信息的执行计划：

select
        dept_no,
        emp_no,
        (select sum(sal) from emp e2 where e2.dept_no = e1.dept_no) sal_tot
from
        emp e1
order by
        dept_no, emp_no
;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  20000 |00:00:04.71 |     731K|       |       |          |
|   1 |  SORT AGGREGATE    |      |   3338 |      1 |   3338 |00:00:04.59 |     731K|       |       |          |
|*  2 |   TABLE ACCESS FULL| EMP  |   3338 |   3333 |     11M|00:01:06.77 |     731K|       |       |          |
|   3 |  SORT ORDER BY     |      |      1 |  20000 |  20000 |00:00:04.71 |     731K|   832K|   511K|  739K (0)|
|   4 |   TABLE ACCESS FULL| EMP  |      1 |  20000 |  20000 |00:00:00.08 |     219 |       |       |          |
----------------------------------------------------------------------------------------------------------------
复制

部门和员工不正确的组合会导致内联标量子查询执行3338次而不是每个部门1次，这也是一个线索，查询应该在hash join中使用了非合并聚合视图。

Summary

我们看到了数据源执行统计信息会告诉你一个操作会执行多少次，它总共做了多少的工作，以及优化器预估返回的行数跟真实返回行数的差距，特别需要记住我们需要用E-rows * Starts来跟A-Rows进行比对，同样也认识到，”局部”的预测准确并不代表“整体”的准确。

不过，有两个地方可以将全局思想应用到数据源执行统计信息。第一个是找到最早的操作中，预估和实际执行的行数有很大误差的地方，这有可能会导致后面低效的连接方式。第二个地方是实际上花费在标量子查询上的工作量远比预测的来的大的情况，如果没有数据源执行统计信息，那么很有可能就不知道到底表量子查询被执行了多少次。

| 译者简介

执行计划-12：基数反馈

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

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

评论

相关阅读