-
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.
-
Could somebody please help?????
Thanks.
-
Somebody help me please!!
-
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,12c
email: ocp_9i@yahoo.com
-
The tables are already analyzed :
alter table compute statistics.
What else should I do?
Thanks.
-
It is still slow.
Please help.
Thanks.
-
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.
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|