sql - v$sql_plan_monitor - JOIN 估计非常不准确？

Question

当涉及到 v$sql_plan_monitor 时，我遇到了一些奇怪的现象。 Oracle 11.2 上的表。

我有两张大小合适的 table 。一个有大约 2500 万行，另一个大约有 3500 万行，两者都是约 99% 的唯一性，只有少量的重复记录。

解释计划如下(表名代替隐私，表在解释计划之前收集了统计信息):

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      |       |       | 65611 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE            |                      |     1 |    34 |            |          |        |      |            |
|   2 |   PX COORDINATOR           |                      |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10002             |     1 |    34 |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE         |                      |     1 |    34 |            |          |  Q1,02 | PCWP |            |
|*  5 |      FILTER                |                      |       |       |            |          |  Q1,02 | PCWC |            |
|*  6 |       HASH JOIN OUTER      |                      |   234K|  7770K| 65611   (1)| 00:19:41 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE          |                      |    23M|   513M| 26409   (1)| 00:07:56 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10000             |    23M|   513M| 26409   (1)| 00:07:56 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |                      |    23M|   513M| 26409   (1)| 00:07:56 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| PRETTY_BIG_TABLE     |    23M|   513M| 26409   (1)| 00:07:56 |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE          |                      |    36M|   384M| 39164   (1)| 00:11:45 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10001             |    36M|   384M| 39164   (1)| 00:11:45 |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |                      |    36M|   384M| 39164   (1)| 00:11:45 |  Q1,01 | PCWC |            |
|* 14 |           TABLE ACCESS FULL| EVEN_BIGGER_TABLE    |    36M|   384M| 39164   (1)| 00:11:45 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------

让我有些悲伤的数字是 Rows HASH JOIN OUTER 的值步。
Oracle 估计它将输出大约 234k 行，这是一个相对较小的数量。我知道一个事实，在过滤*结果后，查询将返回大约 50k 行，因为它之前使用相同的数据运行以进行测试。

*:实际查询本身是一个使用 LEFT JOIN 的反连接和一个 WHERE过滤空记录。

但是，一旦查询运行，我就会检查它的 sql_id在 v$sql_plan_monitor table :

  1  SELECT
  2      plan_line_id,
  3      plan_operation,
  4      ROUND(MAX(plan_cardinality) / 1000) AS est_krows,
  5      ROUND(SUM(output_rows) / 1000) AS actual_krows
  6  FROM v$sql_plan_monitor
  7  WHERE sql_id = 'sql_id_goes_here'
  8  GROUP BY sql_id, sql_exec_id, sql_exec_start, plan_line_id, plan_operation
  9* ORDER BY sql_exec_id, plan_line_id
SQL> /

PLAN_LINE_ID PLAN_OPERATION                  EST_KROWS ACTUAL_KROWS
------------ ------------------------------ ---------- ------------
           0 SELECT STATEMENT                                     0
           1 SORT                                    0            0
           2 PX COORDINATOR                                       0
           3 PX SEND                                 0            0
           4 SORT                                    0            0
           5 FILTER                                               0
           6 HASH JOIN                             234     23084866
           7 PX RECEIVE                          23402        23168
           8 PX SEND                             23402        23168
           9 PX BLOCK                            23402        23168
          10 TABLE ACCESS                        23402        23168
          11 PX RECEIVE                          36699        17772
          12 PX SEND                             36699        17748
          13 PX BLOCK                            36699        17748
          14 TABLE ACCESS                        36699        17748

请注意，查询仍在进行中，所以 actual_krows值(value)正在增长。

所以我的问题是:

为什么来自 HASH JOIN 的实际行数减少了五个数量级？

我该怎么做才能更准确地估计或更准确地读取实际行输出进度？它似乎只给我在 JOIN 上的可怕结果，没有别的。

Answer 1

为什么估计是错误的？

因为，理论上，it is impossible to predict if a program will ever finish ，更不用说预测需要多长时间了。而且，实际上，估计是困难的，Oracle 只有 satisficing 的时间。 ; Oracle 不知道查询是每天提交一次还是每秒提交一千次，因此无法花费大量时间来决定。

我们如何改进估计？

查看整个查询并了解有关表结构和数据分布的一些信息可能会有所帮助。这是很多信息，不能保证它会有所帮助。相反，这里有一堆可能对调整基数有用的方法。根据您的查询、 session 、环境等，并非所有这些都会有所帮助。

ASSOCIATE STATISTICS 估计声明性代码已经够难了，当使用过程代码时，Oracle 不会费力去尝试。如果有自定义函数，默认估计会很差。但是您可以指定自定义选择性来更改估计值。在极少数情况下，可能值得用具有关联统计信息的函数替换复杂的 SQL 表达式。

虚假统计 DBMS_STATS.SET_COLUMN_STATS和其他功能允许您更改估计算法的输入。但请注意，您对这一查询的修复不会破坏其他具有完全合理估计的查询。

Extended statistics 正如 ibre5041 所暗示的，列组或表达式可能难以估计。相反，您可以让 Oracle 收集有关这些组和表达式的统计信息。然后，当它们在查询中使用时，估计可能会好得多。

重写条件 某些类型的表达式比其他类型更难估计。如果可能，尝试重构你的表达式。比如一些复杂的NVL表达式有时可以用 OR 写得更好.

SQL Profiles “SQL 配置文件是包含辅助数据的数据库对象
特定于 SQL 语句的统计信息。”例如，表统计信息可能仅暗示 10% 的行连接，并且配置文件可能会说“将其乘以 1000”。

未记录的提示 OPT_ESTIMATE和 CARDINALITY提示可以帮助弥补错误的估计。 OPT_ESTIMATE是 SQL 配置文件使用的内容，并且是说“嘿，将基数增加 1000%”的好方法。 CARDINALITY是说“整个查询将返回 X 行”的简单方法。但是这些提示很难使用。

动态采样像 `/*+ dynamic_sampling(4) */这样的提示是一种告诉优化器“这是一个昂贵的查询，花时间读取现有数据，尝试并调整数字”的方式。至少，这是理论。在实践中，它并不总是很有帮助。

基数反馈 运行该语句两次，如果基数严重错误，Oracle 可能会第二次修复它。

自适应查询优化 12c 引入了一个功能，执行计划偶尔会检查行数，如果估计错误，则会自行修复。这并不能解决根本原因。而且您还无法使用它。但这听起来很酷，可能是开始考虑升级的一个很好的理由。

我们甚至需要修正估计吗？

关注基数是明智的。错误的基数估计会导致许多性能问题。但在许多情况下，基数可能会出现数量级的错误，这无关紧要。

我没有看到执行计划有任何明显的问题。两个大表的访问方式正确(如果大部分行都用全表扫描更好)，join方式好(hash join对多行最好)，join顺序好(大表hashed) (即第一个表)，较大的表被探测(即第二个表))，并行性好(每一步都使用并行性，没有大行源的广播等)。

如果那个执行计划是整个故事，我会称之为成功。

有时减少 5 个数量级并不重要，尤其是当错误接近执行计划的结尾时。 234K 是一个足够大的数字，可以阻止很多错误，比如错误的交叉连接。

但是，如果这只是较大查询或 View 的一部分，则由此产生的基数可能会影响其他执行计划。