Oracle 19c多递归CTE语句执行过慢问题排查与优化咨询

问题背景 #

使用Oracle 19c数据库，RESOURCES表为嵌套文件夹层级结构，包含约250万行数据，层级最深达10层，表结构及索引定义如下：

create table RESOURCES (
    ID_       NUMBER(10) not null constraint "PK_RESOURCES" primary key,
    FOLDERID_ NUMBER(10) constraint "FK_PARENTFOLDER" references RESOURCES
);

create index FOLDERIDINDEX on RESOURCES (FOLDERID_);

通过递归CTE查询指定资源的所有后代时，单分支场景性能正常，但多分支合并查询时出现严重性能问题：

• 慢查询（60分钟未返回）：

WITH cte1 (id_) AS (SELECT id_ FROM Resources where id_ = 11
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
     cte2 (id_) AS (SELECT id_ FROM Resources where id_ = 808965
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1) OR r.folderId_ IN (SELECT * FROM cte2));

• 优化后查询（几秒完成）：将两个IN子查询合并为UNION

WITH cte1 (id_) AS (SELECT id_ FROM Resources where id_ = 11
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
     cte2 (id_) AS (SELECT id_ FROM Resources where id_ = 808965
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1 UNION SELECT * FROM cte2));

由于SQL由应用自动生成，修改合并逻辑难度大，需分析问题原因并寻找其他优化方案。该问题仅在Oracle 19c中出现，MySQL 8、PostgreSQL 13、SQL Server 2016均无异常。

性能差异原因分析 #

对比两个查询的执行计划，核心差异在于Oracle对OR条件的处理逻辑：

