Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Colorado
    Posts
    10

    Unanswered: performance problem driving us crazy

    Writing a pretty heavy query, trying to limit data base on coniditions.
    Three table join on multiple columns all part of primary key.
    tables are 1 - common table, 2 - line table, 3 - temp table created in previous sql which consits only of all primary keys of items we want from common and line tables

    so outline:

    select common.values, line.values from
    temp table, common.table, line.table
    where
    temp table.primary key = common table.primary key and
    common table.primary key = line table. primary key

    the key is made up of 5 columns.

    when we join the common to the line the data is back in a flash, however, it returns more data then we will ever need
    as soon as we join in the temp table (1/10th the data compared to volume in the common) the query will run forever!!! The main purpose of the temp data is to filter out data we do not need (ie 1/10 of the common table)

    right after the temp table is created (previous step) runstats is handled like this
    "RUNSTATS ON TABLE DC.TMP_DCS_EXT_2 AND INDEXES ALL SHRLEVEL CHANGE"

    tried joining just the common and temp table performance is just as horrible.
    any ideas on where to look?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: performance problem driving us crazy

    Can you do a

    db2 explain all with snapshot for <sql statement>

    db2 "select max(explain_time) from explain_instance"

    db2exfmt -d sample -g -o explain.out -w -1 -# 0
    (If it prompts for other options, hit enter to accept defaults)

    If you have not created explain tables before , create explain tables using the definitions in sqllib\misc\EXPLAIN.DDL

    If you attach the output of the above command along with the table defintion, someone here may be able to help
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Location
    Colorado
    Posts
    10

    Re: performance problem driving us crazy

    yes I can

    db2 explain all with snapshot for <sql statement>

    db2 "select max(explain_time) from explain_instance"

    explain max(explain_time)
    results
    1
    --------------------------
    2004-01-26-12.22.27.486488

    db2exfmt -d sample -g -o explain.out -w -1 -# 0
    see attachments

    If you attach the output of the above command along with the table defintion, someone here may be able to help
    see attachments
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2004
    Posts
    2
    try:
    1. check all indexes. if they cover your joins
    2. run index advise(see script below).
    3. can you get db2 get db cfg and get dbm cfg.
    and db2level, oslevel, lsconf (aix)
    4. ddls for indexes.
    5. check if step 3,7,5 are covered with good indexes.


    :
    sql_file=$1
    set -x
    db2advis -d db2prqa -t 5 -o ${sql_file}.out -i $sql_file -a db2inst1/db2inst22
    exit;

    for your sizes, looks, like you need to tune your parameters too:
    all this is too small.
    Parallelism: None
    CPU Speed: 5.668131e-07
    Comm Speed: 100
    Buffer Pool size: 1000
    Sort Heap size: 256
    Database Heap size: 1200
    Lock List size: 100
    Maximum Lock List: 10
    Average Applications: 1
    Locks Available: 1130

  5. #5
    Join Date
    Jan 2004
    Posts
    2
    Also run runstats on tables and indexes involved if you did not yet.

  6. #6
    Join Date
    Aug 2003
    Location
    Colorado
    Posts
    10

    performance contd.

    Originally posted by ykten13
    try:
    1. check all indexes. if they cover your joins
    done
    2. run index advise(see script below).
    done
    3. can you get db2 get db cfg and get dbm cfg.
    and db2level, oslevel, lsconf (aix)
    not sure what your asking here, are you looking for configurations?
    4. ddls for indexes.
    don't have a clue on how I would go about looking for this
    5. check if step 3,7,5 are covered with good indexes.
    here is where I'm at a lose also, I see that 3,7,5 are table scans but I don't really know how to read the explain. But there is an index available for everything in the where

    stats are run on the temp table everytime they are recreated and stats have been run against other 'static' tables




    :
    sql_file=$1
    set -x
    db2advis -d db2prqa -t 5 -o ${sql_file}.out -i $sql_file -a db2inst1/db2inst22
    exit;

    for your sizes, looks, like you need to tune your parameters too:
    all this is too small.
    Parallelism: None
    CPU Speed: 5.668131e-07
    Comm Speed: 100
    Buffer Pool size: 1000
    Sort Heap size: 256
    Database Heap size: 1200
    Lock List size: 100
    Maximum Lock List: 10
    Average Applications: 1
    Locks Available: 1130

Posting Permissions

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