chain_cnt

[lee]

Hi,
I am running a query which uses a view and also there is a sub-query in this query. This query took about 120 secs to execute. I queried dba_tables to check for chain_cnt and found that 2 tables have chain_cnt of 224 and 7067 resp.

I deleted the chained rows and analyzed the 2 tables again. Now, the query takes about 65 secs to execute. It fetches 1788 records. But, I am not sure if it is because of the deletion of chained rows or not. Again if I execute the same query it will take more time to execute.

Any other way or method to make the query run faster?

Thanks. Please help.

[marist89]

What's the query plan say?

[lee]

The plan has not changed after deletion of chained rows.

Please help.

[pando]

the most effective method is tune your view definition or sql statement

[kris109]

Post your query with info on any indexes.


[Edited by kris109 on 02-15-2002 at 07:57 PM]

[lee]

Here is the query:

select VTS.CLNT_ENT_NM,
VTS.CLNT_ID,
VTS.CUST_NM,
VTS.CUST_NO,
VTS.ACCT_NO,
VTS.ACCT_ID,
VTS.CUST_REF,
VTS.BNK_REF,
VTS.BNK_NM,
VTS.VAL_DT,
VTS.ENT_DT,
VTS.TRD_ENT_DT,
VTS.STMT_DT,
VTS.LAST_ENT_DT,
VTS.CR_D_IND,
VTS.PMT_TYP,
VTS.PROD_TYP,
VTS.PROD_TYP_DES,
VTS.PMT_DETL,
VTS.FTHR_REF,
VTS.ACCT_NM,
VTS.BR_COD,
VTS.BRN_NM,
VTS.TRAN_CCY,
VTS.TRAN_AMT,
VTS.TRAN_NO,
VTS.BENE,
VTS.TRAN_TYP,
VTS.BR_TAX_ID,
VTS.DETAIL,
VTS.ACT_ONLY,
VTS.AS_OF_DT,
VTS.LEGAL_TEXT,
VTS.TOTAL,
VTS.BASE_TRAN_AMT,
VTS.BASE_CCY,
VTS.LANG_ID,
VTS.BATCH_NO,
VTS.SWIFT_TRAN_CD,
VTS.SERIAL_NO,
VTS.GFCID,
VTS.CUST_BAS_NO,
VTS.CUST_PTY_ID,
VTS.TRAN_COD,
VTS.BR_PTY_ID
from VW_TRAN_SMRY VTS
WHERE ( VTS.LANG_ID = 'en_US'
AND VTS.BR_COD = '862'
AND VTS.ENT_DT between to_date('20011103000000', 'YYYYMMDDHH24MISS')
and to_date('20020214235959', 'YYYYMMDDHH24MISS')
AND VTS.VAL_DT between to_date('20011103000000', 'YYYYMMDDHH24MISS')
and to_date('20020214235959', 'YYYYMMDDHH24MISS')
AND VTS.BASE_CCY = 'USD'
AND VTS.CLNT_ID = 52325434
and exists (select 'x' from ENTITLEMENT_PRIVILEGE ENT,
ENT_USER_PROFILE_LINK EUPL
where rownum < 2
and ( ENT.CashPIbrcod = CHR(26)
or VTS.BR_COD = ENT.CashPIbrcod
or VTS.BR_COD is null)
and ( ENT.CashPIaccount = CHR(26)
or VTS.ACCT_ID = ENT.CashPIaccount
or VTS.ACCT_ID is null)
and ( ENT.CashPIcustno = -1001
or VTS.CUST_PTY_ID = ENT.CashPIcustno
or VTS.CUST_PTY_ID is null)
and ENT.status_type = 'PROCESSED'
AND EUPL.STATUS_TYPE = 'PROCESSED'
AND EUPL.PROFILE_ID = ENT.PROFILE_ID
AND ENT.STATUS_TYPE = 'PROCESSED'
AND ENT.SERV_CLAS_TYP = 'GENCASHPI'
AND EUPL.user_id = 117022 ))
ORDER BY VTS.ACCT_NO ASC ,
VTS.VAL_DT ASC



Please help. Thanks.

[pando]

you have to post view definition, indexes on all tables, explain plan, number of rows of each table

a simple sql stmt is not enough?

[lee]

