已发现Oracle View效率低下，占用TB内存

我有一个观点，最近几个月已经被修改，现在根据解释查询正在消耗TB的内存。景色是这样的…

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;

字符串
Explain查询中的问题如下图所示.

这可能是ListAgg造成的问题，但另一种意见，如何改善将不胜感激

优化器估计查询将处理巨大数量的行。许多操作预计将返回 * 数亿到万亿行 *。即使这些数字比估计的要大，查询也可能做了大量的工作。
您需要找到一种方法来减少查询访问的数据量。一种方法是减少访问每个表的次数。
在查询中多次列出多个表/子查询。例如，LD_TRANSPORT_STATUS出现四次; ROV_TGT_LDS列出六次。重构语句以尽可能少地访问表可能会有所帮助。
下面是这些ld_transport_status连接的可能优化，以帮助您入门：

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

字符串
我将其读作“为每个ld_id查找具有最大transport_status的行”。
如果是这种情况，应该可以将其合并为transport_status的一次访问：

使用max ( transport_status )作为分析/窗口函数的子查询
过滤此值以仅返回transport_status等于找到的最大值的行。

举例来说：

left join ( 
  select max ( transport_status ) 
           over ( partition by ld_id ) mx_transport_status,
         ld_id,
         transport_status,
         -- other cols 
  from   ld_transport_status
  where  transport_status >= 1
) mdlcts
on   mdlcts.ld_id = lc.id
and  mdlcts.mx_transport_status = mdlcts.transport_status

型
有一个类似的子查询查找min ( transport_status );这应该被类似地重写。您可以将这两个查询组合到一个查询中，该查询过滤等于min或max的行。
我对查询的理解不够好，无法提出进一步的改进;很可能您将不得不迭代地处理这个问题，直到性能可以接受为止。

已发现Oracle View效率低下，占用TB内存

2条答案

相关问题

热门标签

最新问答