union of 2 select statements in a procedure
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: union of 2 select statements in a procedure

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    There is a procedure which is a union of 2 select statements with an order by clause at the end of the second select clause. This proceure takes a very long time to execute in Production. I checked to see if it was using the right index but it wasn't so I put an index hint. Although it is now using that index, it is still taking a long time.

    It is as follows:

    Procedure SP_RELTRANS
    (
    SYSCLIENTID IN varchar2,
    CLOSED_DATE IN varchar2,
    SYSLANGUAGE IN varchar2,
    SYSORACHARSET IN varchar2,
    SYSTIMEOFFSET IN number := 0,
    Pmt_Relt_cur IN OUT pmtw_types.pmt_relt_cur_type);
    Pragma RESTRICT_REFERENCES(SP_RELTRANS,WNDS);



    Procedure SP_RELTRANS
    (
    SYSCLIENTID IN varchar2:='0',
    CLOSED_DATE IN varchar2:=sysdate,
    SYSLANGUAGE IN varchar2,
    SYSORACHARSET IN varchar2,
    SYSTIMEOFFSET IN number := 0,
    Pmt_Relt_cur IN OUT pmtw_types.pmt_relt_cur_type) is
    PSTART_CLOSED_DATE date;
    PEND_CLOSED_DATE date;
    BEGIN
    PSTART_CLOSED_DATE := to_date(rpad(substr(CLOSED_DATE,1,8),17,' 00:00:00'),'YYYYMMDD HH24:MI:SS');
    PEND_CLOSED_DATE := to_date(rpad(substr(CLOSED_DATE,1,8),17,' 23:59:59'),'YYYYMMDD HH24:MI:SS');
    PSTART_CLOSED_DATE := PSTART_CLOSED_DATE - (SYSTIMEOFFSET + 5)/24;
    PEND_CLOSED_DATE := PEND_CLOSED_DATE - (SYSTIMEOFFSET + 5)/24;
    open Pmt_Relt_cur
    for
    select /*+ index(t transaction_ndx_8) */
    distinct
    t.clnt_id as client_id,
    t.co_nam as company_name,
    t.wrld_lnk_clnt_no as wl_no,
    te.acct_ccy_cod as acct_ccy,
    te.acct_no as fa_no,
    te.acct_fam_nam as fa_name,
    t.tran_typ as tran_type,
    t.mt100_ref_no as reference_no,
    substr(t.bene_pty_nam,1,20) as payee_name,--CR
    t.tran_val_dt as value_date,
    nvl(convert(substr(tvm_1.typ_val_des,1,11),'WE8MSWIN1252'),substr(tve_1.typ_val_des,1,11))as status,--CR
    Get_FXVal(t.tran_no) as contract_no,
    te.iso_ccy_cod as issue_ccy,
    te.tran_amt as payment_amount,
    to_char(te.conv_rt,'99990D99999999') as fx_rate,
    te.equiv_amt as equivalent_amount,
    nvl(ccy1.ccy_deci_plac,2) as pymt_decimal_cnt,
    nvl(ccy2.ccy_deci_plac,2) as equiv_decimal_cnt,
    to_char(f.usr_actn_dt, 'yyyymmdd') as release_date
    from
    transaction t,
    tran_entry te,
    tran_life_cyc f,
    type_values tvm_1,
    type_values tve_1,
    currency ccy1,
    currency ccy2
    where t.tran_no = te.tran_no and
    t.tran_no = f.tran_no and
    f.usr_actn_typ = 'RELEASE' AND
    te.iso_ccy_cod <> te.acct_ccy_cod AND
    t.tran_typ IN ('RCH','EFT','OCH') and
    (t.pymt_typ is null or t.pymt_typ = 'CORP') and
    t.proc_loca='999' and
    t.tran_sta_typ<>'REPAIR_REQ' and ----03/11/02 GUAT0276577
    t.tran_sta_typ = tvm_1.typ_val(+) AND
    tvm_1.typ_def(+) = 'TRAN_STA' and
    t.tran_sta_typ = tve_1.typ_val and
    tve_1.typ_def = 'TRAN_STA' and
    tve_1.lang_id = 'en_US' and
    te.iso_ccy_cod = ccy1.iso_ccy_cod(+) and
    te.acct_ccy_cod = ccy2.iso_ccy_cod(+) and
    ( t.clnt_id= '1318253') and
    f.usr_actn_dt between '20020419' and '20020419' and
    (tvm_1.lang_id(+) = 'en_US')
    UNION
    select /*+ index(t transaction_ndx_8) */
    distinct
    t.clnt_id as client_id,
    t.co_nam as company_name,
    t.wrld_lnk_clnt_no as wl_no,
    te.acct_ccy_cod as acct_ccy,
    te.acct_no as fa_no,
    te.acct_fam_nam as fa_nm,
    t.tran_typ as tran_type,
    t.mt100_ref_no as reference_no,
    substr(t.bene_pty_nam,1,20) as payee_name,
    t.tran_val_dt as value_date,
    nvl(convert(substr(tvm_1.typ_val_des,1,11),'WE8MSWIN1252'),substr(tve_1.typ_val_des,1,11)) as status,
    -- 02/08 YP:----- t.tran_no as contract_no,
    ' ' as contract_no,
    te.iso_ccy_cod as issue_ccy,
    te.tran_amt as payment_amount,
    '1.00000000' as fx_rate, ---
    te.equiv_amt as equivalent_amount,
    nvl(ccy1.ccy_deci_plac,2) as pymt_decimal_cnt,
    nvl(ccy2.ccy_deci_plac,2) as equiv_decimal_cnt,
    to_char(f.usr_actn_dt,'yyyymmdd') as release_date
    from transaction t,
    tran_entry te,
    tran_life_cyc f,
    type_values tvm_1,
    type_values tve_1,
    currency ccy1,
    currency ccy2
    where
    te.iso_ccy_cod = te.acct_ccy_cod and
    t.tran_no = te.tran_no and
    t.tran_no = f.tran_no and
    f.usr_actn_typ = 'RELEASE' AND
    t.tran_typ IN ('RCH','EFT','OCH') and
    (t.pymt_typ is null or t.pymt_typ = 'CORP') and
    t.proc_loca='999' and
    t.tran_sta_typ<>'REPAIR_REQ' and ----03/11/02 GUAT0276577
    t.tran_sta_typ = tvm_1.typ_val(+) AND
    tvm_1.typ_def(+) = 'TRAN_STA' and
    t.tran_sta_typ = tve_1.typ_val and
    tve_1.typ_def = 'TRAN_STA' and
    tve_1.lang_id = 'en_US' and
    te.iso_ccy_cod = ccy1.iso_ccy_cod(+) and
    te.acct_ccy_cod = ccy2.iso_ccy_cod(+) and
    ( t.clnt_id= '1318253') and
    f.usr_actn_dt between '20020419' and '20020419' and
    (tvm_1.lang_id(+) = 'en_US')
    order by 2,6,4,7,10;
    END SP_RELTRANS;
    -----------------------------------------

    transaction_ndx_8 index has
    clnt_id,
    tran_sta_typ,
    tran_typ as the index columns.

    For some reason, the TRAN_LIFE_CYC table is doing a FULL TABLE SCAN. Which index should I use? TRAN_LIFE_CYC HAS THE UNIQUE INDEX : pk_tran_life_cyc
    ON tran_life_cyc
    ( tran_no,
    usr_actn_typ,
    usr_actn_dt )


    Tried to use this index hint, but it is still slow.


    Please help ASAP.

    Thanks.

  2. #2
    Join Date
    Feb 2000
    Posts
    142
    Could somebody please help?????

    Thanks.

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    Somebody help me please!!

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by lee
    Somebody help me please!!
    Hard one :-) Analyze the tables used and the indexes, post again here if procedure is still slow.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Feb 2000
    Posts
    142
    The tables are already analyzed :
    alter table compute statistics.


    What else should I do?


    Thanks.

  6. #6
    Join Date
    Feb 2000
    Posts
    142
    It is still slow.
    Please help.

    Thanks.

  7. #7
    Join Date
    Apr 2002
    Posts
    11

    The UNION is unnecessary

    If I have compared the parts of the UNION correctly,
    the UNION itself is not necessary. The only difference
    between the two parts (where te.iso_ccy_cod != te.acct_ccy_cod,
    and where it is equal) is the "contract_no" and "fx_rate" columns.
    Instead, you could drop the 2nd part of the UNION;
    remove the check for the "iso" code != the "acct" code;
    and use DECODE to get the desired values for the 2 columns:

    DECODE(te.iso_ccy_cod, te.acct_ccy_cod, '', Get_FXVAL(t.tran_no)) as contract_no

    DECODE(te.iso_ccy_cod, te.acct_ccy_cod, '1.00000000',
    to_char(te.conv_rt,'99990D99999999')) as fx_rate


    I would also suggest replacing the "index()" hint
    with an "ordered" hint; this should make Oracle access
    the tran_life_cyc table in a Nested Loop using the table's unique index (which could have been implemented as a PRIMARY KEY constraint instead).


    You may also want to check if the "DISTINCT" option is really necessary.


    If all this does not improve the query, the Explain Plan for the query will need to be examined in more detail.


  8. #8
    Join Date
    Feb 2000
    Posts
    142
    Thanks but the DISTICT cannot be removed. Also, the UNION is required. So we will have to find a way to tune it as it is.

    I tried to put 'FIRST_ROWS ORDERED' hints but it is still slow.

    The original plan is as follows as per TKPROF:


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    261 SORT (UNIQUE)
    261 UNION-ALL
    15 NESTED LOOPS (OUTER)
    16 NESTED LOOPS (OUTER)
    16 NESTED LOOPS
    262 NESTED LOOPS
    30002 NESTED LOOPS (OUTER)
    30002 NESTED LOOPS
    30 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
    OF 'TYPE_VALUES'
    30 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TYPE_VALUES' (UNIQUE)
    30030 INLIST ITERATOR
    30088 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
    OF 'TRANSACTION'
    31884 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'TRANSACTION_NDX_1' (NON-UNIQUE)
    30001 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'TYPE_VALUES'
    60002 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    'PK_TYPE_VALUES' (UNIQUE)
    30262 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TRAN_LIFE_CYC' (UNIQUE)
    276 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'TRAN_ENTRY'
    522 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TRAN_ENTRY' (UNIQUE)
    15 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'CURRENCY'
    30 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_CURRENCY'
    (UNIQUE)
    15 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'CURRENCY'
    30 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_CURRENCY'
    (UNIQUE)
    246 NESTED LOOPS (OUTER)
    247 NESTED LOOPS (OUTER)
    247 NESTED LOOPS
    262 NESTED LOOPS
    30002 NESTED LOOPS (OUTER)
    30002 NESTED LOOPS
    30 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
    OF 'TYPE_VALUES'
    30 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TYPE_VALUES' (UNIQUE)
    30030 INLIST ITERATOR
    30088 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
    OF 'TRANSACTION'
    31884 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'TRANSACTION_NDX_1' (NON-UNIQUE)
    30001 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'TYPE_VALUES'
    60002 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
    'PK_TYPE_VALUES' (UNIQUE)
    30262 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TRAN_LIFE_CYC' (UNIQUE)
    507 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'TRAN_ENTRY'
    522 INDEX GOAL: ANALYZED (RANGE SCAN) OF
    'PK_TRAN_ENTRY' (UNIQUE)
    246 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'CURRENCY'
    492 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_CURRENCY'
    (UNIQUE)
    246 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'CURRENCY'
    492 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_CURRENCY'
    (UNIQUE)


    Please help.

    Thanks.

  9. #9
    Join Date
    Apr 2002
    Posts
    11

    No, the UNION is NOT required!

    I understand that your data conditions may make the DISTINCT required (although it seems that you rejected that suggestion out-of-hand, instead of actually examining the data being returned from the query, to see if it might already be unique without using the DISTINCT option), but it is NOT correct to say that the UNION is required. Since you did not understand my description of how to combine the 2 parts of the UNION into one query, here is the result:

    select /*+ ORDERED */
    distinct
    t.clnt_id as client_id,
    t.co_nam as company_name,
    t.wrld_lnk_clnt_no as wl_no,
    te.acct_ccy_cod as acct_ccy,
    te.acct_no as fa_no,
    te.acct_fam_nam as fa_name,
    t.tran_typ as tran_type,
    t.mt100_ref_no as reference_no,
    substr(t.bene_pty_nam,1,20) as payee_name,
    t.tran_val_dt as value_date,
    nvl(convert(substr(tvm_1.typ_val_des,1,11),'WE8MSWIN1252'),substr(tve_1.typ_val_des,1,11))as status,
    DECODE(te.iso_ccy_cod, te.acct_ccy_cod, '', Get_FXVAL(t.tran_no)) as contract_no,
    te.iso_ccy_cod as issue_ccy,
    te.tran_amt as payment_amount,
    DECODE(te.iso_ccy_cod, te.acct_ccy_cod, '1.00000000',
    to_char(te.conv_rt,'99990D99999999')) as fx_rate,
    te.equiv_amt as equivalent_amount,
    nvl(ccy1.ccy_deci_plac,2) as pymt_decimal_cnt,
    nvl(ccy2.ccy_deci_plac,2) as equiv_decimal_cnt,
    to_char(f.usr_actn_dt, 'yyyymmdd') as release_date
    from
    transaction t,
    tran_entry te,
    tran_life_cyc f,
    type_values tvm_1,
    type_values tve_1,
    currency ccy1,
    currency ccy2
    where t.tran_no = te.tran_no and
    t.tran_no = f.tran_no and
    f.usr_actn_typ = 'RELEASE' AND
    t.tran_typ IN ('RCH','EFT','OCH') and
    (t.pymt_typ is null or t.pymt_typ = 'CORP') and
    t.proc_loca='999' and
    t.tran_sta_typ<>'REPAIR_REQ' and ----03/11/02 GUAT0276577
    t.tran_sta_typ = tvm_1.typ_val(+) AND
    tvm_1.typ_def(+) = 'TRAN_STA' and
    t.tran_sta_typ = tve_1.typ_val and
    tve_1.typ_def = 'TRAN_STA' and
    tve_1.lang_id = 'en_US' and
    te.iso_ccy_cod = ccy1.iso_ccy_cod(+) and
    te.acct_ccy_cod = ccy2.iso_ccy_cod(+) and
    ( t.clnt_id= '1318253') and
    f.usr_actn_dt between '20020419' and '20020419' and
    (tvm_1.lang_id(+) = 'en_US')
    order by 2,6,4,7,10;


    Try that instead, and you should find that it produces the same output as your version with the UNION.
    And if the UNION-less query doesn't perform well
    (making sure you run it with the hint as specified above),
    post the TKPROF version of its EXPLAIN PLAN,
    and we'll see what's up.


  10. #10
    Join Date
    Feb 2000
    Posts
    142
    Oh, I forgot to mention that since this is a procedure, there are bind variables in it. I run this procedure through the following:
    DECLARE
    START_TIME BINARY_INTEGER := 0;
    END_TIME BINARY_INTEGER := 0;
    PROC_PROCESS_TIME BINARY_INTEGER := 0;
    PROC_ROWS_PROCESS_TIME BINARY_INTEGER := 0;
    CRIT_PROCESS_TIME BINARY_INTEGER := 0;
    CRIT_ROWS_PROCESS_TIME BINARY_INTEGER := 0;
    LBL_PROC_PROCESS_TIME BINARY_INTEGER := 0;
    LBL_ROWS_PROCESS_TIME BINARY_INTEGER := 0;
    TOTAL_PROCESS_TIME BINARY_INTEGER := 0;

    NO_OF_ROWS INTEGER := 0;

    SYSCLIENTID varchar2(50) := '1318253';
    SYSLANGUAGE varchar2(50) := 'en_US';
    SYSORACHARSET varchar2(240) := 'WE8MSWIN1252';
    CLOSED_DATE varchar2(50) := '20020419 To 20020419';
    SYSTIMEOFFSET number := 0;

    pmt_relt_cur PMTW_TYPES.pmt_relt_cur_type;
    RELTRANS_REC PMTW_TYPES.RELTRANS_REC;

    BEGIN
    DBMS_OUTPUT.PUT_LINE('SP_RELTRANS');
    DBMS_OUTPUT.Enable(1000000);
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    DBMS_OUTPUT.PUT('Sysclientid: ');
    DBMS_OUTPUT.PUT_LINE(sysclientid);
    DBMS_OUTPUT.PUT('Syslanguage: ');
    DBMS_OUTPUT.PUT_LINE(syslanguage);
    DBMS_OUTPUT.PUT('Sysoracharset: ');
    DBMS_OUTPUT.PUT_LINE(sysoracharset);
    START_TIME := DBMS_UTILITY.GET_TIME;
    PMTW_DATA_ACCESS.SP_RELTRANS(
    SYSCLIENTID ,
    CLOSED_DATE ,
    SYSLANGUAGE ,
    SYSORACHARSET ,
    SYSTIMEOFFSET,
    pmt_relt_cur);

    END_TIME := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('-------------------');
    DBMS_OUTPUT.PUT('DATA PROCEDURE Processing Time: ');
    PROC_PROCESS_TIME := (END_TIME - START_TIME)/100;
    DBMS_OUTPUT.PUT_LINE(PROC_PROCESS_TIME);
    DBMS_OUTPUT.PUT_LINE('-----------------');
    DBMS_OUTPUT.PUT_LINE('DATA PROCEDURE Rows...');
    START_TIME := DBMS_UTILITY.GET_TIME;
    NO_OF_ROWS :=0;
    LOOP
    FETCH pmt_relt_cur INTO RELTRANS_REC;
    EXIT WHEN pmt_relt_cur%NOTFOUND;
    NO_OF_ROWS := pmt_relt_cur%ROWCOUNT;
    END LOOP;
    END_TIME := DBMS_UTILITY.GET_TIME;
    CLOSE pmt_relt_cur;
    DBMS_OUTPUT.PUT('Number of Rows Fetched ');
    DBMS_OUTPUT.PUT_LINE(no_of_rows);
    DBMS_OUTPUT.PUT_LINE('-----------------');
    DBMS_OUTPUT.PUT('Rows fetch time: ');
    PROC_ROWS_PROCESS_TIME := (END_TIME - START_TIME)/100;
    DBMS_OUTPUT.PUT_LINE(PROC_ROWS_PROCESS_TIME);

    /* DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('PROCESSING CRITERIA PROCEDURE.........');
    DBMS_OUTPUT.PUT('Branch: ');
    DBMS_OUTPUT.PUT_LINE(pbranch);
    DBMS_OUTPUT.PUT('Customer: ');
    DBMS_OUTPUT.PUT_LINE(pcustomer);
    DBMS_OUTPUT.PUT('AccountGroup: ');
    DBMS_OUTPUT.PUT_LINE(pacctgrp);
    DBMS_OUTPUT.PUT('Account: ');
    DBMS_OUTPUT.PUT_LINE(paccount);
    DBMS_OUTPUT.PUT('Currency: ');
    DBMS_OUTPUT.PUT_LINE(pcurrency);
    DBMS_OUTPUT.PUT('ProductType: ');
    DBMS_OUTPUT.PUT_LINE(ProdType);
    DBMS_OUTPUT.PUT('TransactionType: ');
    DBMS_OUTPUT.PUT_LINE(ptrantyp);
    DBMS_OUTPUT.PUT('Syslanguage: ');
    DBMS_OUTPUT.PUT_LINE(syslanguage);
    DBMS_OUTPUT.PUT('Sysoracharset: ');
    DBMS_OUTPUT.PUT_LINE(sysoracharset);
    DBMS_OUTPUT.PUT('AccountCurrency: ');
    DBMS_OUTPUT.PUT_LINE(pccy_acct);
    DBMS_OUTPUT.PUT('TransactionCurrency: ');
    DBMS_OUTPUT.PUT_LINE(pccy_tran);
    DBMS_OUTPUT.PUT('SYSUSERID: ');
    DBMS_OUTPUT.PUT_LINE(SYSUSERID);
    START_TIME := DBMS_UTILITY.GET_TIME;
    BTR_DATA_ACCESS.SP_BTR_CRITERIA(
    pbranch,
    pcustomer,
    pacctgrp,
    paccount,
    pcurrency,
    prodtype,
    ptrantyp,
    syslanguage,
    sysoracharset,
    pccy_acct,
    pccy_tran,
    sysclientid,
    SYSUSERID,
    btr_crit_cur);
    END_TIME := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT('CRITERIA PROCEDURE PROCESSING TIME: ');
    CRIT_PROCESS_TIME := (END_TIME - START_TIME)/100;
    DBMS_OUTPUT.PUT_LINE(CRIT_PROCESS_TIME);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('PROCESSING CRIETERIA ROWS..............');
    START_TIME := DBMS_UTILITY.GET_TIME;
    NO_OF_ROWS :=0;
    LOOP
    FETCH BTR_CRIT_CUR INTO BTR_CRIT_REC;
    EXIT WHEN BTR_CRIT_CUR%NOTFOUND;
    NO_OF_ROWS := BTR_CRIT_CUR%ROWCOUNT;
    END LOOP;
    END_TIME := DBMS_UTILITY.GET_TIME;
    CLOSE BTR_CRIT_CUR;
    DBMS_OUTPUT.PUT('CRITERIA PROCEDURE ROWS: ');
    DBMS_OUTPUT.PUT_LINE(no_of_rows);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT('CRITERIA PROCEDURE ROWS FETCH TIME: ');
    CRIT_ROWS_PROCESS_TIME := (End_time - Start_time)/100;
    DBMS_OUTPUT.PUT_LINE(CRIT_ROWS_PROCESS_TIME);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('PROCESSING LABEL PROCEDURE............ ');
    DBMS_OUTPUT.PUT('Syslanguage: ');
    DBMS_OUTPUT.PUT_LINE(syslanguage);
    DBMS_OUTPUT.PUT('Sysoracharset: ');
    DBMS_OUTPUT.PUT_LINE(sysoracharset);
    START_TIME := DBMS_UTILITY.GET_TIME;
    SP_LBL_CASH_BAL_STMT(
    syslanguage,
    sysoracharset,
    btr_lbl_cur_type);
    END_TIME := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT('LABEL PROCEDURE PROCESSING TIME: ');
    LBL_PROC_PROCESS_TIME := (END_TIME - START_TIME) /100;
    DBMS_OUTPUT.PUT_LINE(LBL_PROC_PROCESS_TIME);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('LABEL ROWS PROCESSING TIME........');
    START_TIME := DBMS_UTILITY.GET_TIME;
    NO_OF_ROWS :=0;
    LOOP
    FETCH BTR_LBL_CUR_TYPE INTO BTR_LBL_REC;
    EXIT WHEN BTR_LBL_CUR_TYPE%NOTFOUND;
    NO_OF_ROWS := BTR_LBL_CUR_TYPE%ROWCOUNT;
    END LOOP;
    DBMS_OUTPUT.PUT('LABEL PROCEDURE: NUMBER OF ROWS: ');
    DBMS_OUTPUT.PUT_LINE(no_of_rows);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
    DBMS_OUTPUT.PUT('LABEL PROCEDURE ROWS FETCH TIME: ');
    LBL_ROWS_PROCESS_TIME := (End_time - Start_time)/100;
    DBMS_OUTPUT.PUT_LINE(LBL_ROWS_PROCESS_TIME);
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');*/

    DBMS_OUTPUT.PUT('TOTAL PROCESSING TIME OF ALL PROCEDURES AND ROWS: ');
    TOTAL_PROCESS_TIME := PROC_PROCESS_TIME + PROC_ROWS_PROCESS_TIME +
    CRIT_PROCESS_TIME + CRIT_ROWS_PROCESS_TIME +
    LBL_PROC_PROCESS_TIME + LBL_ROWS_PROCESS_TIME;
    DBMS_OUTPUT.PUT_LINE(TOTAL_PROCESS_TIME);
    END;
    /


    As you can see, the clnt_id, sysoracharset , syslanguage and closed_date are given here.


    Thanks.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width