Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Nov 2008
    Posts
    33

    Unanswered: Not in Query tuning

    hi ,
    One of my produciton job is running very long that is about 30 hours and job abending with the table space issue.Adding space is not working out and there is an opportunity to tune the query.So I have modified the query and but the query is really running long about 20hours and completing fine.Can any body please help me out tuning the below query in a efficient manner that should be able to complete soon.

    Out of 6 tables below three are very huge tables.
    Record coutn as follows
    Code:
    CRCD_ACCT_DIM - 615671320
    BNKCD_CORR_FCT- 2979
    SETL_CHBK_FCT -  60453
    SLS_RCPT_RTRV_FCT-507105
    STLTR_ACCT_FCT -4542838640
    EFT_AUTH_FCT- 1881171580
    Production Query:
    ------------------
    Code:
    SELECT 
    A.CRCD_ACCT_DIM_I, A.CRCD_ACCT_REF_I AS CRCD_ACCT_REF_I, A.CR_ACCT_PLN_C, A.DB_CD_ACCT_TYPE_C, A.CRBIN_I, A.SETL_ACCT_TYPE_C, A.RFSH_PGM_I, A.RFSH_TS 
    FROM CRCD_ACCT_DIM A
    where A.CRCD_ACCT_DIM_I not in 
    (select CRCD_ACCT_DIM_I  from BNKCD_CORR_FCT
    UNION ALL
    select CRCD_ACCT_DIM_I  from SETL_CHBK_FCT 
    UNION ALL
    select CRCD_ACCT_DIM_I from SLS_RCPT_RTRV_FCT 
    UNION ALL
    select CRCD_ACCT_DIM_I from STLTR_ACCT_FCT 
    UNION ALL
    select CRCD_ACCT_DIM_I from EFT_AUTH_FCT )
    AND months_between(SYSDATE, A.RFSH_TS) > 36
    Modified query
    -------------
    Code:
    SELECT 
    A.CRCD_ACCT_DIM_I, A.CRCD_ACCT_REF_I AS CRCD_ACCT_REF_I, A.CR_ACCT_PLN_C, A.DB_CD_ACCT_TYPE_C, A.CRBIN_I, A.SETL_ACCT_TYPE_C, A.RFSH_PGM_I, A.RFSH_TS 
    FROM CRCD_ACCT_DIM A 
    Where (Not Exists 
    (select  B.CRCD_ACCT_DIM_I from BNKCD_CORR_FCT B Where B.CRCD_ACCT_DIM_I= A.CRCD_ACCT_DIM_I) AND NOT EXISTS 
    (select K.CRCD_ACCT_DIM_I from SETL_CHBK_FCT K Where K.CRCD_ACCT_DIM_I=A.CRCD_ACCT_DIM_I) AND NOT EXISTS 
    (select D.CRCD_ACCT_DIM_I from SLS_RCPT_RTRV_FCT D Where D.CRCD_ACCT_DIM_I=A.CRCD_ACCT_DIM_I) AND NOT EXISTS 
    (select E.CRCD_ACCT_DIM_I from STLTR_ACCT_FCT E Where E.CRCD_ACCT_DIM_I=A.CRCD_ACCT_DIM_I) AND NOT EXISTS
    (select F.CRCD_ACCT_DIM_I from EFT_AUTH_FCT F Where F.CRCD_ACCT_DIM_I=A.CRCD_ACCT_DIM_I)) 
    AND months_between(SYSDATE, A.RFSH_TS) > 36
    Last edited by anjan.k; 01-28-10 at 21:01. Reason: in complete sentence

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN for both SQL

    Code:
    SELECT a.crcd_acct_dim_i,
           a.crcd_acct_ref_i AS crcd_acct_ref_i,
           a.cr_acct_pln_c,
           a.db_cd_acct_type_c,
           a.crbin_i,
           a.setl_acct_type_c,
           a.rfsh_pgm_i,
           a.rfsh_ts
    FROM   crcd_acct_dim a
    WHERE  a.crcd_acct_dim_i NOT IN (SELECT crcd_acct_dim_i
                                     FROM   bnkcd_corr_fct
                                     UNION ALL
                                     SELECT crcd_acct_dim_i
                                     FROM   setl_chbk_fct
                                     UNION ALL
                                     SELECT crcd_acct_dim_i
                                     FROM   sls_rcpt_rtrv_fct
                                     UNION ALL
                                     SELECT crcd_acct_dim_i
                                     FROM   stltr_acct_fct
                                     UNION ALL
                                     SELECT crcd_acct_dim_i
                                     FROM   eft_auth_fct)
           AND Months_between(SYSDATE,a.rfsh_ts) > 36
    Code:
    SELECT a.crcd_acct_dim_i,
           a.crcd_acct_ref_i AS crcd_acct_ref_i,
           a.cr_acct_pln_c,
           a.db_cd_acct_type_c,
           a.crbin_i,
           a.setl_acct_type_c,
           a.rfsh_pgm_i,
           a.rfsh_ts
    FROM   crcd_acct_dim a
    WHERE  (NOT EXISTS (SELECT b.crcd_acct_dim_i
                        FROM   bnkcd_corr_fct b
                        WHERE  b.crcd_acct_dim_i = a.crcd_acct_dim_i)
            AND NOT EXISTS (SELECT k.crcd_acct_dim_i
                            FROM   setl_chbk_fct k
                            WHERE  k.crcd_acct_dim_i = a.crcd_acct_dim_i)
            AND NOT EXISTS (SELECT d.crcd_acct_dim_i
                            FROM   sls_rcpt_rtrv_fct d
                            WHERE  d.crcd_acct_dim_i = a.crcd_acct_dim_i)
            AND NOT EXISTS (SELECT e.crcd_acct_dim_i
                            FROM   stltr_acct_fct e
                            WHERE  e.crcd_acct_dim_i = a.crcd_acct_dim_i)
            AND NOT EXISTS (SELECT f.crcd_acct_dim_i
                            FROM   eft_auth_fct f
                            WHERE  f.crcd_acct_dim_i = a.crcd_acct_dim_i))
           AND Months_between(SYSDATE,a.rfsh_ts) > 36
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is crcd_acct_dim_i indexed in every table? and are statistics current on the tables?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2008
    Posts
    33
    Thanks for your Quick responce
    Details of the OS

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    crcd_acct_dim_i is indexed on the all tables.But i am not sure of the statistics are upto date.Could you please let me know how to check that?





    Production Query Explian Plan
    -----------------------------

    SELECT STATEMENT ALL_ROWS 268322 1 92
    PX COORDINATOR
    PX SEND(QC (RANDOM)) SYS.:TQ10003@:Q1003 268322 1 92
    BUFFER(SORT) 1 92
    NESTED LOOPS(ANTI) 268322 1 92
    NESTED LOOPS(ANTI) 268319 1 85
    NESTED LOOPS(ANTI) 268319 1 78
    HASH JOIN(ANTI) 268319 37 2627
    HASH JOIN(ANTI) 268301 16062 1027968
    PX RECEIVE 147609 30783566 1754663262
    PX SEND(HASH) SYS.:TQ10000@:Q1000 147609 30783566 1754663262
    PX BLOCK(ITERATOR) 147609 30783566 1754663262 1 4 11
    TABLE ACCESS(FULL) CSEMGR.CRCD_ACCT_DIM@:Q1000 ANALYZED 147609 30783566 1754663262 1 4 11
    PX RECEIVE 20337 1881171580 13168201060
    PX SEND(HASH) SYS.:TQ10001@:Q1001 20337 1881171580 13168201060
    PX PARTITION RANGE(ALL) 20337 1881171580 13168201060 1 147 15
    INDEX(FULL SCAN) CSEMGR.EFT_AUTH_FCT_IE3@:Q1001 ANALYZED 20337 1881171580 13168201060 1 147 15
    PX RECEIVE 17 60453 423171
    PX SEND(HASH) SYS.:TQ10002@:Q1002 17 60453 423171
    PX BLOCK(ITERATOR) 17 60453 423171
    INDEX(FAST FULL SCAN) CSEMGR.SETL_CHBK_FCT_PK@:Q1002 ANALYZED 17 60453 423171
    INDEX(RANGE SCAN) CSEMGR.BNKCD_CORR_FCT_IE1@:Q1003 ANALYZED 1 2971 20797
    INDEX(RANGE SCAN) CSEMGR.SLS_RCPT_RTRV_FCT_IE1@:Q1003 ANALYZED 1 507105 3549735
    PARTITION RANGE(ALL) 2 4542838640 31799870480 1 117 23
    INDEX(RANGE SCAN) CSEMGR.STLTR_ACCT_FCT_IE4@:Q1003 ANALYZED 2 4542838640 31799870480 1 117 23

    Modified Query Explain Plan:
    ------------------------

    SELECT STATEMENT ALL_ROWS 268322 1 92
    PX COORDINATOR
    PX SEND(QC (RANDOM)) SYS.:TQ10003@:Q1003 268322 1 92
    BUFFER(SORT) 1 92
    NESTED LOOPS(ANTI) 268322 1 92
    NESTED LOOPS(ANTI) 268319 1 85
    NESTED LOOPS(ANTI) 268319 1 78
    HASH JOIN(ANTI) 268319 37 2627
    HASH JOIN(ANTI) 268301 16062 1027968
    PX RECEIVE 147609 30783566 1754663262
    PX SEND(HASH) SYS.:TQ10000@:Q1000 147609 30783566 1754663262
    PX BLOCK(ITERATOR) 147609 30783566 1754663262 1 4 11
    TABLE ACCESS(FULL) CSEMGR.CRCD_ACCT_DIM@:Q1000 ANALYZED 147609 30783566 1754663262 1 4 11
    PX RECEIVE 20337 1881171580 13168201060
    PX SEND(HASH) SYS.:TQ10001@:Q1001 20337 1881171580 13168201060
    PX PARTITION RANGE(ALL) 20337 1881171580 13168201060 1 147 15
    INDEX(FULL SCAN) CSEMGR.EFT_AUTH_FCT_IE3@:Q1001 ANALYZED 20337 1881171580 13168201060 1 147 15
    PX RECEIVE 17 60453 423171
    PX SEND(HASH) SYS.:TQ10002@:Q1002 17 60453 423171
    PX BLOCK(ITERATOR) 17 60453 423171
    INDEX(FAST FULL SCAN) CSEMGR.SETL_CHBK_FCT_PK@:Q1002 ANALYZED 17 60453 423171
    INDEX(RANGE SCAN) CSEMGR.BNKCD_CORR_FCT_IE1@:Q1003 ANALYZED 1 2971 20797
    INDEX(RANGE SCAN) CSEMGR.SLS_RCPT_RTRV_FCT_IE1@:Q1003 ANALYZED 1 507105 3549735
    PARTITION RANGE(ALL) 2 4542838640 31799870480 1 117 23
    INDEX(RANGE SCAN) CSEMGR.STLTR_ACCT_FCT_IE4@:Q1003 ANALYZED 2 4542838640 31799870480 1 117 23

    Sorry if it is confusing
    Last edited by anjan.k; 01-27-10 at 17:13.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/oracle/10316...s-posters.html

    dBforums - BB Code List

    without formatting, information is not understandable.

    It appears partitioning exists.
    If so provide details or last DDL for tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In some cases MINUS can be used instead of using NOT IN.
    You might want to consider if this is a viable alternative for your situation, requirements & data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2008
    Posts
    33
    Hi ,

    Please find the attached images Explian plan related to the Queries.Pleaes let me kwnow if you need any other information
    Attached Thumbnails Attached Thumbnails Production EP.GIF   Modified EP.GIF  

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anjan.k View Post
    AND months_between(SYSDATE, A.RFSH_TS) > 36
    One thing you could do is to change the above to
    Code:
    and a.rfsh_ts < sysdate - 36*30
    and make sure there is an index on crcd_acct_dim (rfsh_ts, crcd_acct_dim_i) (or may be the other way around - try both).
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Nov 2008
    Posts
    33
    You mean to say try that new code in the both queries?

  11. #11
    Join Date
    Nov 2008
    Posts
    33
    here is the details of the table CRCD_ACCT_DIM

    OWNER CSEMGR
    TABLE_NAME CRCD_ACCT_DIM
    TABLESPACE_NAME
    CLUSTER_NAME
    IOT_NAME
    STATUS VALID
    PCT_FREE
    PCT_USED
    INI_TRANS
    MAX_TRANS
    INITIAL_EXTENT
    NEXT_EXTENT
    MIN_EXTENTS
    MAX_EXTENTS
    PCT_INCREASE
    FREELISTS
    FREELIST_GROUPS
    LOGGING
    BACKED_UP N
    NUM_ROWS 615671320
    BLOCKS 3370582
    EMPTY_BLOCKS 127
    AVG_SPACE 1667
    CHAIN_CNT 0
    AVG_ROW_LEN 73
    AVG_SPACE_FREELIST_BLOCKS 0
    NUM_FREELIST_BLOCKS 0
    DEGREE 8
    INSTANCES 1
    CACHE N
    TABLE_LOCK ENABLED
    SAMPLE_SIZE 30783566
    LAST_ANALYZED 11-DEC-09
    PARTITIONED YES
    IOT_TYPE
    TEMPORARY N
    SECONDARY N
    NESTED NO
    BUFFER_POOL
    ROW_MOVEMENT DISABLED
    GLOBAL_STATS YES
    USER_STATS NO
    DURATION
    SKIP_CORRUPT DISABLED
    MONITORING YES
    CLUSTER_OWNER
    DEPENDENCIES DISABLED
    COMPRESSION
    DROPPED NO
    COMMENTS

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OraFAQ Forum: Performance Tuning How to tune SQL or Identify Performance Problem and Bottleneck

    please post formatted EXPLAIN PLAN for both SQL
    please post DDL for all tables & indexes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Nov 2008
    Posts
    33
    hi Anacedent..Did you get a chance to look in to my Explain plans thanks

  14. #14
    Join Date
    Nov 2008
    Posts
    33
    Just small clarification the attached Explain plans doesnt show any.please let me know

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm sorry...your batch job is just a SELECT?

    What are you actually trying to accomplish?


    >> So I have modified the query and but the query is really running long about 20hours and completing fine


    <cough>
    That's some good sheet
    </cough>

    20 Hours?

    "We're on the road to Bangkok...aboard the Thailand express...we'll hit some stops along the way...we only stop for the best"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •