Oracle 为什么优化器选择一个包括合并加入笛卡尔的计划？

嗨，康纳，
再次感谢您和克里斯为我们所做的所有出色工作。
我们有时会为优化器的决策而苦苦挣扎。在以下情况下，我将问题缩小到这个子查询:

SELECT i.hw_oid32,
       hw_oid_lo,
       i.hw_version,
       i.hw_category,
       i.hw_score,
       i.hw_source,
       p.hw_user_or_group,
       p.hw_type,
       i.hw_iterator_index
FROM   hw_iterators i
       inner join hw_permissions p USING (hw_oid_lo)
       inner join hw_effective_groups g
               ON ( p.hw_user_or_group = g.hw_groupname
                    AND p.hw_type = g.hw_type )
WHERE  i.hw_iterator_id = :p_iterator_id
       AND i.hw_iterator_id_internal = :v_int_id
       AND g.hw_session_id = :p_we_session_id 
复制

set autotrace traceonly显示以下执行计划:

Execution Plan
----------------------------------------------------------
Plan hash value: 570251651

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |     1 |   422 |    37   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                          |                     |     1 |   422 |    37   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN                 |                     |     1 |   401 |    36   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | HW_EFFECTIVE_GROUPS |     1 |   284 |    34   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                   | HW_EGRP_IND1        |     1 |       |     1   (0)| 00:00:01 |
|   5 |    BUFFER SORT                         |                     |     1 |   117 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| HW_ITERATORS        |     1 |   117 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | HW_ITER_IND1        |     1 |       |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN                    | HW_PER_PK           |     1 |    21 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("G"."HW_SESSION_ID"=TO_NUMBER(:P_WE_SESSION_ID))
   7 - access("I"."HW_ITERATOR_ID"=TO_NUMBER(:P_ITERATOR_ID) AND
              "I"."HW_ITERATOR_ID_INTERNAL"=TO_NUMBER(:V_INT_ID))
   8 - access("I"."HW_OID_LO"="P"."HW_OID_LO" AND "P"."HW_USER_OR_GROUP"="G"."HW_GROUPNAME" AND
              "P"."HW_TYPE"="G"."HW_TYPE")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
        258  recursive calls
          0  db block gets
    1792277  consistent gets
     550937  physical reads
        160  redo size
     247537  bytes sent via SQL*Net to client
       5040  bytes received via SQL*Net from client
        405  SQL*Net roundtrips to/from client
         27  sorts (memory)
          0  sorts (disk)
       6050  rows processed

复制

问题是优化器决定从hw_effective_groups表开始，然后尝试将其与hw_iterators表连接，但是没有连接谓词!另一方面，如果我指定一个前导 (I) 提示执行计划看起来像这样:

Execution Plan
----------------------------------------------------------
Plan hash value: 3948157368

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |     1 |   422 |    39   (0)| 00:00:01 |
|*  1 |  HASH JOIN                            |                     |     1 |   422 |    39   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                     |     4 |   552 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| HW_ITERATORS        |     1 |   117 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | HW_ITER_IND1        |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | HW_PER_PK           |     3 |    63 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED | HW_EFFECTIVE_GROUPS |     1 |   284 |    34   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                   | HW_EGRP_IND1        |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P"."HW_USER_OR_GROUP"="G"."HW_GROUPNAME" AND "P"."HW_TYPE"="G"."HW_TYPE")
   4 - access("I"."HW_ITERATOR_ID"=TO_NUMBER(:P_ITERATOR_ID) AND
              "I"."HW_ITERATOR_ID_INTERNAL"=TO_NUMBER(:V_INT_ID))
   5 - access("I"."HW_OID_LO"="P"."HW_OID_LO")
   7 - access("G"."HW_SESSION_ID"=TO_NUMBER(:P_WE_SESSION_ID))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
        105  recursive calls
          0  db block gets
       6751  consistent gets
       2101  physical reads
        140  redo size
     275473  bytes sent via SQL*Net to client
       5040  bytes received via SQL*Net from client
        405  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       6050  rows processed

复制

