Home » RDBMS Server » Performance Tuning » Performance issue in procedure due to cursors (Oracle 9i)
Performance issue in procedure due to cursors [message #378826] Fri, 02 January 2009 01:04 Go to next message
vps_25
Messages: 1
Registered: January 2009
Junior Member
Hi,

The following procedure taking more time for execution, because of nested cursor, please give the solution for this.

CREATE OR REPLACE PROCEDURE Qsfw_Adv_Search_Data_Dump_Prc(
pSuccessFailure  OUT VARCHAR2,
pErrorCode       OUT VARCHAR2,
pErrorMsg   OUT VARCHAR2
)
AS

v_seperator CONSTANT VARCHAR2(2) := ':';
v_details CLOB;
i NUMBER:=1;


-- Base Cursor for getting all the case numbers which are not in adv_search table.

CURSOR cur_mantas_case IS
SELECT case_nbr FROM AML_CASE_MASTER 
WHERE case_nbr NOT IN (SELECT CONTROL_NBR FROM QSFW_ADV_SEARCH_DATA) 
and POST_PRC_FLG = 'I' 
AND CASE_SOURCE = 'MANTAS';

-- Cursor for getting the MI_TRXN details

CURSOR cur_mantas_mi_trxn(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS SELECT  TRXN_INTRL_REF_ID||v_seperator||
INSTR_NM||v_seperator||
AUG_INSTR_NB||v_seperator||
REM_NM||v_seperator||
REM_AUG_NM||v_seperator||
REM_ACCT_ID||v_seperator||
BENEF_NM||v_seperator||
BENEF_AUG_NM||v_seperator||
BENEF_ACCT_ID||v_seperator||
DEP_INSTN_NM||v_seperator||
DEP_INSTN_AUG_NM||v_seperator||
DEP_INSTN_ACCT_ID||v_seperator||
CLR_INSTN_NM||v_seperator||
CLR_INSTN_AUG_NM||v_seperator||
CLR_INSTN_ACCT_ID||v_seperator||
ISSUE_INSTN_ID||v_seperator||
ISSUE_INSTN_BRNCH_ID||v_seperator||
ISSUE_INSTN_NM||v_seperator||
ISSUE_INSTN_AUG_NM||v_seperator||
ISSUE_INSTN_ACCT_ID||v_seperator||
SCND_BENEF_NM||v_seperator||
SCND_BENEF_AUG_NM||v_seperator||
SCND_BENEF_ACCT_ID||v_seperator||
MRKNG_TX||v_seperator||
CMMNT_TX||v_seperator||
CSTM_1_TX||v_seperator||
CSTM_2_TX||v_seperator||
CSTM_3_TX||v_seperator||
CSTM_4_TX||v_seperator||
CSTM_5_TX||v_seperator||
TRXN_GRP_TX_ID||v_seperator||
CNDTR_NM||v_seperator||
CNDTR_AUG_NM||v_seperator||
CNDTR_ACCT_ID||v_seperator||
REM_NTITY_RISK_FACTR_TX||v_seperator||
REM_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_RISK_FACTR_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
DEP_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
DEP_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
CLR_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
CLR_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
ISSUE_INST_NTITY_RISK_FACTR_TX||v_seperator||
ISSUE_INST_NTITY_LIST_MATCH_TX||v_seperator||
SCND_BENEF_NTITY_LIST_MATCH_TX||v_seperator||
CNDTR_NTITY_RISK_FACTR_TX||v_seperator||
CNDTR_NTITY_LIST_MATCH_TX details
FROM MANTAS_MI_TRXN WHERE case_nbr=v_case_nbr;

-- Cursor for getting the WIRE_TRXN details

CURSOR cur_mantas_wire_trxn(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS SELECT
TRXN_INTRL_REF_ID||v_seperator||
ORIG_NM||v_seperator||
ORIG_AUG_NM||v_seperator||
ORIG_ACCT_ID||v_seperator||
BENEF_NM||v_seperator||
BENEF_AUG_NM||v_seperator||
BENEF_ACCT_ID||v_seperator||
ORIG_TO_BENEF_INSTR_TX||v_seperator||
SEND_INSTN_ID||v_seperator||
SEND_INSTN_NM||v_seperator||
SEND_INSTN_AUG_NM||v_seperator||
SEND_INSTN_ACCT_ID||v_seperator||
RCV_INSTN_ID||v_seperator||
RCV_INSTN_NM||v_seperator||
RCV_INSTN_AUG_NM||v_seperator||
RCV_INSTN_ACCT_ID||v_seperator||
BANK_TO_BANK_INSTR_TX||v_seperator||
SCND_BENEF_NM||v_seperator||
SCND_BENEF_AUG_NM||v_seperator||
SCND_BENEF_ACCT_ID||v_seperator||
CSTM_1_TX||v_seperator||
CSTM_2_TX||v_seperator||
CSTM_3_TX||v_seperator||
CSTM_4_TX||v_seperator||
CSTM_5_TX||v_seperator||
ORIG_NTITY_RISK_FACTR_TX||v_seperator||
ORIG_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_RISK_FACTR_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
SEND_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
RCV_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
RCV_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
SCND_ORIG_NTITY_RISK_FACTR_TX||v_seperator||
SCND_ORIG_NTITY_LIST_MATCH_TX||v_seperator||
SCND_BENEF_NTITY_RISK_FACTR_TX||v_seperator||
SCND_BENEF_NTITY_LIST_MATCH_TX details
FROM MANTAS_WIRE_TRXN
WHERE case_nbr=v_case_nbr;


-- Cursor for getting the Account details


CURSOR cur_mantas_acct_smry(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
A.ARC_BATCH_NM||v_seperator||
A.ACCT_INTRL_ID||v_seperator||
A.ACCT_TYPE1_CD||v_seperator||
A.ACCT_TYPE2_CD||v_seperator||
A.ACCT_TAX_ID||v_seperator||
A.TITL_LINE1_TX||v_seperator||
A.TITL_LINE2_TX||v_seperator||
A.TITL_LINE3_TX||v_seperator||
A.HH_ACCT_GRP_ID||v_seperator||
A.ALT_ACCT_ID||v_seperator||
A.ACCT_DSPLY_NM||v_seperator||
A.PRMRY_PRDCT_TYPE_CD||v_seperator||
A.LEGAL_NTITY_ID||v_seperator||
A.PRMRY_CUST_INTRL_ID||v_seperator||
A.ACCT_MATCH_TX||v_seperator||
A.TAX_PAYR_CUST_INTRL_ID||v_seperator||
A.CSTM_1_TX||v_seperator||
A.CSTM_2_TX||v_seperator||
A.CSTM_3_TX||v_seperator||
A.CSTM_4_TX||v_seperator||
A.CSTM_5_TX||v_seperator||
A.DAY_TRD_APRVL_UPDT_U_INTRL_ID||v_seperator||
A.ULTMT_INSTL_CUST_INTRL_ID||v_seperator||
A.PRCSNG_BATCH_NM||v_seperator||
A.DMCLD_BRCH_CD||v_seperator||
A.BUS_DMN_LIST_TX||v_seperator||
A.FIRM_ACCT_ORG_INTRL_ID||v_seperator||
A.ACCT_EFCTV_RISK_FACTR_TX||v_seperator||
A.SRVC_TEAM_INTRL_ID details
FROM MANTAS_ACCT a
WHERE a.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_addr(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
B.ADDR_RGN_NM||v_seperator||
B.ADDR_STRT_LINE6_TX||v_seperator||
B.ADDR_STRT_LINE5_TX||v_seperator||
B.ADDR_STRT_LINE4_TX||v_seperator||
B.ADDR_STRT_LINE3_TX||v_seperator||
B.ADDR_STRT_LINE2_TX||v_seperator||
B.ADDR_STRT_LINE1_TX||v_seperator||
B.ADDR_POSTL_CD||v_seperator||
B.ADDR_STATE_CD||v_seperator||
B.ADDR_CITY_NM||v_seperator||
B.ADDR_USAGE_CD||v_seperator||
B.PRCSNG_BATCH_NM details
FROM
MANTAS_ACCT_ADDR b
WHERE b.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_bal_posn_smry(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
C.ARC_BATCH_NM||v_seperator||
C.ACCT_INTRL_ID||v_seperator||
C.HI_CNC_SCRTY_INTRL_ID||v_seperator||
C.HI_CNC_SHRT_SCRTY_INTRL_ID||v_seperator||
C.HI_CNC_SCTR_NM||v_seperator||
C.HI_CNC_NKD_OPTN_UND_SCRTY_ID||v_seperator||
C.HI_CNC_LNG_OPTN_UND_SCRTY_ID||v_seperator||
C.HH_ACCT_GRP_ID||v_seperator||
C.CSTM_1_TX||v_seperator||
C.CSTM_2_TX||v_seperator||
C.CSTM_3_TX||v_seperator||
C.CSTM_4_TX||v_seperator||
C.CSTM_5_TX||v_seperator details
FROM
MANTAS_ACCT_BAL_POSN_SMRY c
WHERE c.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_grp(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
D.ACCT_GRP_NM||v_seperator||
D.ROUTG_ORG_INTRL_ID||v_seperator||
D.PRCSNG_BATCH_NM||v_seperator||
D.BUS_DMN_LIST_TX||v_seperator||
D.PRMRY_ACCT_INTRL_ID||v_seperator details
FROM
MANTAS_ACCT_GRP d
WHERE d.case_nbr=v_case_nbr;

CURSOR Cur_mantas_mangd_acct(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
E.ARC_BATCH_NM||v_seperator||
E.ACCT_INTRL_ID||v_seperator||
E.NVSMT_MGR_INTRL_ID||v_seperator||
E.NVSMT_MGR_NM||v_seperator||
E.MSTR_ACCT_INTRL_ID||v_seperator||
E.MSTR_ACCT_NM||v_seperator||
E.PRCSNG_BATCH_NM details
FROM
MANTAS_MANGD_ACCT e
WHERE e.case_nbr=v_case_nbr;


BEGIN

 FOR cur_case IN cur_mantas_case
  LOOP
    FOR cur_mi_trxn IN cur_mantas_mi_trxn(cur_case.case_nbr)
     LOOP
     v_details := v_details ||v_seperator || cur_mi_trxn.details;
     END LOOP;

     FOR cur_cash_trxn IN cur_mantas_cash_trxn(cur_case.case_nbr)
     LOOP
     v_details := v_details ||v_seperator || cur_cash_trxn.details;
     END LOOP;

    IF LENGTH(v_details)>1
     THEN
        i:=i +1;
       INSERT INTO QSFW_ADV_SEARCH_DATA VALUES (cur_case.case_nbr,'TRXN_DATA',v_details);
      v_details :='';
     END IF;

     FOR cur_acct IN cur_mantas_acct_smry(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||cur_acct.details;
     END LOOP;

      FOR cur_acct_addr IN Cur_mantas_acct_addr(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||cur_acct_addr.details;
     END LOOP;

      FOR Cur_acct_bal_posn_smry IN Cur_mantas_acct_bal_posn_smry(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_acct_bal_posn_smry.details;
     END LOOP;

      FOR Cur_acct_grp IN Cur_mantas_acct_grp(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_acct_grp.details;
     END LOOP;

      FOR Cur_mangd_acct IN Cur_mantas_mangd_acct(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_mangd_acct.details;
     END LOOP;

    
     IF LENGTH(v_details)>1
      THEN
         i:=i +1;
         INSERT INTO QSFW_ADV_SEARCH_DATA VALUES (cur_case.case_nbr,'FOCUS_DATA',v_details);
         v_details :='';
     END IF;
  
  IF i>10 THEN
    COMMIT;
    i:=1;
  END IF;

  END LOOP;

  COMMIT;

END;

Re: Performance issue in procedure due to cursors [message #378849 is a reply to message #378826] Fri, 02 January 2009 01:55 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
It would help if you can identify the SQL statement that causes the slow response. Also, please provide us with an explain plan, execution stats and your table/index structures. It would also help if you can tell us what optimizer you are using and how/ how frequently you collect statistics.
Re: Performance issue in procedure due to cursors [message #378944 is a reply to message #378826] Fri, 02 January 2009 19:25 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Looks to me like a Concurrent Cursors approach would help. Take a look at this article, especially the last section on Concurrent Cursors.

Ross Leishman
Previous Topic: sql tuning
Next Topic: Db writer Process
Goto Forum:
  


Current Time: Sat Dec 03 17:51:12 CST 2016

Total time taken to generate the page: 0.09599 seconds