plsql - 如何改进 pl-sql 过程代码，100k 记录需要 2 小时
问题描述
如何提高以下代码的性能？100k 条记录需要 2 小时。
create or replace PROCEDURE APO_RMS_E_PRIO_ITEM_REPL_P
AS
O_error_message VARCHAR2(2000);
L_program VARCHAR2(100):= 'APO_RMS_E_PRIO_ITEM_REPL_P';
L_SOH NUMBER(20,4);
L_PRIO_ITEM_SOH NUMBER(20,4);
L_SUM_SOH NUMBER(20,4);
L_ITEM VARCHAR2(25);
L_MIN_STOCK NUMBER(20,4);
L_MAX_STOCK NUMBER(20,4);
L_ORDER_ROQ NUMBER(20,4);
--cursor to get all prio items............
CURSOR CUR_1 IS
SELECT OT.LOCATION,
OT.ITEM
FROM ITEM_MASTER IM,
ORD_TEMP OT
WHERE IM.ITEM = OT.ITEM
AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 115 AND RUV.UDA_ITEM = IM.ITEM)
AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 116 AND RUV.UDA_ITEM = IM.ITEM)
AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 117 AND RUV.UDA_ITEM = IM.ITEM)
AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 163 AND RUV.UDA_LOV_VALUE != 0 AND RUV.UDA_ITEM = IM.ITEM)
AND NOT EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 122 AND RUV.UDA_ITEM = IM.ITEM)
AND NOT EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 123 AND RUV.UDA_ITEM = IM.ITEM);
--cursor to get all items for the prio item group........
cursor cur_2 (l_item varchar2) is
WITH RUV AS
(
SELECT UDA_ID, UDA_ITEM, UDA_LOV_VALUE FROM RPM_UDA_VIEW
),
ARIIS as
(
SELECT * FROM APO_RMS_I_ITEM_STG
)
SELECT ITEM
FROM ITEM_MASTER IM
WHERE EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 115 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 116 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 117 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 163 AND RUV2.UDA_LOV_VALUE != 0 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_ID = RUV2.UDA_ID)
AND NOT EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 122 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
AND NOT EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 123 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
AND IM.ITEM IN (SELECT ITEM_PARENT FROM ITEM_MASTER WHERE ITEM IN (
(
(SELECT A.VARNUMMER
FROM APO_RMS_I_ITEM_STG A
WHERE a.date_received =
(SELECT MAX(a1.date_received)
FROM apo_rms_i_item_stg a1
WHERE a1.varuid=a.varuid
)
AND a.ANTAL_NUMERISK *NVL(a.ANTAL_MULTIPEL_1, 1)*NVL(a.ANTAL_MULTIPEL_2, 1) BETWEEN
(SELECT MAX(L.ANTAL_NUMERISK*NVL(L.ANTAL_MULTIPEL_1, 1)*NVL(L.ANTAL_MULTIPEL_2, 1)*0.88)
FROM APO_RMS_I_ITEM_STG L
WHERE L.date_received =
(SELECT MAX(L1.date_received)
FROM apo_rms_i_item_stg L1
WHERE L1.varuid=L.varuid
)
AND L.VARNUMMER =(select item from item_master where ITEM_PARENT=L_ITEM and PRIMARY_REF_ITEM_IND='Y')
)
AND (SELECT MAX(H.ANTAL_NUMERISK*NVL(H.ANTAL_MULTIPEL_1, 1)*NVL(H.ANTAL_MULTIPEL_2, 1)*1.12)
FROM APO_RMS_I_ITEM_STG H
WHERE H.date_received =
(SELECT MAX(H1.date_received)
FROM apo_rms_i_item_stg H1
WHERE H1.varuid=H.varuid
)
AND H.VARNUMMER=(select item from item_master where ITEM_PARENT=L_ITEM and PRIMARY_REF_ITEM_IND='Y'))
AND IM.ITEM NOT IN
(SELECT I.uda_ITEM FROM rpm_uda_view I WHERE I.UDA_ID in (122,123)
)
))
));
BEGIN
FOR REC_1 IN CUR_1
LOOP
L_SUM_SOH := 0;
FOR REC_2 IN CUR_2(REC_1.ITEM)
LOOP
--get stock_on_hand for each item in prio group.....
SELECT IL.STOCK_ON_HAND
INTO L_PRIO_ITEM_SOH
FROM ITEM_LOC_SOH IL
WHERE IL.LOC = REC_1.LOCATION
AND IL.ITEM = REC_1.ITEM;
--get total available stock for each item in prio group.....
SELECT (CASE WHEN AVAILABLE_SOH < 0 THEN 0 ELSE AVAILABLE_SOH END) INTO L_SOH FROM
(
SELECT
SUM(
(NVL(ILS.TSF_EXPECTED_QTY, 0) + NVL(ILS.IN_TRANSIT_QTY, 0) + NVL(ILS.STOCK_ON_HAND, 0) + NVL(ON_ORDER_QTY, 0))
-
(NVL(ILS.TSF_RESERVED_QTY, 0) + NVL(ILS.RTV_QTY, 0) + NVL(ILS.NON_SELLABLE_QTY, 0) + NVL(ILS.CUSTOMER_RESV, 0))
) AVAILABLE_SOH
FROM ITEM_LOC_SOH ILS,
(SELECT L.ITEM,
NVL(SUM(L.QTY_ORDERED - NVL(L.QTY_RECEIVED, 0)), 0) ON_ORDER_QTY
FROM ORDHEAD H,
ORDLOC L
WHERE L.ITEM = REC_2.ITEM
AND L.LOCATION = REC_1.LOCATION
AND L.LOC_TYPE ='S'
AND L.ORDER_NO = H.ORDER_NO
AND H.STATUS = 'A'
AND L.QTY_ORDERED > NVL(L.QTY_RECEIVED, 0)
AND H.ORDER_TYPE != 'CO'
GROUP BY L.ITEM) ORD
WHERE ILS.ITEM = ORD.ITEM(+)
AND ILS.ITEM = REC_2.ITEM
AND ILS.LOC = REC_1.LOCATION);
L_SUM_SOH := L_SUM_SOH + L_SOH;
END LOOP;
--get min max for the prio item....
SELECT RL.MIN_STOCK,
RL.MAX_STOCK
INTO L_MIN_STOCK,
L_MAX_STOCK
FROM REPL_ITEM_LOC RL
WHERE RL.LOCATION = REC_1.LOCATION
AND RL.ITEM = REC_1.ITEM;
IF (L_SUM_SOH < L_MIN_STOCK)
Then
SELECT OT.QTY_ORDERED
INTO L_ORDER_ROQ
FROM RMS13PRD.ORD_TEMP OT
WHERE OT.LOCATION = REC_1.LOCATION
AND OT.ITEM = REC_1.ITEM;
INSERT INTO APO_RMS_E_PRIO_ITEM
SELECT ORD_TEMP_SEQ_NO,
CROSSDOCK_IND,
CROSSDOCK_STORE,
DEPT,
SUPPLIER,
ITEM,
PACK_IND,
ORIGIN_COUNTRY_ID,
UNIT_COST,
PICKUP_LEAD_TIME,
SUPP_LEAD_TIME,
LOCATION,
LOC_TYPE,
L_ORDER_ROQ,
SUPP_PACK_SIZE,
ORDER_STATUS,
CONTRACT_NO,
CONTRACT_TYPE,
DUE_IND,
L_SUM_SOH,
L_PRIO_ITEM_SOH,
SYSDATE,
'update_prio_item'
FROM RMS13PRD.ORD_TEMP OT
WHERE OT.LOCATION = REC_1.LOCATION
AND OT.ITEM = REC_1.ITEM;
COMMIT;
UPDATE ORD_TEMP OT
SET OT.QTY_ORDERED = NVL(L_MAX_STOCK, 0) - NVL(L_SUM_SOH, 0)
WHERE OT.LOCATION = REC_1.LOCATION
AND OT.ITEM = REC_1.ITEM;
COMMIT;
ELSE
INSERT INTO APO_RMS_E_PRIO_ITEM
SELECT ORD_TEMP_SEQ_NO,
CROSSDOCK_IND,
CROSSDOCK_STORE,
DEPT,
SUPPLIER,
ITEM,
PACK_IND,
ORIGIN_COUNTRY_ID,
UNIT_COST,
PICKUP_LEAD_TIME,
SUPP_LEAD_TIME,
LOCATION,
LOC_TYPE,
QTY_ORDERED,
SUPP_PACK_SIZE,
ORDER_STATUS,
CONTRACT_NO,
CONTRACT_TYPE,
DUE_IND,
L_SUM_SOH,
L_PRIO_ITEM_SOH,
SYSDATE,
'delete_prio_item'
FROM RMS13PRD.ORD_TEMP OT
WHERE OT.LOCATION = REC_1.LOCATION
AND OT.ITEM = REC_1.ITEM;
COMMIT;
DELETE FROM RMS13PRD.ORD_TEMP OT
WHERE OT.LOCATION = REC_1.LOCATION
AND OT.ITEM = REC_1.ITEM;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
O_error_message := L_program||'-'||SQLCODE||SQLERRM;
END APO_RMS_E_PRIO_ITEM_REPL_P;
解决方案
当我需要找出程序的哪一部分需要（太多）时间来执行时，我会使用一种简单的日志记录技术：一个包含关于我在哪里以及我在做什么以及时间戳的信息的表。它是通过一个AUTONOMOUS_TRANSACTION过程填充的（这样我就可以提交日志信息，而不会影响调用者）。
这是脚本（随意修改）：
CREATE TABLE a1_log
(
id NUMBER,
datum DATE,
descr VARCHAR2 (500)
);
CREATE SEQUENCE seqlognap START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE PROCEDURE a1_p_log (par_descr IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO a1_log
SELECT seqlognap.NEXTVAL, SYSDATE, par_descr FROM DUAL;
COMMIT;
END a1_p_log;
然后，在我正在处理的过程中，放置 a1_p_log 调用，例如
begin
a1_p_log('step 1: initializing');
-- some init code here
a1_p_log('step 2: an ugly query follows now');
SELECT blabla --> very ugly query
a1_p_log('step 3: updating xyz table');
UPDATE xyz set ...
a1_p_log('step 4: finish');
end;
该过程可能需要一些时间才能完成（几分钟，几小时......），但我可以随时查询a1_log表格：
SELECT id,
datum,
LEAD (datum) OVER (ORDER BY id) datum_next,
(LEAD (datum) OVER (ORDER BY id) - datum) * 24 * 60 * 60 diff_sec,
descr
FROM a1_log
ORDER BY id;
注意DIFF_SEC值，它显示两个步骤之间的秒数。如果您注意到这里的数字很大，那么您应该尝试优化该查询以使其运行得更快。
至于你的代码：太复杂了，我不能说什么smart，除了：你可能想COMMIT从循环中删除所有这些调用。COMMIT在过程结束时使用一个,。
推荐阅读
- scala - Right[Nothing, Map[String, String]] 类型的表达式不符合预期的 Left[String, Nothing] 类型
- java - Spring Cloud Stream 消息未通过消息队列接收
- javascript - 使用 react-native-router-flux 将数据从屏幕发送到选项卡屏幕
- karate - 在日志中隐藏密码
- android - 未安装客户端应用程序的推送通知
- php - 我从 API 中获取选择选项，因为应该选择一个选项
- javascript - 如何在 axios 中解决 Promise 和 spy 函数
- sql-server - 使用 SSIS 的存储过程部署和备份
- sql - SQL DB2/400：检查是否存在全局临时表
- sql - SQL 子查询首先按 not null 分组