慢查询执行计划 #

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |              |     1 |     5 |    54G  (1)|591:38:12 |
|   1 |  SORT AGGREGATE                             |              |     1 |     5 |            |          |
|*  2 |   FILTER                                    |              |       |       |            |          |
|   3 |    TABLE ACCESS FULL                        | RESOURCES    |  2410K|    11M|  9389   (1)| 00:00:01 |
|*  4 |    VIEW                                     |              |   239K|  3046K| 23128   (1)| 00:00:01 |
|   5 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|              |       |       |            |          |
|*  6 |      INDEX UNIQUE SCAN                      | PK_RESOURCES |     1 |     6 |     2   (0)| 00:00:01 |
|*  7 |      HASH JOIN                              |              |   239K|  5623K| 23126   (1)| 00:00:01 |
|   8 |       RECURSIVE WITH PUMP                   |              |       |       |            |          |
|   9 |       BUFFER SORT (REUSE)                   |              |       |       |            |          |
|  10 |        TABLE ACCESS FULL                    | RESOURCES    |  2410K|    25M|  9386   (1)| 00:00:01 |
|* 11 |    VIEW                                     |              |   239K|  3046K| 23128   (1)| 00:00:01 |
|  12 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|              |       |       |            |          |
|* 13 |      INDEX UNIQUE SCAN                      | PK_RESOURCES |     1 |     6 |     2   (0)| 00:00:01 |
|* 14 |      HASH JOIN                              |              |   239K|  5623K| 23126   (1)| 00:00:01 |
|  15 |       RECURSIVE WITH PUMP                   |              |       |       |            |          |
|  16 |       BUFFER SORT (REUSE)                   |              |       |       |            |          |
|  17 |        TABLE ACCESS FULL                    | RESOURCES    |  2410K|    25M|  9386   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - filter( EXISTS (SELECT 0 FROM "CTE1" "CTE1" WHERE "CTE1"."ID_"=:B1) OR  EXISTS (SELECT 0 "
"              FROM "CTE2" "CTE2" WHERE "CTE2"."ID_"=:B2))"
"   4 - filter("CTE1"."ID_"=:B1)"
"   6 - access("ID_"=11)"
"   7 - access("R"."FOLDERID_"="C"."ID_")"
"  11 - filter("CTE2"."ID_"=:B1)"
"  13 - access("ID_"=808965)"
"  14 - access("R"."FOLDERID_"="C"."ID_")"

Oracle选择了FILTER操作：对RESOURCES表的每一行（约2410万行），分别执行两次EXISTS检查（匹配cte1或cte2）。由于递归CTE未被物化，每行触发的子查询都会重新计算递归逻辑，导致总计算量呈指数级增长，成本估算达54G，执行时间极长。

优化后查询执行计划 #

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |               |     1 |    18 |       | 55733   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE                                |               |     1 |    18 |       |            |          |
|*  2 |   HASH JOIN                                    |               |  2806K|    48M|    11M| 55733   (1)| 00:00:03 |
|   3 |    VIEW                                        | VW_NSO_1      |   479K|  6092K|       | 50820   (1)| 00:00:02 |
|   4 |     SORT UNIQUE                                |               |   479K|  6092K|  9424K| 50820   (1)| 00:00:02 |
|   5 |      UNION-ALL                                 |               |       |       |       |            |          |
|   6 |       VIEW                                     |               |   239K|  3046K|       | 23128   (1)| 00:00:01 |
|   7 |        UNION ALL (RECURSIVE WITH) BREADTH FIRST|               |       |       |       |            |          |
|*  8 |         INDEX UNIQUE SCAN                      | PK_RESOURCES  |     1 |     6 |       |     2   (0)| 00:00:01 |
|*  9 |         HASH JOIN                              |               |   239K|  5623K|       | 23126   (1)| 00:00:01 |
|  10 |          RECURSIVE WITH PUMP                   |               |       |       |       |            |          |
|  11 |          BUFFER SORT (REUSE)                   |               |       |       |       |            |          |
|  12 |           TABLE ACCESS FULL                    | RESOURCES     |  2410K|    25M|       |  9386   (1)| 00:00:01 |
|  13 |       VIEW                                     |               |   239K|  3046K|       | 23128   (1)| 00:00:01 |
|  14 |        UNION ALL (RECURSIVE WITH) BREADTH FIRST|               |       |       |       |            |          |
|* 15 |         INDEX UNIQUE SCAN                      | PK_RESOURCES  |     1 |     6 |       |     2   (0)| 00:00:01 |
|* 16 |         HASH JOIN                              |               |   239K|  5623K|       | 23126   (1)| 00:00:01 |
|  17 |          RECURSIVE WITH PUMP                   |               |       |       |       |            |          |
|  18 |          BUFFER SORT (REUSE)                   |               |       |       |       |            |          |
|  19 |           TABLE ACCESS FULL                    | RESOURCES     |  2410K|    25M|       |  9386   (1)| 00:00:01 |
|  20 |    INDEX FAST FULL SCAN                        | FOLDERIDINDEX |  2410K|    11M|       |  2392   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - access("R"."FOLDERID_"="ID_")"
"   8 - access("ID_"=11)"
"   9 - access("R"."FOLDERID_"="C"."ID_")"
"  15 - access("ID_"=808965)"
"  16 - access("R"."FOLDERID_"="C"."ID_")"

Oracle先合并两个CTE的结果（UNION-ALL + SORT UNIQUE），再通过HASH JOIN与RESOURCES表的FOLDERIDINDEX索引快速扫描结果关联，仅需一次合并和关联操作，计算量大幅降低，执行时间缩短至秒级。

优化方案 #

1. 递归CTE物化提示 #

在递归CTE定义中添加/*+ MATERIALIZE */提示，强制Oracle将CTE结果存储到临时表，避免每行重复计算递归逻辑：

WITH cte1 (id_) AS (SELECT /*+ MATERIALIZE */ id_ FROM Resources where id_ = 11
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
     cte2 (id_) AS (SELECT /*+ MATERIALIZE */ id_ FROM Resources where id_ = 808965
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1) OR r.folderId_ IN (SELECT * FROM cte2));

2. 合并递归CTE分支 #

将多个起始节点合并为一个递归CTE，减少分支数量：

WITH cte (id_) AS (SELECT id_ FROM Resources where id_ IN (11, 808965)
                    UNION ALL
                    SELECT r.id_ FROM Resources r JOIN cte c ON r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE r.folderId_ IN (SELECT * FROM cte);

此方案仅需一次递归计算，性能与UNION版本相当，若应用生成逻辑可调整，优先采用。

3. 应用Oracle补丁 #

检查Oracle 19c的Release Update（RU）补丁，部分版本存在递归CTE在OR条件下的优化bug，例如Bug 30659169（递归子查询因子与OR条件导致性能低下），升级至包含该补丁的RU版本可解决问题。

4. 强制关联方式提示 #

在主查询中添加/*+ USE_HASH(r cte1 cte2) */或/*+ MERGE(cte1 cte2) */提示，引导Oracle选择关联而非FILTER操作：

WITH cte1 (id_) AS (SELECT id_ FROM Resources where id_ = 11
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
     cte2 (id_) AS (SELECT id_ FROM Resources where id_ = 808965
                    UNION ALL
                    SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT /*+ USE_HASH(r cte1 cte2) */ count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1) OR r.folderId_ IN (SELECT * FROM cte2));

内容的提问来源于stack exchange，提问作者ahu