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

    Unanswered: performance issue

    db2 ese 9.5 fp9 on P/linux
    partitioned table : legal.a1_metadata
    db2 describe table legal.a1_metadata
    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    ID SYSIBM BIGINT 8 0 No
    GENERIC_ID SYSIBM BIGINT 8 0 No
    ...
    TIMESTAMP_LEGALLOG SYSIBM TIMESTAMP 10 0 Yes
    partitioned on TIMESTAMP_LEGALLOG by month (3 months of data)
    about 120M rows by partition
    global unique index on generic_id
    when doing select * from table where generic_id=xx
    instant reply = ok
    when doing select * from table where generic_id in (xx,aa,bb,cc)
    upto/including 4 values : immediate answer =ok
    when specifying 5 values or more in in-clause the elapsed time is 5min and we see in snapshot we read 38M rows
    the explain plan for equal predicate or in predicate is almost the same
    it does not change if we specify 4 or 5 values for in-clause
    the id we specify are numbers that follow each other -
    if we execute the query twice=same time - probably because bufferpool has been flushed by this nbr of read pages..
    select *
    from legal.A1_metadata
    where generic_id in ( 767876478, 767876479, 767876480, 767876481,
    767876482, 767876483, 767876484, 767876485, 767876486,
    767876487, 767876488, 767876489, 767876490, 767876491,
    767876492, 767876493, 767876494, 767876495)

    Access Table Name = LEGAL.A1_METADATA ID = -6,-32761
    | Index Scan: Name = LEGAL.A1_METADATA_GENERIC_ID ID = 2
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: GENERIC_ID (Ascending)
    | #Columns = 19
    | Data-Partitioned Table
    | Skip Deleted Rows
    | Avoid Locking Committed Data
    | All data partitions will be accessed
    | #Key Columns = 0
    | | Start Key: Beginning of Index
    | | Stop Key: End of Index
    | Data Prefetch: Eligible 0
    | Index Prefetch: None
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Table Constructor
    | | 18-Row(s)
    | Insert Into Sorted Temp Table ID = t1
    | | #Columns = 1
    | | #Sort Key Columns = 1
    | | | Key 1: (Ascending)
    | | Sortheap Allocation Parameters:
    | | | #Rows = 18.000000
    | | | Row Width = 8
    | | Piped
    | Access Temp Table ID = t1
    | | #Columns = 1
    | | Relation Scan
    | | | Prefetch: Eligible
    anyone any idea what could be happening or why the behavior is different for 4 or 5 values in in-clause
    thanks for all comment
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Merge join? You're not showing the entire query, are you?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes this is the query
    select *
    from legal.A1_metadata
    where generic_id in ( 767876478, 767876479, 767876480, 767876481,
    767876482, 767876483, 767876484, 767876485, 767876486,
    767876487, 767876488, 767876489, 767876490, 767876491,
    767876492, 767876493, 767876494, 767876495);
    as if db2 was creating a temp table to put the supplied values and doing join with real table
    as with the setting DB2_INLIST_TO_NLJN - we do not have this setting active
    Last edited by przytula_guy; 09-20-12 at 09:48.
    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

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I even tested this in small environment and getting same symptom for no rows found
    (0)[db2inst1@alx00005 ~]$ date;db2 -tvf tt;date
    Thu Sep 20 15:18:56 CEST 2012
    select * from legal.A1_metadata where generic_id in ( 767876478, 67876479,767876494)
    ID GENERIC_ID REQUESTER_SECTOR_CODE REQUESTER_INSTITUTION_TYPE USER_REQUESTER SUPPLIER_SECTOR_CODE SUPPLIER_INSTITUTION_TYPE FORM_IDENTIFICATION FORM_VARIANT REQUEST_CLASS QUALITY_CODE RETURN_CODE_A1_NETWORK RETURN_CODE_A1_APPLICATION RETURN_CODE_A1_FLUX MODE RECEIVER_SECTOR_CODE RECEIVER_INSTITUTION_TYPE TISTA TIMESTAMP_LEGALLOG
    -------------------- -------------------- --------------------- -------------------------- -------------- -------------------- ------------------------- ------------------- ------------ ------------- ------------ ---------------------- -------------------------- ------------------- ---- -------------------- ------------------------- -------------------------- --------------------------

    0 record(s) selected.


    Thu Sep 20 15:18:56 CEST 2012
    (0)[db2inst1@alx00005 ~]$ vi tt
    (0)[db2inst1@alx00005 ~]$ date;db2 -tvf tt;date
    Thu Sep 20 15:19:09 CEST 2012
    select * from legal.A1_metadata where generic_id in ( 767876478, 767876479,767876494,767876495)

    ID GENERIC_ID REQUESTER_SECTOR_CODE REQUESTER_INSTITUTION_TYPE USER_REQUESTER SUPPLIER_SECTOR_CODE SUPPLIER_INSTITUTION_TYPE FORM_IDENTIFICATION FORM_VARIANT REQUEST_CLASS QUALITY_CODE RETURN_CODE_A1_NETWORK RETURN_CODE_A1_APPLICATION RETURN_CODE_A1_FLUX MODE RECEIVER_SECTOR_CODE RECEIVER_INSTITUTION_TYPE TISTA TIMESTAMP_LEGALLOG
    -------------------- -------------------- --------------------- -------------------------- -------------- -------------------- ------------------------- ------------------- ------------ ------------- ------------ ---------------------- -------------------------- ------------------- ---- -------------------- ------------------------- -------------------------- --------------------------

    0 record(s) selected.


    Thu Sep 20 15:20:14 CEST 2012

    first result is immediate
    second result takes about 1minute for 0 rows found
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Could you post the two execution plans (preferably using db2exfmt)?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by przytula_guy View Post
    [...]
    I don't have a good explanation for what you are experiencing. Does

    Code:
    select x.* from legal.A1_metadata x join ( values 767876478, 767876479,767876494,767876495 ) y(generic_id) on x.generic_id = y.generic_id
    improve things? As a sidenote you can use:

    Code:
    time db2 -tf ...
    
    instead of
    
    date; db2 -tf ...; date
    --
    Lennart

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for all update

    for nick ;
    the explain of long running query
    DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool

    ******************** DYNAMIC ***************************************

    ==================== STATEMENT ==========================================

    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = No
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
    "DB2INST1"


    Statement:

    select *
    from legal.A1_metadata
    where generic_id in ( 767876478, 767876479, 767876480, 767876481,
    767876482, 767876483, 767876484, 767876485, 767876486,
    767876487, 767876488, 767876489, 767876490, 767876491,
    767876492, 767876493, 767876494, 767876495)


    Section Code Page = 1208

    Estimated Cost = 64.301086
    Estimated Cardinality = 17.999998

    Access Table Name = LEGAL.A1_METADATA ID = -6,-32761
    | Index Scan: Name = LEGAL.A1_METADATA_GENERIC_ID ID = 2
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: GENERIC_ID (Ascending)
    | #Columns = 19
    | Data-Partitioned Table
    | Skip Deleted Rows
    | Avoid Locking Committed Data
    | All data partitions will be accessed
    | #Key Columns = 0
    | | Start Key: Beginning of Index
    | | Stop Key: End of Index
    | Data Prefetch: Eligible 0
    | Index Prefetch: None
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Table Constructor
    | | 18-Row(s)
    | Insert Into Sorted Temp Table ID = t1
    | | #Columns = 1
    | | #Sort Key Columns = 1
    | | | Key 1: (Ascending)
    | | Sortheap Allocation Parameters:
    | | | #Rows = 18.000000
    | | | Row Width = 8
    | | Piped
    | Access Temp Table ID = t1
    | | #Columns = 1
    | | Relation Scan
    | | | Prefetch: Eligible
    Return Data to Application
    | #Columns = 19

    End of section
    for the fast query
    select *
    from legal.A1_metadata
    where generic_id in ( 767876478, 767876479, 767876480, 767876481)


    Section Code Page = 1208

    Estimated Cost = 64.287239
    Estimated Cardinality = 4.000000

    Access Table Name = LEGAL.A1_METADATA ID = -6,-32761
    | Index Scan: Name = LEGAL.A1_METADATA_GENERIC_ID ID = 2
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: GENERIC_ID (Ascending)
    | #Columns = 19
    | Data-Partitioned Table
    | Skip Deleted Rows
    | Avoid Locking Committed Data
    | All data partitions will be accessed
    | #Key Columns = 0
    | | Start Key: Beginning of Index
    | | Stop Key: End of Index
    | Data Prefetch: Eligible 0
    | Index Prefetch: None
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Table Constructor
    | | 4-Row(s)
    | Insert Into Sorted Temp Table ID = t1
    | | #Columns = 1
    | | #Sort Key Columns = 1
    | | | Key 1: (Ascending)
    | | Sortheap Allocation Parameters:
    | | | #Rows = 4.000000
    | | | Row Width = 8
    | | Piped
    | Access Temp Table ID = t1
    | | #Columns = 1
    | | Relation Scan
    | | | Prefetch: Eligible
    Return Data to Application
    | #Columns = 19

    End of section
    Lennart = I will try the proposed query and use your syntax
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Interesting. I would still have liked to see the db2exfmt output...

    Did you try to rebuild the index on generic_id? Or at least look at the reorgchk output for it?
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update
    as in accept we had the same problem - I dropped the index/created the index and executed runstats on this table/index
    this problem was resolved in this environment.
    I have not dropped yet the index in prod and will publish the formatted output of explain tables if needed...
    stats are up-to-date and reorgchk only indicates * for F6
    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

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    That is interesting.
    Did you compare the access plan between the test and prod?
    if you could ,plz provide the following information:
    1. output of the db2batch with -i complete -o p 5 e yes
    2. output of the db2exfmt with -d <dbname> -1
    3, output of the db2look -d <dbname> -t .... -e -m

    I am wonder why db2 coverts in list to merge join?
    The nl join must be the best choice in this env。
    thx

  11. #11
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update
    today is not friday 13th but anyhow
    trying to run the db2batch and : immediate answer..
    * Elapsed Time is: 0.084076 seconds (complete)

    the access plan is different and as suggested by "fengsun2" : nl join must be the best choice in ...

    Statement:

    select *
    from legal.A1_metadata
    where generic_id in ( 767876478, 767876479, 767876480 , 767876481 ,
    767876482, 767876483, 767876484, 767876485, 767876486,
    767876487, 767876488, 767876489, 767876490, 767876491,
    767876492, 767876493, 767876494, 767876495 )


    Section Code Page = 1208

    Estimated Cost = 77.432457
    Estimated Cardinality = 17.999998

    Table Constructor
    | 18-Row(s)
    Nested Loop Join
    | Access Table Name = LEGAL.A1_METADATA ID = -6,-32761
    | | Index Scan: Name = LEGAL.A1_METADATA_GENERIC_ID ID = 2
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: GENERIC_ID (Ascending)
    | | #Columns = 19
    | | Data-Partitioned Table
    | | Single Record
    | | Fully Qualified Unique Key
    | | Skip Deleted Rows
    | | All data partitions will be accessed

    I have no idea why this has changed : table is incremental loaded each day - each day runstats-.. same procedure - no detach partition.. nothing

    anyhow : I will try to follow this behavior..
    many thanks for all updates....
    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

Posting Permissions

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