Here is the view:
CREATE OR REPLACE VIEW vw_tran_smry (
lang_id,
clnt_ent_nm,
clnt_id,
cust_nm,
cust_no,
cust_pty_id,
cust_bas_no,
gfcid,
acct_no,
acct_id,
cust_ref,
bnk_ref,
bnk_nm,
val_dt,
ent_dt,
trd_ent_dt,
stmt_dt,
last_ent_dt,
cr_d_ind,
pmt_typ,
prod_typ,
prod_typ_des,
pmt_detl,
fthr_ref,
acct_nm,
br_cod,
brn_nm,
tran_ccy,
tran_amt,
tran_no,
bene,
tran_typ,
br_tax_id,
detail,
act_only,
as_of_dt,
legal_text,
total,
base_tran_amt,
base_ccy,
tran_ccy_deci_plac,
batch_no,
swift_tran_cd,
serial_no,
tran_cod,
br_pty_id )
AS
SELECT
A.lang_id,
clnt_ent_nm,
clnt_id,
cust_nm,
cust_no,
cust_pty_id,
cust_bas_no,
gfcid,
acct_no,
acct_id,
cust_ref,
bnk_ref,
bnk_nm,
val_dt,
ent_dt,
trd_ent_dt,
stmt_dt,
last_ent_dt,
cr_d_ind,
pmt_typ,
prod_typ,
prod_typ_des,
pmt_detl,
nvl(decode(A.tran_cod,'MSC',NULL,
nvl(tcm.tran_cod_des, tce.tran_cod_des)),
--BTR_UTIL.get_tran_desc(t1.tran_cod,tv.lang_id,as1.br_pty_id)),
nvl(fthr_ref,nvl(tvm2.typ_val_des, tve2.typ_val_des))) fthr_ref,
acct_nm,
br_cod,
brn_nm ,
tran_ccy,
tran_amt,
tran_no,
bene,
tran_typ,
br_tax_id,
detail,
act_only,
as_of_dt,
legal_text,
total,
base_tran_amt,
base_ccy,
tran_ccy_deci_plac,
batch_no,
swift_tran_cd,
serial_no,
A.tran_cod,
br_pty_id
from ( SELECT /*+ FIRST_ROWS ORDERED INDEX(t1 RDS_TRANSACTION_NDX_5) INDEX(s1 RDS_STATEMENT_IDX_5) */
--GENERAL ACCOUNT STATEMENT FIELDS
t1.tran_cod as tran_cod,
as1.br_pty_id as brn_id,
lg.lang_id as lang_id,
as1.clnt_nam as clnt_ent_nm,
as1.clnt_id as clnt_id,
as1.cust_lgl_nam as cust_nm,
nvl(as1.GFCID ,as1.CUST_BAS_NO) as cust_no,
decode(AS1.gfcid,null,AS1.cust_bas_PTY_id,AS1.gfcid_PTY_id) as cust_pty_id,
as1.cust_bas_no as cust_bas_no,
as1.gfcid as gfcid,
as1.acct_no as acct_no,
as1.acct_id as acct_id,
NVL(ref_no, othr_ref_no) as cust_ref,
t1.othr_ref_no as bnk_ref,
as1.bank_nam as bnk_nm,
t1.tran_val_dt as val_dt,
t1.tran_post_dt as ent_dt,
t1.tran_ent_dt as trd_ent_dt,
s1.stmt_dt as stmt_dt,
NULL as last_ent_dt,
t1.cr_dr_mrk as cr_d_ind,
t1.prod_cod as pmt_typ,
t1.prod_typ as prod_typ,
nvl(tvm1.typ_val_des,tve1.typ_val_des) as prod_typ_des,
t1.tran_detl as pmt_detl,
-- nvl(decode(t1.tran_cod,'MSC',NULL,
-- BTR_UTIL.get_tran_desc(t1.tran_cod,tv.lang_id,as1.br_pty_id)),
-- nvl(t1.fthr_ref_data,tv2.typ_val_des))
t1.fthr_ref_data as fthr_ref,
as1.acct_fam_nam as acct_nm,
as1.br_cod as br_cod,
as1.br_nam as brn_nm ,
t1.iso_ccy_cod as tran_ccy,
DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
'RC', -1.0*t1.tran_amt,
t1.tran_amt) as tran_amt,
t1.tran_no as tran_no,
DECODE(t1.CR_DR_MRK,'C ',tp2.ptcp_pty_nam,'RD', tp2.ptcp_pty_nam,
'D ', tp1.ptcp_pty_nam,'RC', tp1.ptcp_pty_nam,' ') as bene,
t1.tran_typ_cod as tran_typ,
b1.br_tax_id as br_tax_id,
t1.xtra_info as detail,
nvl(t1.act_ind,'N') as act_only,
to_date('01/01/1999','dd/mm/yyyy') as as_of_dt,
' ' as legal_text,
0 as total,
NVL(Decode(e4.math_actn,'M',
(decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))* e4.conv_rt,
(decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))/ e4.conv_rt)

,NVL((decode(e2.math_actn,'M',(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))/
e2.conv_rt,(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))*e2.conv_rt)),

NVL((decode(e6.math_actn,'M',(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))/
e6.conv_rt,(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))*e6.conv_rt)) ,

NVL( (decode(e2.math_actn,'M',(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))/
e2.conv_rt,(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))*e2.conv_rt)) ,

(decode(e6.math_actn,'M',(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))/
e6.conv_rt,(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))*e6.conv_rt) )))))*
DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
'RC', -1.0*t1.tran_amt,t1.tran_amt)
as base_tran_amt,
bc.iso_ccy_cod as base_ccy,
2 as tran_ccy_deci_plac,
rc1.batch_no as batch_no,
t1.swift_tran_cod as swift_tran_cd,
rc1.chq_ser_no as serial_no,
as1.br_pty_id as br_pty_id

