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

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.

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

Do you have explains for both version 7 and version 8?
What is your version 8 fixpack level?

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

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).

Buffer pool size (pages) (BUFFPAGE) = 1000

how much is this???

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.

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.

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.