Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: performance question

    having db2 aese 9.7 fp7 on linux x-series
    having 2 tables
    idx.g_cat : 267M rows
    catmig.mig_xcat : 38M rows
    we have executed runstats on both tables
    doing a merge on these
    merge into idx.g_cat as dst using catmig.mig_xcat as src on src.docn = dst.docn when matched then update set dst.doctyp_1 = src.doctyp_1,...
    in the explain plan we could notice :
    opening the largest table - tablescan
    doing nested loop join on smaller table by index on docn (takes 10hrs to execute)
    we would presume - opening small table and looking up large table (or even doing merge scan as eq predicate and index available on both tables)
    current plan :
    Access Table Name = IDX.G_CAT ID = 35,4
    | | | | #Columns = 67
    | | | | Clustered by Dimension for Block Index Access
    | | | | Compressed Table
    | | | | Skip Inserted Rows
    | | | | May participate in Scan Sharing structures
    | | | | Relation Scan
    | | | | | Prefetch: Eligible
    | | | | Lock Intents
    | | | | | Table: Intent Exclusive
    | | | | | Block: Exclusive
    | | | | | Row : None
    | | | Nested Loop Join
    | | | | Access Table Name = CATMIG.MIG_XCAT ID = 57,261
    | | | | | Index Scan: Name = CATMIG.MIG_XCAT ID = 1
    | | | | | | Regular Index (Not Clustered)
    | | | | | | Index Columns:
    | | | | | | | 1: DOCN (Ascending)

    we have tried different scenarios with different indexes (changing the join column as being part of clustered index) - currently regular index on +DOCN for both tables
    on large table we could not do this (mdc table with different dimension)

    sortheap = automatic sheapthres_shr = automatic sheapthres=0
    physical mem 32G : 4cpu : 1 instance - 1 db and db2 only appl on this machine
    if any idea/hint can be provided..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    just forgot to indicate that in small table docn is unique also unique index created..

    while continuing research and reading other threads (Serge Rielau) he indicated to have a unique idx on this on column
    and to set current query optimization 3
    I have tried this last setting and in that case : small table is outer table
    I will do a new test to see if elapsed time changes..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Have you tried to create a statistical view joining these tables or to use optimization profiles?
    Regards,
    Mark.

Posting Permissions

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