Oracle视图KLL_DTL_VIEW内存占用过高（TB级）优化咨询

我有一个经过多次修改的视图KLL_DTL_VIEW，执行计划显示其内存占用已达TB级，怀疑是LISTAGG函数导致内存占用过高。视图代码如下：

DROP VIEW KLL_DTL_VIEW;

CREATE OR REPLACE FORCE VIEW KLL_DTL_VIEW
(PLACENAME, PLACESTATUS, PLACEPLC, PLACERECOVERING, PLACEADDRESS, 
CENTERNAME, CENTERLOCATION, CENTERORDER, CENTERLINE, CENTERITEM, 
CENTERQUANTITY, CENTERORIGINALQUANTITY, CENTERERRORQUANTITY, CENTERCOMPLETEQUANTITY, 
CENTERSTATUS, 
CENTERPLCORDER, CENTERPLCERRORCODES, CENTERLOCKS, CENTERINVLOCKS, CENTERINVLINE, 
TGTNAME, TGTLOCATION, TGTORDER, TGTCARTON, TGTQUANTITY, 
TGTLOCKS)
BEQUEATH DEFINER
AS 
WITH
    ROV_STATION
    AS
        (SELECT S.NAME                   PLACENAME,
                SA.ROVSTATUS             PLACESTATUS,
                SA.PLC                   PLACEPLC,
                SA.ROBOTRECOVERING       PLACERECOVERING,
                P.ADDRESS                PLACEADDRESS,
                NULL                     CENTERNAME,
                NULL                     CENTERLOCATION,
                NULL                     CENTERORDER,
                NULL                     CENTERLINE,
                NULL                     CENTERITEM
           FROM STATION  S
                JOIN STATION_ATTRIBUTES SA ON S.ID = SA.ENTITY_ID
                JOIN STORAGE_ROLE SR ON S.PLACE_ROLE_ID = SR.ID
                JOIN PLACE P ON SR.PLACE_ID = P.ID
          WHERE S.NAME = 'ROV08'),
    ROV_LDS
    AS
        (SELECT LC.ID                            ID,
                LC.NAME                          NAME,
                LISTAGG (DISTINCT EL.REASON, '|')
                    WITHIN GROUP (ORDER BY EL.REASON)
                    OVER (PARTITION BY LC.ID)    LOCKS,
                CASE
                    WHEN SP.NAME = 'TS01'
                    THEN
                        NVL2 (DLCTS.PLACE_ID,
                              'Transporting To ' || DP.NAME,
                              '')
                    ELSE
                           NVL2 (SLCTS.PLACE_ID || DLCTS.PLACE_ID,
                                 'Transporting From ' || SP.NAME,
                                 SP.NAME)
                        || NVL2 (DLCTS.PLACE_ID, ' To ' || DP.NAME, '')
                END                              LOCATION
           FROM LD  LC
                LEFT JOIN
                (  SELECT MIN (TRANSPORT_STATUS)     TRANSPORT_STATUS,
                          LD_ID
                     FROM LD_TRANSPORT_STATUS
                    WHERE TRANSPORT_STATUS < 1
                 GROUP BY LD_ID) MSLCTS
                    ON MSLCTS.LD_ID = LC.ID
                LEFT JOIN LD_TRANSPORT_STATUS SLCTS
                    ON     SLCTS.LD_ID = MSLCTS.LD_ID
                       AND SLCTS.TRANSPORT_STATUS = 
                           MSLCTS.TRANSPORT_STATUS
                LEFT JOIN PLACE SP
                    ON SP.ID = 
                       CASE
                           WHEN SLCTS.PLACE_ID IS NOT NULL
                           THEN
                               SLCTS.PLACE_ID
                           ELSE
                               LC.PLACE_ID
                       END
                LEFT JOIN
                (  SELECT MAX (TRANSPORT_STATUS)     TRANSPORT_STATUS,
                          LD_ID
                     FROM LD_TRANSPORT_STATUS
                    WHERE TRANSPORT_STATUS >= 1
                 GROUP BY LD_ID) MDLCTS
                    ON MDLCTS.LD_ID = LC.ID
                LEFT JOIN LD_TRANSPORT_STATUS DLCTS
                    ON     DLCTS.LD_ID = MDLCTS.LD_ID
                       AND DLCTS.TRANSPORT_STATUS = 
                           MDLCTS.TRANSPORT_STATUS
                LEFT JOIN PLACE DP ON DP.ID = DLCTS.PLACE_ID
                JOIN ROV_STATION RS
                    ON    SP.ADDRESS LIKE 
                              '%' || RS.PLACENAME || '%'
                       OR DP.ADDRESS LIKE 
                              '%' || RS.PLACENAME || '%'
                LEFT JOIN ENTITY_LOCK EL ON EL.LD_ID = LC.ID),
    ROV_CENTER_LDS
    AS
        (SELECT NULL
                    PLACENAME,
                NULL
                    PLACESTATUS,
                NULL
                    PLACEPLC,
                NULL
                    PLACERECOVERING,
                NULL
                    PLACEADDRESS,
                RLC.NAME
                    CENTERNAME,
                RLC.LOCATION
                    CENTERLOCATION,
                O.NAME
                    CENTERORDER,
                OL.NAME
                    CENTERLINE,
                OOLA.ITEMFORMAT
                    CENTERITEM,
                NVL (ILR.QUANTITY, 0)
                    CENTERQUANTITY,
                NVL (RPS.ORIGINAL_QUANTITY, 0)
                    CENTERORIGINALQUANTITY,
                NVL (OOLA.RVERRORPKQUANTITY, 0)
                    CENTERERRORQUANTITY,
                NVL (OOLA.COMPLETEQUANTITY, 0)
                    CENTERCOMPLETEQUANTITY,
                NVL (RPS.ROBOT_PK_STATUS_ID, 0)
                    CENTERSTATUS,
                RPS.ROBOT_ORDER_ID
                    CENTERPLCORDER,
                RPS.ERROR_CODES
                    CENTERPLCERRORCODES,
                RLC.LOCKS
                    CENTERLOCKS,
                LISTAGG (DISTINCT EL.REASON, '|')
                    WITHIN GROUP (ORDER BY EL.REASON)
                    OVER (PARTITION BY IL.ID)
                    CENTERINVLOCKS,
                IL.ID
                    CENTERINVLINE,
                NULL
                    TGTNAME,
                NULL
                    TGTLOCATION,
                NULL
                    TGTORDER,
                NULL
                    TGTCARTON,
                NULL
                    TGTQUANTITY,
                NULL
                    TGTLOCKS
           FROM ROV_LDS  RLC
                JOIN SECTION S ON S.LD_ID = RLC.ID
                JOIN INV_LINE IL ON IL.SECTION_ID = S.ID
                JOIN INV_LINE_RESERVATION ILR
                    ON ILR.INV_LINE_ID = IL.ID
                JOIN ORDER_LINE OL ON OL.ID = ILR.ORDER_LINE_ID
                JOIN OUTBOUNDORDERLINE_ATTRIBUTES OOLA
                    ON OOLA.ENTITY_ID = OL.ID
                JOIN ORDERS O ON O.ID = OL.ORDER_ID
                LEFT JOIN RESERVATION_PK_STATUS RPS
                    ON RPS.INV_LINE_RESERVATION_ID = ILR.ID
                LEFT JOIN ENTITY_LOCK EL ON EL.INV_LINE_ID = IL.ID),
    ROV_TGT_LDS
    AS
        (  SELECT NULL                           PLACENAME,
                  NULL                           PLACESTATUS,
                  NULL                           PLACEPLC,
                  NULL                           PLACERECOVERING,
                  NULL                           PLACEADDRESS,
                  NULL                           CENTERNAME,
                  NULL                           CENTERLOCATION,
                  NULL                           CENTERORDER,
                  NULL                           CENTERLINE,
                  NULL                           CENTERITEM,
                  NULL                           CENTERQUANTITY,
                  NULL                           CENTERORIGINALQUANTITY,
                  NULL                           CENTERERRORQUANTITY,
                  NULL                           CENTERCOMPLETEQUANTITY
             FROM ROV_LDS RLC
                  JOIN OUTBOUNDORDER_ATTRIBUTES OOA
                      ON OOA.TGT = RLC.NAME
                  JOIN ORDERS O ON O.ID = OOA.ENTITY_ID
                  LEFT JOIN SECTION S ON S.LD_ID = RLC.ID
                  LEFT JOIN INV_LINE IL ON IL.SECTION_ID = S.ID
         GROUP BY RLC.NAME,
                  RLC.LOCATION,
                  O.NAME,
                  OOA.CARTONNAME,
                  RLC.LOCKS),
    RV_UNKNOWN_CENTER_LDS
    AS
        (SELECT NULL             PLACENAME,
                NULL             PLACESTATUS,
                NULL             PLACEPLC,
                NULL             PLACERECOVERING,
                NULL             PLACEADDRESS,
                RLC.NAME         CENTERNAME,
                RLC.LOCATION     CENTERLOCATION,
                'Unknown'        CENTERORDER,
                NULL             CENTERLINE,
                NULL             CENTERITEM
           FROM ROV_LDS RLC
          WHERE     RLC.NAME NOT IN
                        (SELECT CENTERNAME
                           FROM ROV_CENTER_LDS)
                AND RLC.NAME NOT IN
                        (SELECT TGTNAME
                           FROM ROV_TGT_LDS)
                AND (   RLC.LOCATION LIKE '%CENTER%'
                     OR RLC.LOCATION LIKE '%PK%')),
    RV_UNKNOWN_TGT_LDS
    AS
        (SELECT NULL             PLACENAME,
                NULL             PLACESTATUS,
                NULL             PLACEPLC,
                NULL             PLACERECOVERING,
                NULL             PLACEADDRESS,
                NULL             CENTERNAME,
                NULL             CENTERLOCATION,
                NULL             CENTERORDER,
                NULL             CENTERLINE,
                NULL             CENTERITEM
           FROM ROV_LDS RLC
          WHERE     RLC.NAME NOT IN
                        (SELECT CENTERNAME
                           FROM ROV_CENTER_LDS)
                AND RLC.NAME NOT IN
                        (SELECT TGTNAME
                           FROM ROV_TGT_LDS)
                AND (   RLC.LOCATION LIKE '%TGT%'
                     OR RLC.LOCATION LIKE '%Put%'))