from account_scpn as1,
rds_statement s1,
rds_transaction t1,
branch b1,
vw_language lg,
rds_thrd_pty_info tp1,
rds_thrd_pty_info tp2,
rds_remote_checking rc1,
currency bc,
exchange_rate e1,
exchange_rate e2,
exchange_rate e3,
exchange_rate e4,
exchange_rate e5,
exchange_rate e6,
type_values tve1,
type_values tvm1

where as1.acct_id = s1.acct_id and
s1.acct_sta_typ = 'PROCESSED' and
-- as1.acct_sta_typ = s1.acct_sta_typ and
s1.acct_id = t1.acct_id and
s1.stmt_no = t1.stmt_no and
s1.actv_ind = 'Y' and
t1.backval_ind is null and
t1.tran_no = tp1.tran_no(+) and
tp1.tpty_typ(+) = 'BENE' and
t1.tran_no = tp2.tran_no(+) and
tp2.tpty_typ(+) = 'ORDPTY' and
t1.tran_no = rc1.tran_no(+) and
as1.br_pty_id = b1.pty_id and
b1.pty_sta_typ = 'PROCESSED' and
-- as1.pty_sta_typ = b1.pty_sta_typ and

t1.iso_ccy_cod = e1.iso_ccy_cod(+) and
e1.bas_ccy_cod(+) = 'USD' and
e2.bas_ccy_cod(+) = 'USD' and
e1.prod_src_typ(+) = 'MCP' and
e2.prod_src_typ(+) = 'MCP' and
e1.actv_ind(+) = 'Y' and
e2.actv_ind(+) = 'Y' and
e2.iso_ccy_cod(+) = bc.iso_ccy_cod and

e3.bas_ccy_cod(+) = 'EUR' and
e3.prod_src_typ(+) = 'EMU' and
e3.actv_ind(+) = 'Y' and
e3.iso_ccy_cod(+) = bc.iso_ccy_cod and

e4.bas_ccy_cod(+) = 'EUR' and
e4.prod_src_typ(+) = 'EMU' and
e4.actv_ind(+) = 'Y' and
e4.iso_ccy_cod(+) = t1.iso_ccy_cod and

-- CHECK NYRS IF MCP MISSING 6/27/01 CHANGE T. HINES

e5.iso_ccy_cod(+) = t1.iso_ccy_cod and
e5.bas_ccy_cod(+) = 'USD' and
e5.prod_src_typ(+) = 'NYRS' and
e5.exch_rt_typ(+) = 'SPOT' and
e5.actv_ind(+) = 'Y' and

e6.bas_ccy_cod(+) = 'USD' and
e6.prod_src_typ(+) = 'NYRS' and
e6.exch_rt_typ(+) = 'SPOT' and
e6.actv_ind(+) = 'Y' and
e6.iso_ccy_cod(+) = bc.iso_ccy_cod and


t1.prod_typ = tve1.typ_val(+) and
tve1.typ_def(+) = 'PROD_TYP' and
tve1.lang_id(+) = 'en_US' and
((t1.prod_typ = tvm1.typ_val or
tvm1.typ_val is null ) and
tvm1.typ_def(+) = 'PROD_TYP' and
tvm1.lang_id(+) = lg.lang_id )) A,
type_values tve2,
type_values tvm2,
Transaction_code tcm
, Transaction_code tce
where A.swift_tran_cd = tve2.typ_val(+)
and tve2.typ_def(+) = 'SWIFTTRAN'
and tve2.lang_id(+) = 'en_US'
and (
A.swift_tran_cd = tvm2.typ_val(+)
and tvm2.typ_def(+) = 'SWIFTTRAN'
and tvm2.lang_id(+) = A.lang_id )

and tcm.tran_cod(+) = A.tran_cod
and tcm.lang_id(+) = A.lang_id
and tcm.pty_id(+) = A.brn_id
and TCM.TRAN_COD_STA_TYP(+) = 'PROCESSED'
and TCM.PTY_STA_TYP(+) = 'PROCESSED'
and tcm.lang_id(+) <> 'en_US'
and tcm.tran_cod(+) <> 'MSC'

and tce.tran_cod(+) = A.tran_cod
and tce.pty_id(+) = A.brn_id
and tce.TRAN_COD_STA_TYP(+) = 'PROCESSED'
and tce.PTY_STA_TYP(+) = 'PROCESSED'
and tce.lang_id(+) = 'en_US'
and tce.tran_cod(+) <> 'MSC'



Account_scpn table has 38162 rows.
rds_statement has 334709 rows.
rds_transaction has 1908111 rows.
branch has 265 rows
vw_language has 27 rows
rds_thrd_pty_info has 100866 rows
rds_remote_checking has 1354873 rows
currency has 208 rows
exchange_rate has 60827 rows
type_values has 42052 rows.

[kris109]

I wonder whether I should quote an hourly rate for looking at this.

[lee]

Well, since this was causing a lot of problem, I thought that I would get some help....

Thanks.