Oracle 12.2中PGA内存操作等待的查询调优求助

针对680K行数据拆分时出现PGA内存等待、执行时长超数小时的问题，以下是几种针对性调优方法：

1. 修复CONNECT BY递归逻辑，消除无效笛卡尔积 #

原CONNECT BY语句未限定仅对当前行递归，导致数据库产生大量跨行关联计算。添加PRIOR条件和SYS_GUID()可强制单行递归：

SELECT REGEXP_SUBSTR(LWNEIGHBORREL_GNBID, '[^;]+', 1, LEVEL) AS GNBID
FROM ericssonlw."NRCellCU"
WHERE loaddate_ = TRUNC(SYSDATE)
CONNECT BY LEVEL <= LENGTH(LWNEIGHBORREL_GNBID) - LENGTH(REPLACE(LWNEIGHBORREL_GNBID, ';')) + 1
  AND PRIOR "NRCellCU".主键列 = "NRCellCU".主键列  -- 替换为表的主键/唯一非空标识列
  AND PRIOR SYS_GUID() IS NOT NULL;

注：必须替换为表的主键或唯一非空列，确保每行仅递归自身，彻底消除不必要的跨行关联开销。

2. 使用Oracle 12c+ JSON_TABLE特性（推荐） #

Oracle 12.2支持通过JSON函数快速拆分字符串，性能远优于正则递归：

SELECT jt.gnbid
FROM ericssonlw."NRCellCU" t,
     JSON_TABLE(
       '["' || REPLACE(t.LWNEIGHBORREL_GNBID, ';', '","') || '"]',
       '$[*]' COLUMNS gnbid VARCHAR2(50) PATH '$'
     ) jt
WHERE t.loaddate_ = TRUNC(SYSDATE);

该方法将分号分隔的字符串转换为JSON数组，再通过JSON_TABLE展开，完全避免递归操作，大幅降低PGA内存消耗。

3. 强制触发索引过滤与分区裁剪 #

执行计划显示全表扫描，即使loaddate_有索引，可能是分区表未触发分区裁剪或索引未被选中。可添加提示强制走索引，先过滤当天数据再拆分：

SELECT /*+ INDEX(t idx_loaddate_) */  -- 替换为loaddate_对应的索引名
       REGEXP_SUBSTR(t.LWNEIGHBORREL_GNBID, '[^;]+', 1, LEVEL) AS GNBID
FROM ericssonlw."NRCellCU" t
WHERE t.loaddate_ = TRUNC(SYSDATE)
CONNECT BY LEVEL <= LENGTH(t.LWNEIGHBORREL_GNBID) - LENGTH(REPLACE(t.LWNEIGHBORREL_GNBID, ';')) + 1
  AND PRIOR t.主键列 = t.主键列
  AND PRIOR SYS_GUID() IS NOT NULL;

若表按loaddate_分区，需确保TRUNC(SYSDATE)与分区键类型完全匹配（如均为DATE类型），触发分区裁剪缩小扫描范围。

4. 采用PIPELINED函数批量处理 #

通过PL/SQL流水线函数批量读取并拆分数据，流式返回结果，减少内存膨胀：

CREATE OR REPLACE PACKAGE pkg_split AS
  TYPE gnbid_tab IS TABLE OF VARCHAR2(50);
  FUNCTION split_gnbid(p_loaddate DATE) RETURN gnbid_tab PIPELINED;
END pkg_split;
/

CREATE OR REPLACE PACKAGE BODY pkg_split AS
  FUNCTION split_gnbid(p_loaddate DATE) RETURN gnbid_tab PIPELINED IS
    CURSOR c_data IS
      SELECT LWNEIGHBORREL_GNBID
      FROM ericssonlw."NRCellCU"
      WHERE loaddate_ = p_loaddate;
    v_str VARCHAR2(4000);
    v_pos NUMBER;
  BEGIN
    FOR rec IN c_data LOOP
      v_str := rec.LWNEIGHBORREL_GNBID || ';';
      v_pos := INSTR(v_str, ';');
      WHILE v_pos > 0 LOOP
        PIPE ROW(SUBSTR(v_str, 1, v_pos - 1));
        v_str := SUBSTR(v_str, v_pos + 1);
        v_pos := INSTR(v_str, ';');
      END LOOP;
    END LOOP;
    RETURN;
  END split_gnbid;
END pkg_split;
/

-- 调用函数
SELECT column_value AS GNBID FROM TABLE(pkg_split.split_gnbid(TRUNC(SYSDATE)));

该方法通过游标逐行读取拆分，避免递归查询的内存过载问题，适配大数量级数据场景。

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