Update PL/SQL block inside procedure, which I'm using in web project, is taking too long

I've written PL/SQL code for updating columns of table, based on passed input from front end application e.g. if INSTTABLE = 8 then following block will run. I've 90+ applications like this and running update inside procedure.During testing it in dev environment, Although code is working fine and doing intended job which is to set columns values to zero first and then call few functions to do financial calculations, it is taking too long (more than an hour). Initially I thought it is because of many millions of rows/data but I am posting it here to check whether there is any further improvement is possible in code to reduce execution time.

Thanks in advance for kind help!

PS: I can't make changes to functions, such as BAS2_RWA_CALC internal calc/return value because it is being used by many other procs in prod, hence I did set columns to 0, in seperate update statement, before doing main update. Reason for setting it to zero is that production is following same logic of setting columns to 0 first and then doing calculations. If I don't do so, there is difference in comparison query.

--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

1 answer

  • answered 2019-07-17 22:14 Bob Jarvis

    In your MERGE statement you've got 59 function calls. That's a lot. It looks like you're calling 'BAS2_RWA_CALC' 27 times for every row in MORT_BACK_SEC, even if you don't use the results of those calls. Now, it may be that the database is smart enough to only make those calls for the rows you actually end up using from MORT_BACK_SEC, but I wouldn't count on that. If they're not needed elsewhere (and I don't see that they are) I suggest pushing those calculations into the WHEN MATCHED block. You're also calling BAS2_MGRL_CAPITAL and BAS2_EL_CALC 16 times for every update that gets made. I have no idea how complex these functions are but if they're simple and can be replaced by an inline CASE expression or the like I'd do that, because that number of function calls are going to kill the performance of this statement. I don't know what kind of plan this is generating, but I doubt it matters - I suspect that the run time of the statement will be dominated by the execution time of all those function calls.

    I'd try to rearrange your MERGE statement for you but I don't know which function parameters are columns in MORT_BACK_SEC and which ones are variables defined outside the MERGE statement. Try to factor as many function calls as you can out of the statement itself, if any of them can be, and certainly get them out of the initial MERGE table. IMO your MERGE should start with

    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')
    

    and then take it from there.

    Of course, since you don't have a WHEN NOT MATCHED THEN INSERT, you could re-do this as an UPDATE instead of a MERGE once you get all those function calls moved into the UPDATE block. It's up to you. But again, I doubt it matters as those function calls are where I suspect that this statement spends its time.

    Best of luck.