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

关于Oracle 11.2上的 v$sql_plan_monitor 表，我遇到了一个奇怪的现象。

我有两个尺寸合适的 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估计它将输出大约23.4万行，数量相对较少。我知道一个事实，查询将过滤*结果后返回大约5万行，因为它之前已经运行了相同的数据用于测试。

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

但是，查询运行后，将在sql_id表中检查其v$sql_plan_monitor:

  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值正在增长。

所以我的问题是:

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

我应该怎么做才能对实际的行输出进度获得更准确的估计或更准确的读数？它似乎只给我关于JOIN的可怕结果，仅此而已。

最佳答案

为什么估算错误？

因为从理论上来说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方法很好(散列联接对于许多行来说最好)，联接顺序很好(对大表进行哈希处理) (即第一个表)，较大的表(即第二个表)被探测到，并且并行性很好(在每个步骤中使用并行性，没有广播巨大的行源，等等)。

如果说执行计划是全部，那么我会称之为成功。

有时5个数量级的差异并不重要，尤其是当错误接近执行计划的末尾时。 234K足够大，可以阻止很多错误，例如不良的交叉连接。

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

关于sql - v $ sql_plan_monitor-JOIN估计非常不准确？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27431455/