chain_cnt

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.

What's the query plan say?

The plan has not changed after deletion of chained rows.

Please help.

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

Post your query with info on any indexes.


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

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.

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?

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.

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

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

Thanks.

Just kidding.

Well I am still waiting if somebody could please help me.

Thanks.

Please let me know soon.

I request somebody to help me please.

Thanks.

Given the complexity of the query, view definition and the number of rows, 65 seconds does not seem to be too bad. However, if you want to improve the performance, you need to do some homework.

1. Run the query directly against the tables. Since you are using inline view for your view definition, you just have to add the where clauses there. Run explain plan to see whether there is improvement.

2. You have to play around with the join order. Use the most restrictive join condition first. This is true for both query and the view definition. Again, run explain plan to measure the performance.

3. Tune your indexes. Obviously you need indexes on columns(t1. trans_no, s1.acct_id etc). Again, you can play around and see whether an index actually improves query response or makes it worse.