Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    29

    Unhappy Unanswered: V8.1 EEE Query Problem

    UDB Ver 8.1 (64 Bit)
    ESE (Multi partitioning)

    We seen a lot of performance problems in production environment.
    Mostly, Query with 8 table joins.

    Any one come across this type of issue

    Thanks
    Racha

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    8 tables in one join is a lot. With ESE partitions, it helps if the portioning keys are the same for as many tables as possible that are used in the join. Do not use the primary key as the partitioning key for all of the tables. If you post the DDL for all 8 tables, we might be able to give some advice on how to partition them to avoid data shipping across partitions to satisfy the joins.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2003
    Posts
    29
    Thank you

    But same query with 8 table join works in V7.1 (EEE) FP4
    Only problem with V8. Even I ran Db2advisor and Explain plans.

    How the optimization works in V8? Please clarify me.

    Do you come across similar problem in your env?

    Thanks
    Racha

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do you have explains for both version 7 and version 8?
    What is your version 8 fixpack level?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2003
    Posts
    29
    V8 Fp3
    Yes I do have explain plans.

    Generally speaking I have increased Statement Heap from 8000 to 12000 --- Does it help??

    Here is the DB cfg

    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 4000
    Buffer pool size (pages) (BUFFPAGE) = 1000
    Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
    Number of extended storage segments (NUM_ESTORE_SEGS) = 0
    Max storage for lock list (4KB) (LOCKLIST) = 2000

    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 8192

    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 12000
    SQL statement heap (4KB) (STMTHEAP) = 12000
    Default application heap (4KB) (APPLHEAPSZ) = 256
    Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Percent. of lock lists per application (MAXLOCKS) = 10
    Lock timeout (sec) (LOCKTIMEOUT) = -1

    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
    Number of I/O servers (NUM_IOSERVERS) = 14
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

    Track modified pages (TRACKMOD) = OFF

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC
    Average number of active applications (AVG_APPLS) = 10
    Max DB files open per application (MAXFILOP) = 64

    Log file size (4KB) (LOGFILSIZ) = 50000

    Guru's Please help to fix the Query performance.

    Any thing to do with Optimization. Current Opt level is 7

    Thanks
    Racha

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is very difficult to help without a lot more details about what queries and the response time differences, and without seeing your complete configuration of tablespaces, containers, bufferpools, etc.

    You said that you have explains on both systems, but are they the same? If not, make sure you reorg the tables/indexes, then do runstats with full statistics. You will need to rebind any static SQL packages (not likely if you are running queries).

    Looking at your configuration, the bufferpool may be too small (but it is hard to tell without seeing your bufferpool definitions). The best way to fix this (and other potential problems) is to run the Configuration Advisor. Open the Control Center and right click on the database. Select the Configuration Advisor and answer the questions. This will do a very good job of tuning the parameters. You will want to allocate 50% - 75% of total system memory to DB2. This will give you a decent size bufferpool.

    But you can also use the AUTOCONFIGURE command (see the Command Reference manual), which is the same thing in command line mode.

    If you can’t run the Configuration Advisor or AUTOCONFIGURE, then set the bufferpool size to 50% - 75% of your total system memory. Make sure the sortheaps are adequate.

    If you have database managed temporary tablespaces, try to make them at least 2 times the size of the largest table (on each partition).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2003
    Posts
    14
    Buffer pool size (pages) (BUFFPAGE) = 1000

    how much is this???

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That is 1000 4K pages or 4MB. I am not sure if the DB config shows all bufferpools or just the default bufferpool. But if that is the only bufferpool, it is set to the default and is very small. The total of all bufferpools should probably be at least 50% of real system memory. The 64 bit DB2 is not practically constrained by addressable memory limits the same way 32 bit DB2 is constrained.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Oct 2003
    Posts
    29
    Thanks!!!

    we have 6 bufferpools with 80000 16k pages. All tsp are 16k which uses the above said bufferpools.

    The default bufferpool 1000 pages used by Temp, Syscat and Userspace1 which uses default IBM BP.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That's what I figured. The DB config only show the default bufferpool value.

    However, I suspect that you have way too many bufferpools. You would be better off with fewer and larger bufferpools (total size of all bufferpools the same). I would suggest 2 (or 3 max) bufferpools:

    - Small and medium size tables, all indexes
    - large tables

    If your tablespaces have 16K bufferpools, you should have a large temporary tablespace with 16K pages. If you have the temporary tablespace defined as DMS, it should be at least twice as large as the largest table (if at all possible). SMS will allocate space as needed, but make sure there is room to allocate very large tempspace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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