哈希联接的 Oracle 查询性能问题-解网

问：

我正在使用 oracle 19c，并在一个简单的查询中遇到了一个奇怪的性能问题。这是我的查询：

SELECT 
TU.ID AS RecordID
FROM TABLE_U TU
JOIN (SELECT to_date('11-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
UNION ALL SELECT to_date('10-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
)  PDATE ON (1=1)
JOIN TABLE_T t ON (t.PROCESSOR = TU.PROCESSOR AND TU.TTD = t.ID AND t.FROMDATE <=PDATE.PROCESS_DATE AND t.TODATE >= PDATE.PROCESS_DATE)
LEFT JOIN TABLE_TD TD ON (TD.PROCESSOR = t.PROCESSOR AND TD.ABC = t.ID)

WHERE t.CREATED <=PDATE.PROCESS_DATE
AND TU.FROMDATE <= PDATE.PROCESS_DATE

这是它的执行计划

Plan hash value: 1872580574
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                 |  3745 |   369K| 35213   (1)| 00:00:02 |
|*  1 |  HASH JOIN OUTER                        |                                 |  3745 |   369K| 35213   (1)| 00:00:02 |
|   2 |   NESTED LOOPS                          |                                 |  3745 |   303K| 34957   (1)| 00:00:02 |
|   3 |    NESTED LOOPS                         |                                 | 77970 |   303K| 34957   (1)| 00:00:02 |
|   4 |     NESTED LOOPS                        |                                 |  7797 |   388K|  3853   (1)| 00:00:01 |
|   5 |      VIEW                               |                                 |     2 |    18 |     4   (0)| 00:00:01 |
|   6 |       UNION-ALL                         |                                 |       |       |            |          |
|   7 |        FAST DUAL                        |                                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |        FAST DUAL                        |                                 |     1 |       |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_T                         |  3899 |   159K|  1924   (1)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN                  | TABLE_T_FROMDATE                |    23 |       |  1916   (1)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN                    | PK_TABLE_U                      |    10 |       |     3   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID          | TABLE_U                         |     1 |    32 |     4   (0)| 00:00:01 |
|  13 |   INDEX STORAGE FAST FULL SCAN          | TABLE_TD_IDX                    |   231K|  4066K|   256   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("TD"."PROCESSOR"(+)="T"."PROCESSOR" AND "TD"."ABC"(+)="T"."ID")
  10 - access("T"."TODATE">="PDATE"."PROCESS_DATE" AND "T"."FROMDATE"<="PDATE"."PROCESS_DATE" AND 
              "T"."CREATED"<="PDATE"."PROCESS_DATE")
       filter("T"."CREATED"<="PDATE"."PROCESS_DATE" AND "T"."TODATE">="PDATE"."PROCESS_DATE")
  11 - access("TU"."TTD"="T"."ID" AND "T"."PROCESSOR"="TU"."PROCESSOR")
       filter("T"."PROCESSOR"="TU"."PROCESSOR")
  12 - filter("TU"."FROMDATE"<="PDATE"."PROCESS_DATE")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   0 -  SEL$6
         E -  USEX_NL

（统计数据是最新的）我有两个问题：

查询需要 30 分钟以上才能执行
我不明白为什么 oracle 正在考虑加入 TABLE_TD 知道这是左JOI，也没有被起诉选定的列，也不在 where 条件中。

我强制了嵌套循环提示，如下所示：

SELECT /*+ USE_NL(t TD)*/
TU.ID AS RecordID
FROM TABLE_U TU
JOIN (SELECT to_date('11-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
UNION ALL SELECT to_date('10-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
)  PDATE ON (1=1)
JOIN TABLE_T t ON (t.PROCESSOR = TU.PROCESSOR AND TU.TTD = t.ID AND t.FROMDATE <=PDATE.PROCESS_DATE AND t.TODATE >= PDATE.PROCESS_DATE)
LEFT JOIN TABLE_TD TD ON (TD.PROCESSOR = t.PROCESSOR AND TD.ABC = t.ID)

WHERE t.CREATED <=PDATE.PROCESS_DATE
AND TU.FROMDATE <= PDATE.PROCESS_DATE

新计划更正确，查询执行时间不到 1 秒！

Plan hash value: 3532208304
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                 |  3745 |   369K| 42448   (1)| 00:00:02 |
|   1 |  NESTED LOOPS OUTER                    |                                 |  3745 |   369K| 42448   (1)| 00:00:02 |
|   2 |   NESTED LOOPS                         |                                 |  3745 |   303K| 34957   (1)| 00:00:02 |
|   3 |    NESTED LOOPS                        |                                 |  7797 |   388K|  3853   (1)| 00:00:01 |
|   4 |     VIEW                               |                                 |     2 |    18 |     4   (0)| 00:00:01 |
|   5 |      UNION-ALL                         |                                 |       |       |            |          |
|   6 |       FAST DUAL                        |                                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL                        |                                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_T                         |  3899 |   159K|  1924   (1)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                  | TABLE_T_FROMDATE                |    23 |       |  1916   (1)| 00:00:01 |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED | TABLE_U                         |     1 |    32 |     4   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN                   | PK_TABLE_U                      |    10 |       |     3   (0)| 00:00:01 |
|* 12 |   INDEX RANGE SCAN                     | TABLE_TD_IDX                    |     1 |    18 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - access("T"."TODATE">="PDATE"."PROCESS_DATE" AND "T"."FROMDATE"<="PDATE"."PROCESS_DATE" AND 
              "T"."CREATED"<="PDATE"."PROCESS_DATE")
       filter("T"."CREATED"<="PDATE"."PROCESS_DATE" AND "T"."TODATE">="PDATE"."PROCESS_DATE")
  10 - filter("TU"."FROMDATE"<="PDATE"."PROCESS_DATE")
  11 - access("TU"."TTD"="T"."ID" AND "T"."PROCESSOR"="TU"."PROCESSOR")
       filter("T"."PROCESSOR"="TU"."PROCESSOR")
  12 - access("TD"."ABC"(+)="T"."ID" AND "TD"."PROCESSOR"(+)="T"."PROCESSOR")

任何解释为什么预言机可以选择第一个执行计划以及如何在不强制提示的情况下解决这个问题？

Oracle 查询优化 SQLCreploy

答：

2赞 Paul W 6/18/2023 #1

您在不知不觉中使用它。只需加入它，如果它应该为每个键（）找到多于一行，您就可以将其他表的行数相乘。仅仅因为您没有在 SELECT 子句中应用过滤器或请求过滤器中的列，并不意味着 join 没有执行任何操作。Oracle 仍必须执行联接。TDTDprocessor,abc
Oracle 认为，在到达 TD 之前执行其他联接后，它将有 3,745 行。这已经超出了其成本数学的极限，可以预测哈希连接将比嵌套循环更便宜。如果你注意到你的提示版本，它计算的成本是 42K，而它的首选计划是 35K——数字非常接近，所以它接近拐点。微小的更改可能会导致它在这两个计划之间来回切换。这是 Oracle 的典型情况，随着数据随时间的变化，Oracle 总是试图选择它认为最好的计划。当然，它并不完美，并且经常在其计算中输入不正确的假设，从而导致不正确的成本核算，这意味着糟糕的计划。我们试图尽可能地给它提供最好的统计数据，如果我们仍然有问题，提示在修复问题和保持稳定方面会创造奇迹。话虽如此，不要以为甲骨文在这里是错的。
现在，我怀疑你没有正确地安排你的测试时间。1s 听起来像是在对出现的第一行（响应时间）进行计时，而不是在获取最后一行（吞吐量）之前进行计时。从哈希联接更改为嵌套循环，以便每个联接都是嵌套循环，绝对可以立即为您提供< 1s响应（第一行），因为这就是嵌套循环的工作方式 - 在下一行工作之前，您可以获得第一行，等等。如果它一直向下嵌套循环，您将立即在客户端中看到第一行。如果任何地方只有一个哈希联接，则必须等到所有行的哈希联接完成后，才能看到第一行。如果这是正在发生的事情，那么你需要改变你的测试。将查询包装在 a 中，以便在整个 SQL 中进行测试。我猜你会发现甲骨文的原始计划几乎和你的新计划一样，甚至稍微好一点。SELECT COUNT(*) FROM ([query])
要实际调整您的查询，您需要找出它在哪里花费时间。运行一段时间后，请查阅 ASH （）以获取 SQL 的 SQL，您可以从 .查看、并计算行数。例如：v$active_session_historysql_idv$sessionsql_plan_line_idsql_plan_operationevent
```
 SELECT sql_plan_line_id,sql_plan_operation,event,COUNT(*)
   FROM v$active_session_history
  WHERE sql_id = 'gdvh64xqz9wcm'
    AND sample_time > SYSDATE - 1/24
  GROUP BY sql_plan_line_id,sql_plan_operation,event
  ORDER BY COUNT(*) DESC
```
这将显示在哪条计划线和什么事件上花费了多少秒（）。这将告诉您问题出在查询中的位置。修复它取决于问题所在。但这应该让你开始。COUNT

0赞 Mark Stewart 7/20/2023

哇，这是一个巧妙的技巧，可以查看 ASH 以查看现实中时间花在哪里！

上一个：使用 SQL 索引的长值对性能的影响

下一个：如何提高 SQL 查询的性能？

哈希联接的 Oracle 查询性能问题

Oracle Query Performance Issue With Hash Join

评论

评论