首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >我在web项目中使用的更新PL/SQL块内部过程花费的时间太长了

我在web项目中使用的更新PL/SQL块内部过程花费的时间太长了
EN

Stack Overflow用户
提问于 2019-07-17 20:18:54
回答 2查看 148关注 0票数 1

我编写了用于更新表列的PL/SQL代码,该代码基于从前端应用程序传递的输入,例如,如果INSTTABLE = 8然后运行以下块的话。我有这样的90+应用程序,并在procedure.During中运行更新,在dev environment中测试它,尽管代码运行良好,并且完成了预期的工作--首先将列值设置为零,然后调用几个函数来进行财务计算,但这花费了很长时间(超过一个小时)。最初,我认为这是由于数百万行/数据造成的,但我在这里发布它是为了检查代码是否有进一步改进的可能,以减少执行时间。

提前感谢您的帮助!

PS:我不能对函数进行更改,例如BAS2_RWA_CALC内部calc/返回值,因为它被prod中的许多其他procs所使用,因此在执行main之前,我确实在单独的update语句中将列设置为0。将其设置为零的原因是生产过程遵循相同的逻辑,即先将列设置为0,然后再进行计算。如果不这样做,比较查询就会有差异。

代码语言:javascript
运行
复制
--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 |
--------------------------------------------------------------------------------------
代码语言:javascript
运行
复制
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
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-07-19 06:00:00

有几种方法可以减少SQL和PL/SQL之间上下文切换的开销。

  1. PRAGMA UDF;在第一个is之后,将pragma udf;添加到代码中,以告诉pragma udf;对SQL函数进行优化。在我的快速测试中,这减少了SQL调用中60%的上下文切换开销。这种方法的问题在于它需要修改每个人的函数,在PL/SQL中调用它时可能会导致性能损失。
  2. 如果不能直接修改函数,在旧函数的基础上创建新函数,用PRAGMA UDF;创建新函数,并在中调用这些函数,则使用包装函数。我的测试表明,这可以减少52%的上下文切换,这并不像以前的解决方案那么好,但非常接近。这种方法将所有原始逻辑保持在同一个位置,从而保留了不重复的规则。但是,它确实创建了新的对象。
  3. SQL /SQL WITH function 将包装器函数创建为SQL语句中的公共表表达式。这可以减少大约32%的开销。这是一种纯SQL方法,但速度不如其他解决方案,SQL语句看起来很难看。

这些方法对你不起作用,但对于未来的读者来说,最好的选择是:

  1. 在声明性SQL代码中移动过程PL/SQL代码。
  2. 更改PL/SQL接口以接受批量数据,例如集合、游标、动态SQL的表名等。
票数 1
EN

Stack Overflow用户

发布于 2019-07-17 22:14:29

在合并语句中,有59个函数调用。太多了。看起来,对于MORT_BACK_SEC中的每一行,您都调用了27次‘MORT_BACK_SEC’,即使您没有使用这些调用的结果。现在,数据库可能足够聪明,只对您实际从MORT_BACK_SEC中使用的行进行这些调用,但我不相信这一点。如果其他地方不需要它们(我看不出来),我建议将这些计算推到WHEN MATCHED块中。

对于所做的每一次更新,您还需要调用BAS2_MGRL_CAPITALBAS2_EL_CALC 16次。我不知道这些函数有多复杂,但是如果它们很简单,并且可以被内联CASE表达式替换,那么我就会这样做,因为函数调用的数量会影响这个语句的性能。我不知道这会产生什么样的计划,但我怀疑这很重要--我怀疑语句的运行时间将被所有这些函数调用的执行时间所支配。

我会尝试为您重新排列MERGE语句,但是我不知道哪些函数参数是MORT_BACK_SEC中的列,哪些是在MERGE语句之外定义的变量。尽可能多地将函数调用从语句本身中筛选出来,如果其中任何一个是可以的话,当然可以将它们从初始的MERGE表中提取出来。我想你的MERGE应该从

代码语言:javascript
运行
复制
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的形式重新执行这些操作。由你决定。但是,我再次怀疑这很重要,因为这些函数调用是我怀疑这个语句花费时间的地方。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/57083542

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档