有关所涉及表的一些信息: hw_permissions是一个相当大的表 (IOT)，具有大约900万行的当前统计信息。另一方面，hw_iterators和hw_effective_groups是正常 (堆) 表，但是由于它们非常不稳定，并且统计信息总是错误的，因此我决定删除并锁定表统计信息。这样，我希望优化器必须使用动态采样 (这显然是这样)。在我的测试用例中，hw_iterators表中的行数约为10000，其中三分之一与: p_iterator_id/:v_int_id匹配。hw_effective_groups表中的行数略高于400，381匹配: p_we_session_id。
尽管测试用例在原本空闲的系统上运行，但它似乎是可重现的。hw_iterators表中的行数变化相当快，但总是 (很多) 小于hw_permissions中的行数。hw_effective_groups也是如此，它可能会增长到几十万，但此查询中的子集 (:p_we_session_id) 通常会小于1000。
所以我的问题是，为什么优化器会选择两个表的连接，没有常见的连接谓词？
顺便说一句，这是在运行的测试数据库上发生的:
Oracle数据库12c标准版版本12.2.0.1.0-64位生产
表和索引的create语句位于livesql上，还包含一些用于加载表的语句。由于配额限制，我无法在livesql上运行它。

优化器认为该查询将仅从HW_EFFECTIVE_GROUPS (第一个计划中的第3行和第4行的rows列) 返回1行。

正如文档所说，如果:

A Cartesian join is an efficient method.

For example, the optimizer may decide to generate a Cartesian product of two very small tables that are both joined to the same large table.


https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/joins.html#GUID-11E7100E-1316-4963-83C5-A85940BE9BB6

如果优化器认为它将从表中获得 (最多) 一行，那么这很快-只需将其与其他表中的每一行组合即可。这只有真正的工作，如果你只从第一个表得到一行虽然!

还要记住-您正在与explain计划。这些是关于什么的预测might发生。为了看看到底发生了什么，你想要execution计划。

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

使用您的实时SQL示例得到这个，我得到这个计划:

--------------------------------------------------------------------------------------------------------------    
| Id  | Operation                              | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |    
--------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                       |                     |      1 |        |   131 (100)|   3999 |    
|   1 |  NESTED LOOPS                          |                     |      1 |   2014K|   131  (87)|   3999 |    
|   2 |   MERGE JOIN CARTESIAN                 |                     |      1 |   2014K|    18   (0)|   2015K|    
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | HW_ITERATORS        |      1 |   3997 |    18   (0)|   3999 |    
|*  4 |     INDEX RANGE SCAN                   | HW_ITER_IND1        |      1 |   3997 |    18   (0)|   3999 |    
|   5 |    BUFFER SORT                         |                     |   3999 |    504 |     0   (0)|   2015K|    
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| HW_EFFECTIVE_GROUPS |      1 |    504 |     0   (0)|    504 |    
|*  7 |      INDEX RANGE SCAN                  | HW_EGRP_IND1        |      1 |    504 |     0   (0)|    504 |    
|*  8 |   INDEX UNIQUE SCAN                    | HW_PER_PK           |   2015K|      1 |     0   (0)|   3999 |    
-------------------------------------------------------------------------------------------------------------- 
复制

HW_ITERATORS的行估计现在要高得多 (〜4k)，并且非常准确。但是它仍然在选择合并加入!

我不确定为什么会这样-这对我来说似乎是一个成本错误。请注意，第5-8行的成本为零。我认为这与缺少的统计数据有关。

在收集所有表格的统计数据后，我得到了这个看起来更合理的计划:

------------------------------------------------------------------------------------------------------------    
| Id  | Operation                            | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |    
------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                     |                     |      1 |        |  8027 (100)|   3999 |    
|*  1 |  HASH JOIN                           |                     |      1 |    501K|  8027   (1)|   3999 |    
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| HW_EFFECTIVE_GROUPS |      1 |    504 |     5   (0)|    504 |    
|*  3 |    INDEX RANGE SCAN                  | HW_EGRP_IND1        |      1 |    504 |     2   (0)|    504 |    
|   4 |   NESTED LOOPS                       |                     |      1 |    533K|  8020   (1)|    535K|    
|*  5 |    TABLE ACCESS FULL                 | HW_ITERATORS        |      1 |   3998 |    19   (0)|   3999 |    
|*  6 |    INDEX RANGE SCAN                  | HW_PER_PK           |   3999 |    133 |     2   (0)|    535K|    
------------------------------------------------------------------------------------------------------------
复制

因此，而不是完全删除统计信息，然后锁定它们，我建议在易失性表上收集统计信息，当它们有代表性的数据时 (最好你能得到)。

Remember the goal isn't to have perfect stats, just stats good enough for the optimizer to choose a good计划。

您仍然可以通过在会话中设置OPTIMIZER_DYNAMIC_SAMPLING或使用dynamic_sampling提示来使用动态采样 (现在称为动态统计信息)。