我在web项目中使用的更新PL/SQL块内部过程花费的时间太长了
我在web项目中使用的更新PL/SQL块内部过程花费的时间太长了
提问于 2019-07-17 20:18:54
我编写了用于更新表列的PL/SQL代码，该代码基于从前端应用程序传递的输入，例如，如果INSTTABLE = 8然后运行以下块的话。我有这样的90+应用程序，并在procedure.During中运行更新，在dev environment中测试它，尽管代码运行良好，并且完成了预期的工作--首先将列值设置为零，然后调用几个函数来进行财务计算，但这花费了很长时间(超过一个小时)。最初，我认为这是由于数百万行/数据造成的，但我在这里发布它是为了检查代码是否有进一步改进的可能，以减少执行时间。
提前感谢您的帮助！
PS:我不能对函数进行更改，例如BAS2_RWA_CALC内部calc/返回值，因为它被prod中的许多其他procs所使用，因此在执行main之前，我确实在单独的update语句中将列设置为0。将其设置为零的原因是生产过程遵循相同的逻辑，即先将列设置为0，然后再进行计算。如果不这样做，比较查询就会有差异。
--explain plan for update set values to zero
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 25545 | 4365K| 1383 (1)| 00:00:01 |
| 1 | UPDATE | MORT_BACK_SEC | | | | |
|* 2 | TABLE ACCESS FULL| MORT_BACK_SEC | 25545 | 4365K| 1383 (1)| 00:00:01 |
------------------------------------------------------------------------------------
--Explain plan for merge
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 2605 | 1541 (1)| 00:00:01 |
| 1 | MERGE | MORT_BACK_SEC | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1 | 2498 | 1541 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| RPT_ACCT_HIER | 1 | 491 | 156 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| MORT_BACK_SEC | 25545 | 48M| 1384 (1)| 00:00:01 |
--------------------------------------------------------------------------------------IF INSTTABLE = 8 THEN
--Block to set columns to zero value
UPDATE usb.MORT_BACK_SEC SET
BAS_EB_RWA = 0, BAS_AB_RWA = 0, BAS_EB_RWA_COMMT = 0, BAS_AB_RWA_COMMT = 0, IMP_BAS_EB_RWA = 0, IMP_BAS_AB_RWA = 0, IDS_BAS_EB_RWA = 0,
IDS_BAS_AB_RWA = 0, DIS_BAS_EB_RWA = 0, DIS_BAS_AB_RWA = 0, PRE_BAS_EB_RWA = 0, PRE_BAS_AB_RWA = 0, BAS_AIR_EB_RWA = 0, BAS_AIR_AB_RWA = 0,
BAS_DELAY_REC_EB_RWA = 0, BAS_DELAY_REC_AB_RWA = 0, BAS_TPR_EB_RWA = 0, BAS_TPR_AB_RWA = 0, BAS_UNR_EB_RWA = 0, BAS_UNR_AB_RWA = 0,
BAS_ICAAP_EB_RWA = 0, BAS_ICAAP_AIR_EB_RWA = 0, BAS_ICAAP_DELAY_REC_EB_RWA = 0, BAS_ICAAP_DIS_EB_RWA = 0, BAS_ICAAP_IDS_EB_RWA = 0,
BAS_ICAAP_IMP_EB_RWA = 0, BAS_ICAAP_PRE_EB_RWA = 0, BAS_ICAAP_TPR_EB_RWA = 0, BAS_ICAAP_UNR_EB_RWA = 0, IMP_BAS_EB_TOTAL_CAPITAL = 0,
IMP_BAS_AB_TOTAL_CAPITAL = 0, IDS_BAS_EB_TOTAL_CAPITAL = 0, IDS_BAS_AB_TOTAL_CAPITAL = 0, DIS_BAS_EB_TOTAL_CAPITAL = 0, DIS_BAS_AB_TOTAL_CAPITAL = 0,
PRE_BAS_EB_TOTAL_CAPITAL = 0, PRE_BAS_AB_TOTAL_CAPITAL = 0, BAS_AIR_EB_TOTAL_CAPITAL = 0, BAS_AIR_AB_TOTAL_CAPITAL = 0,
BAS_DELAY_REC_EB_TOTAL_CAPITAL = 0, BAS_DELAY_REC_AB_TOTAL_CAPITAL = 0, BAS_TPR_EB_TOTAL_CAPITAL = 0, BAS_TPR_AB_TOTAL_CAPITAL = 0,
BAS_UNR_EB_TOTAL_CAPITAL = 0, BAS_UNR_AB_TOTAL_CAPITAL = 0, IMP_BAS_EB_EXPECTED_LOSS = 0, IMP_BAS_AB_EXPECTED_LOSS = 0, IDS_BAS_EB_EXPECTED_LOSS = 0,
IDS_BAS_AB_EXPECTED_LOSS = 0, DIS_BAS_EB_EXPECTED_LOSS = 0, DIS_BAS_AB_EXPECTED_LOSS = 0, PRE_BAS_EB_EXPECTED_LOSS = 0, PRE_BAS_AB_EXPECTED_LOSS = 0,
BAS_AIR_EB_EXPECTED_LOSS = 0, BAS_AIR_AB_EXPECTED_LOSS = 0, BAS_DELAY_REC_EB_EXPECTED_LOSS = 0, BAS_DELAY_REC_AB_EXPECTED_LOSS = 0,
BAS_TPR_EB_EXPECTED_LOSS = 0, BAS_TPR_AB_EXPECTED_LOSS = 0, BAS_UNR_EB_EXPECTED_LOSS = 0, BAS_UNR_AB_EXPECTED_LOSS = 0
WHERE AS_OF_DATE = TO_DATE('06/30/2019','MM/DD/YYYY');
COMMIT;
MERGE INTO ( SELECT /*+ index(a idx1) */ D.*,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_EB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_AB_RWA,
0 AS V_BAS_EB_RWA_COMMT,
0 AS V_BAS_AB_RWA_COMMT,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IMP_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IMP_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IDS_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IDS_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_DIS_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_DIS_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_PRE_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_PRE_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_AIR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_AIR_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_DELAY_REC_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_DELAY_REC_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_TPR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_TPR_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_UNR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_UNR_AB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_ICAAP_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_AIR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_DELAY_REC_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_DIS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_IDS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_IMP_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_PRE_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_TPR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_UNR_EB_RWA
FROM USB.MORT_BACK_SEC D ) A
USING (SELECT * FROM USB.rpt_acct_hier) B
ON (a.gl_account_id = b.acct_member and a.as_of_date = TO_DATE('06/30/2019','MM/DD/YYYY') and b.acct_gen2 = 'a1000')
WHEN MATCHED THEN UPDATE SET
BAS_EB_RWA = V_BAS_EB_RWA,
BAS_AB_RWA = V_BAS_AB_RWA,
BAS_EB_RWA_COMMT = V_BAS_EB_RWA_COMMT,
BAS_AB_RWA_COMMT = V_BAS_AB_RWA_COMMT,
IMP_BAS_EB_RWA = V_IMP_BAS_EB_RWA,
IMP_BAS_AB_RWA = V_IMP_BAS_AB_RWA,
IDS_BAS_EB_RWA = V_IDS_BAS_EB_RWA,
IDS_BAS_AB_RWA = V_IDS_BAS_AB_RWA,
DIS_BAS_EB_RWA = V_DIS_BAS_EB_RWA,
DIS_BAS_AB_RWA = V_DIS_BAS_AB_RWA,
PRE_BAS_EB_RWA = V_PRE_BAS_EB_RWA,
PRE_BAS_AB_RWA = V_PRE_BAS_AB_RWA,
BAS_AIR_EB_RWA = V_BAS_AIR_EB_RWA,
BAS_AIR_AB_RWA = V_BAS_AIR_AB_RWA,
BAS_DELAY_REC_EB_RWA = V_BAS_DELAY_REC_EB_RWA,
BAS_DELAY_REC_AB_RWA = V_BAS_DELAY_REC_AB_RWA,
BAS_TPR_EB_RWA = V_BAS_TPR_EB_RWA,
BAS_TPR_AB_RWA = V_BAS_TPR_AB_RWA,
BAS_UNR_EB_RWA = V_BAS_UNR_EB_RWA,
BAS_UNR_AB_RWA = V_BAS_UNR_AB_RWA,
BAS_ICAAP_EB_RWA = V_BAS_ICAAP_EB_RWA,
BAS_ICAAP_AIR_EB_RWA = V_BAS_ICAAP_AIR_EB_RWA,
BAS_ICAAP_DELAY_REC_EB_RWA = V_BAS_ICAAP_DELAY_REC_EB_RWA,
BAS_ICAAP_DIS_EB_RWA = V_BAS_ICAAP_DIS_EB_RWA,
BAS_ICAAP_IDS_EB_RWA = V_BAS_ICAAP_IDS_EB_RWA,
BAS_ICAAP_IMP_EB_RWA = V_BAS_ICAAP_IMP_EB_RWA,
BAS_ICAAP_PRE_EB_RWA = V_BAS_ICAAP_PRE_EB_RWA,
BAS_ICAAP_TPR_EB_RWA = V_BAS_ICAAP_TPR_EB_RWA,
BAS_ICAAP_UNR_EB_RWA = V_BAS_ICAAP_UNR_EB_RWA,
IMP_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IMP_BAS_EB_RWA, 0), 2),
IMP_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IMP_BAS_AB_RWA, 0), 2),
IDS_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IDS_BAS_EB_RWA, 0), 2),
IDS_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IDS_BAS_AB_RWA, 0), 2),
DIS_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_DIS_BAS_EB_RWA, 0), 2),
DIS_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_DIS_BAS_AB_RWA, 0), 2),
PRE_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_PRE_BAS_EB_RWA, 0), 2),
PRE_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_PRE_BAS_AB_RWA, 0), 2),
BAS_AIR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_AIR_EB_RWA, 0), 2),
BAS_AIR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_AIR_AB_RWA, 0), 2),
BAS_DELAY_REC_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_DELAY_REC_EB_RWA, 0), 2),
BAS_DELAY_REC_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_DELAY_REC_AB_RWA, 0), 2),
BAS_TPR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_TPR_EB_RWA, 0), 2),
BAS_TPR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_TPR_AB_RWA, 0), 2),
BAS_UNR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_UNR_EB_RWA, 0), 2),
BAS_UNR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_UNR_AB_RWA, 0), 2),
-- CALCULATE BAS_EB_EXPECTED_LOSS AND BAS_AB_EXPECTED_LOSS
IMP_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IMP_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IMP_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IMP_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IDS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IDS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DIS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DIS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (PRE_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (PRE_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (AIR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (AIR_AVG_BOOK_BAL, 0,BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DELAY_REC_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DELAY_REC_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (TPR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (TPR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (UNR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (UNR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0)
WHERE
(AS_OF_DATE = TO_DATE('06/30/2019','MM/DD/YYYY'));
COMMIT;
END IF;
--Function: BAS_RWA_CALC logic
CREATE OR REPLACE FUNCTION USB."BAS2_RWA_CALC" (v_formula in char,v_bal in number,v_k_factor in number, v_bas_min in number,v_rwa_adj_rate in number) return number
is
v_rwa number(15,2);
begin
v_rwa := nvl(v_bal,0)*nvl(v_k_factor,0)/nvl(v_bas_min,0);
v_rwa := v_rwa*(1+v_rwa_adj_rate);
return round(v_rwa,2);
end;
/
[1]: https://i.stack.imgur.com/k7ikw.png回答 2
Stack Overflow用户
回答已采纳
发布于 2019-07-19 06:00:00
有几种方法可以减少SQL和PL/SQL之间上下文切换的开销。
PRAGMA UDF;在第一个is之后，将pragma udf;添加到代码中，以告诉pragma udf;对SQL函数进行优化。在我的快速测试中，这减少了SQL调用中60%的上下文切换开销。这种方法的问题在于它需要修改每个人的函数，在PL/SQL中调用它时可能会导致性能损失。- 如果不能直接修改函数，在旧函数的基础上创建新函数，用
PRAGMA UDF;创建新函数，并在中调用这些函数，则使用包装函数。我的测试表明，这可以减少52%的上下文切换，这并不像以前的解决方案那么好，但非常接近。这种方法将所有原始逻辑保持在同一个位置，从而保留了不重复的规则。但是，它确实创建了新的对象。 - SQL /SQL
WITHfunction 将包装器函数创建为SQL语句中的公共表表达式。这可以减少大约32%的开销。这是一种纯SQL方法，但速度不如其他解决方案，SQL语句看起来很难看。
这些方法对你不起作用，但对于未来的读者来说，最好的选择是：
- 在声明性SQL代码中移动过程PL/SQL代码。
- 更改PL/SQL接口以接受批量数据，例如集合、游标、动态SQL的表名等。
Stack Overflow用户
发布于 2019-07-17 22:14:29
在合并语句中，有59个函数调用。太多了。看起来，对于MORT_BACK_SEC中的每一行，您都调用了27次‘MORT_BACK_SEC’，即使您没有使用这些调用的结果。现在，数据库可能足够聪明，只对您实际从MORT_BACK_SEC中使用的行进行这些调用，但我不相信这一点。如果其他地方不需要它们(我看不出来)，我建议将这些计算推到WHEN MATCHED块中。
对于所做的每一次更新，您还需要调用BAS2_MGRL_CAPITAL和BAS2_EL_CALC 16次。我不知道这些函数有多复杂，但是如果它们很简单，并且可以被内联CASE表达式替换，那么我就会这样做，因为函数调用的数量会影响这个语句的性能。我不知道这会产生什么样的计划，但我怀疑这很重要--我怀疑语句的运行时间将被所有这些函数调用的执行时间所支配。
我会尝试为您重新排列MERGE语句，但是我不知道哪些函数参数是MORT_BACK_SEC中的列，哪些是在MERGE语句之外定义的变量。尽可能多地将函数调用从语句本身中筛选出来，如果其中任何一个是可以的话，当然可以将它们从初始的MERGE表中提取出来。我想你的MERGE应该从
MERGE INTO MORT_BACK_SEC a
USING RPT_ACCT_HIER b
ON (a.GL_ACCOUNT_ID = b.ACCT_MEMBER AND
a.AS_OF_DATE = V_DATE AND
b.ACCT_GEN2 = 'a1000')然后从那里拿走。
当然，由于您没有WHEN NOT MATCHED THEN INSERT，所以一旦将所有函数调用移到UPDATE块中，就可以以UPDATE而不是MERGE的形式重新执行这些操作。由你决定。但是，我再次怀疑这很重要，因为这些函数调用是我怀疑这个语句花费时间的地方。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57083542
复制相关文章
相似问题
腾讯云开发者
