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.