chain_cnt
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: chain_cnt

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's the query plan say?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    The plan has not changed after deletion of chained rows.

    Please help.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the most effective method is tune your view definition or sql statement

  5. #5
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Post your query with info on any indexes.


    [Edited by kris109 on 02-15-2002 at 07:57 PM]
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  6. #6
    Join Date
    Feb 2000
    Posts
    142
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  8. #8
    Join Date
    Feb 2000
    Posts
    142
    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.


  9. #9
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    I wonder whether I should quote an hourly rate for looking at this.

    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  10. #10
    Join Date
    Feb 2000
    Posts
    142
    Well, since this was causing a lot of problem, I thought that I would get some help....

    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