Oracle 19c中基于递归CTE视图创建新视图导致会话崩溃求助

场景概述 #

在Oracle 19c环境下，创建或查询基于递归CTE的视图时，会触发会话进程崩溃，SQL Developer中提示No more data to read from socket并终止会话。具体情况如下：

• 核心表DATA（含PROJECT_ID、DATA_IDENTITY等字段）直接查询正常；
• 已创建的递归CTE视图ELEMENTS_BY_PROJECT_V单独查询无问题；
• 但将ELEMENTS_BY_PROJECT_V作为另一递归CTE视图HIERARCHY_BY_ELEMENT_V的初始数据源时，会话崩溃；
• 若直接从DATA表获取初始数据，CTE执行正常；
• 尝试将两个CTE逻辑合并为单个视图，仍出现崩溃；
• 相同逻辑在Oracle 21c环境中无异常，推测为19c版本的环境/BUG问题。

相关SQL示例 #

已正常运行的视图ELEMENTS_BY_PROJECT_V #

CREATE OR REPLACE VIEW ELEMENTS_BY_PROJECT_V AS
WITH 
    HISTORY(PROJECT_ID, COMMIT_ID, PREVIOUS_ID, LVL) AS (...),
    ELEMENT_DATA(PROJECT_ID, COMMIT_ID, DATA_IDENTITY, E_DATA, LVL) AS (...),
    LATEST_VERSIONS(LVL, DATA_IDENTITY_ID) AS (...)
SELECT D.PROJECT_ID, D.COMMIT_ID, D.DATA_IDENTITY, D.E_DATA 
FROM LATEST_VERSIONS V, ELEMENT_DATA D 
WHERE V.LVL=D.LVL AND V.DATA_IDENTITY=D.DATA_IDENTITY;

触发崩溃的视图HIERARCHY_BY_ELEMENT_V #

CREATE OR REPLACE VIEW HIERARCHY_BY_ELEMENT_V AS
WITH
    ROOTS(PROJECT_ID, ELEMENT_ID) AS (
     -- SELECT PROJECT_ID, DATA_IDENTITY FROM ELEMENTS_BY_PROJECT_V -- 执行此语句导致崩溃
     -- SELECT PROJECT_ID, DATA_IDENTITY FROM DATA                  -- 执行此语句正常
    ),
    HIERARCHY(ROOT_PROJECT_ID, ROOT_ID, ELEMENT_ID, LVL) AS (...),
    ELEMENT_DATA(ELEMENT_ID, NAME, TYPE) AS (...),
    IN_PACKAGES(ROOT_PROJECT_ID, ROOT_ID, PACKAGE_NAMES, PACKAGE_IDS) AS (...)
SELECT * FROM IN_PACKAGES WHERE IN_PACKAGES.PROJECT_ID='123' AND IN_PACKAGES.ROOT_ID='abc';

合并CTE后仍崩溃的视图COMBINED_V #

CREATE OR REPLACE VIEW COMBINED_V AS
WITH 
    HISTORY(PROJECT_ID, COMMIT_ID, PREVIOUS_ID, LVL) AS (...),
    ELEMENT_DATA(PROJECT_ID, COMMIT_ID, DATA_IDENTITY, E_DATA, LVL) AS (...),
    LATEST_VERSIONS(LVL, DATA_IDENTITY_ID) AS (...),
    ROOTS(PROJECT_ID, ELEMENT_ID) AS (
      SELECT D.PROJECT_ID, D.DATA_IDENTITY FROM LATEST_VERSIONS V, ELEMENT_DATA D WHERE V.LVL=D.LVL AND V.DATA_IDENTITY=D.DATA_IDENTITY
    ),
    HIERARCHY(ROOT_PROJECT_ID, ROOT_ID, ELEMENT_ID, LVL) AS (...),
    ELEMENT_DATA2(ELEMENT_ID, NAME, TYPE) AS (...),
    IN_PACKAGES(ROOT_PROJECT_ID, ROOT_ID, PACKAGE_NAMES, PACKAGE_IDS) AS (...)
SELECT * FROM IN_PACKAGES WHERE IN_PACKAGES.PROJECT_ID='123' AND IN_PACKAGES.ROOT_ID='abc';

关键现象总结 #

• 调用递归CTE视图作为另一递归CTE的数据源时，触发会话崩溃；
• 直接使用基础表作为数据源则无异常；
• 合并CTE逻辑无法规避问题；
• 版本差异（19c vs 21c）验证为版本特有问题。

解决建议 #

1. 应用Oracle 19c最新补丁 #

此类会话崩溃多为19c版本中递归CTE解析相关的已知BUG，建议查询Oracle官方BUG库（如BUG 31741647、BUG 32364598等），并为数据库安装对应版本的最新Release Update（RU）补丁，修复底层解析逻辑缺陷。

2. 用物化视图替代普通视图 #

将ELEMENTS_BY_PROJECT_V的逻辑转换为物化视图，预先计算并存储结果，避免递归CTE的嵌套解析触发BUG：

CREATE MATERIALIZED VIEW ELEMENTS_BY_PROJECT_MV
REFRESH FAST ON COMMIT
AS
WITH 
    HISTORY(PROJECT_ID, COMMIT_ID, PREVIOUS_ID, LVL) AS (...),
    ELEMENT_DATA(PROJECT_ID, COMMIT_ID, DATA_IDENTITY, E_DATA, LVL) AS (...),
    LATEST_VERSIONS(LVL, DATA_IDENTITY_ID) AS (...)
SELECT D.PROJECT_ID, D.COMMIT_ID, D.DATA_IDENTITY, D.E_DATA 
FROM LATEST_VERSIONS V, ELEMENT_DATA D 
WHERE V.LVL=D.LVL AND V.DATA_IDENTITY=D.DATA_IDENTITY;

之后在HIERARCHY_BY_ELEMENT_V中使用该物化视图作为数据源。

3. 临时调整优化器参数 #

通过会话级参数禁用可能触发BUG的优化特性：

• 回退优化器特性版本：

  ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='19.1.0.0.0';
  

• 关闭递归CTE特定优化：

  ALTER SESSION SET "_optimizer_recursive_cte_optimization"=FALSE;
  

4. 拆解递归逻辑 #

将嵌套的递归CTE拆分为多个独立的普通视图，减少递归层级，降低触发BUG的概率。

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