SELECT "PLACENAME","PLACESTATUS","PLACEPLC","PLACERECOVERING","PLACEADDRESS","CENTERNAME","CENTERLOCATION","CENTERORDER","CENTERLINE","CENTERITEM","CENTERQUANTITY","CENTERORIGINALQUANTITY","CENTERERRORQUANTITY","CENTERCOMPLETEQUANTITY","CENTERSTATUS","CENTERPLCORDER","CENTERPLCERRORCODES","CENTERLOCKS","CENTERINVLOCKS","CENTERINVLINE","TGTNAME","TGTLOCATION","TGTORDER","TGTCARTON","TGTQUANTITY","TGTLOCKS"
  FROM ROV_STATION RS
UNION ALL
SELECT RSLC.PLACENAME,
       RSLC.PLACESTATUS,
       RSLC.PLACEPLC,
       RSLC.PLACERECOVERING,
       RSLC.PLACEADDRESS,
       RSLC.CENTERNAME,
       RSLC.CENTERLOCATION,
       RSLC.CENTERORDER,
       RSLC.CENTERLINE,
       RSLC.CENTERITEM
  FROM ROV_CENTER_LDS  RSLC
       LEFT JOIN ROV_TGT_LDS RTLC
           ON RTLC.TGTORDER = RSLC.CENTERORDER
 WHERE RSLC.CENTERNAME NOT IN
           (SELECT TGTNAME FROM ROV_TGT_LDS)
UNION ALL
SELECT "PLACENAME","PLACESTATUS","PLACEPLC","PLACERECOVERING","PLACEADDRESS","CENTERNAME","CENTERLOCATION","CENTERORDER","CENTERLINE","CENTERITEM","CENTERQUANTITY","CENTERORIGINALQUANTITY","CENTERERRORQUANTITY","CENTERCOMPLETEQUANTITY","CENTERSTATUS","CENTERPLCORDER","CENTERPLCERRORCODES","CENTERLOCKS","CENTERINVLOCKS","CENTERINVLINE","TGTNAME","TGTLOCATION","TGTORDER","TGTCARTON","TGTQUANTITY","TGTLOCKS"
  FROM RV_UNKNOWN_CENTER_LDS RUSLC
UNION ALL
SELECT "PLACENAME","PLACESTATUS","PLACEPLC","PLACERECOVERING","PLACEADDRESS","CENTERNAME","CENTERLOCATION","CENTERORDER","CENTERLINE","CENTERITEM","CENTERQUANTITY","CENTERORIGINALQUANTITY","CENTERERRORQUANTITY","CENTERCOMPLETEQUANTITY","CENTERSTATUS","CENTERPLCORDER","CENTERPLCERRORCODES","CENTERLOCKS","CENTERINVLOCKS","CENTERINVLINE","TGTNAME","TGTLOCATION","TGTORDER","TGTCARTON","TGTQUANTITY","TGTLOCKS"
  FROM ROV_TGT_LDS RTLC
 WHERE RTLC.TGTNAME NOT IN
           (SELECT CENTERNAME FROM ROV_CENTER_LDS)
UNION ALL
SELECT "PLACENAME","PLACESTATUS","PLACEPLC","PLACERECOVERING","PLACEADDRESS","CENTERNAME","CENTERLOCATION","CENTERORDER","CENTERLINE","CENTERITEM","CENTERQUANTITY","CENTERORIGINALQUANTITY","CENTERERRORQUANTITY","CENTERCOMPLETEQUANTITY","CENTERSTATUS","CENTERPLCORDER","CENTERPLCERRORCODES","CENTERLOCKS","CENTERINVLOCKS","CENTERINVLINE","TGTNAME","TGTLOCATION","TGTORDER","TGTCARTON","TGTQUANTITY","TGTLOCKS"
  FROM RV_UNKNOWN_TGT_LDS RUTLC;

执行计划显示存在大量内存消耗的操作，包括窗口函数带来的全量数据驻留、无索引的模糊查询导致的全表扫描等。

性能优化建议 #

1. 优化LISTAGG函数的使用 #

• 替换窗口版LISTAGG为关联子查询版：当前使用LISTAGG(...) OVER (PARTITION BY ...)会先保留所有行再执行聚合，内存开销极大。改为先分组聚合生成拼接字符串，再关联回主查询：
  原ROV_LDS中的LOCKS字段：

  LISTAGG (DISTINCT EL.REASON, '|')
      WITHIN GROUP (ORDER BY EL.REASON)
      OVER (PARTITION BY LC.ID)    LOCKS,
  

  修改为：

  (SELECT LISTAGG(DISTINCT EL.REASON, '|') WITHIN GROUP (ORDER BY EL.REASON)
   FROM ENTITY_LOCK EL
   WHERE EL.LD_ID = LC.ID) LOCKS,
  

  同理处理ROV_CENTER_LDS中的CENTERINVLOCKS，避免窗口函数导致的全量数据内存占用。
• 移除不必要的DISTINCT：如果ENTITY_LOCK表中同一LD_ID/INV_LINE_ID下的REASON无重复，直接删除DISTINCT，减少排序和去重的内存开销。

2. 减少数据扫描量 #

• 优化模糊查询关联条件：ROV_LDS中SP.ADDRESS LIKE '%' || RS.PLACENAME || '%'这类模糊查询无法使用索引，建议在PLACE表的ADDRESS字段创建全文索引，或新增PLACENAME关联字段改用等值关联。
• 提前过滤数据：将ROV_STATION的S.NAME = 'ROV08'过滤条件下推到关联的子查询中，减少后续CTE处理的数据量。
• 替换NOT IN为NOT EXISTS：RV_UNKNOWN_CENTER_LDS和RV_UNKNOWN_TGT_LDS中的NOT IN性能差且可能因NULL值导致结果异常，改为：

  NOT EXISTS (SELECT 1 FROM ROV_CENTER_LDS RCL WHERE RCL.CENTERNAME = RLC.NAME)
  

3. 索引与执行计划优化 #

• 添加必要索引：
  • 为LD_TRANSPORT_STATUS(LD_ID, TRANSPORT_STATUS)创建联合索引，优化分组子查询效率；
  • 为SECTION(LD_ID)、INV_LINE(SECTION_ID)、INV_LINE_RESERVATION(INV_LINE_ID)等关联字段创建索引；
  • 为ENTITY_LOCK(LD_ID)、ENTITY_LOCK(INV_LINE_ID)创建索引，加速LISTAGG子查询。
• 移除FORCE关键字：CREATE OR REPLACE FORCE VIEW会忽略依赖对象错误，可能导致优化器无法生成最优计划，建议去掉FORCE。

4. 简化视图逻辑 #

• 精简UNION ALL字段：部分UNION ALL分支仅需返回部分字段，无需写全量字段名，减少数据传输和内存占用；
• 拆分复杂CTE：将过于庞大的CTE拆分为多个小视图或临时表，分步处理数据，帮助优化器生成更优执行计划。

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