DB2 v9.5.1 ESE on AIX v6.1
We have all tables in one tablespace with 1 bufferpool so as I am looking for ways to improve performance I conducting a number of tests for different configurations. The first test was with tables moved to separated tablespaces. Performance improvement is only 6%. So I run same this test while collecting bufferpool stats and got this:

BP_NAME TOTAL_LOG_READS TOTAL_PHYS_READS TOTHITRATIO DATAHITRATIO IDXHITRATIO SNAPSHOT_TIMESTAMP
-------------- -------------------- -------------------- ----------- ------------ ----------- -------------------
IBMDEFAULTBP 0 0 - - - 2010-03-15-12:50:34
BP_SYSCATSPC 66297 308 99.53 99.18 99.69 2010-03-15-12:50:34
BP_TBS_PROD 585277 8717 98.51 98.36 99.48 2010-03-15-12:50:34
BP_TXS_16K 81311908 4945590 93.91 63.08 99.07 2010-03-15-12:50:34
BP_16K_1 54215390 141568 99.73 99.58 99.88 2010-03-15-12:50:34
BP_32K_1 35993208 2181549 93.93 79.01 99.92 2010-03-15-12:50:34
BP_32K_2 197893067 180969 99.90 99.90 99.99 2010-03-15-12:50:34
BP_32K_3 46000908 1087366 97.63 80.45 99.37 2010-03-15-12:50:34
IBMSYSTEMBP4K 0 0 - - - 2010-03-15-12:50:34
IBMSYSTEMBP8K 0 0 - - - 2010-03-15-12:50:34
IBMSYSTEMBP16K 0 0 - - - 2010-03-15-12:50:34
IBMSYSTEMBP32K 0 0 - - - 2010-03-15-12:50:34

Id Name PageSz PA-NumPgs
1 IBMDEFAULTBP 4096 20600
2 BP_SYSCATSPC 4096 41200
3 BP_TBS_PROD 4096 41200
4 BP_TXS_16K 16384 154500
5 BP_16K_1 16384 51500
6 BP_32K_1 32768 51500
7 BP_32K_2 32768 38625
8 BP_32K_3 32768 51500
4096 IBMSYSTEMBP4K 4096 16
4097 IBMSYSTEMBP8K 8192 16
4098 IBMSYSTEMBP16K 16384 16
4099 IBMSYSTEMBP32K 32768 16


Based on this would you say that we do not need to increase/change bufferpool sizes anymore (because hitratios are high enough already)? What changes if any can I make to improve performance? Total bufferpools size is 8GB which is half of available physical memory.

I still have LOBs (11 tables) in the same tablespaces as tables. Would it improve performance (and how much) if I move LOBs to separate tablespaces?

All tables have PCTFREE=0% and all indexes 10% (default) even for tables with clustered indexes. I tried to make PCTFREE=10% for 12 tables with clustered indexes (all tables were in the same TS then) and perf. improvement was 5%. Would it improve performance if I change PCTFREE of all tables to 10% as well (mixed data warehouse and OLTP environment)?

Thanks in advance

The best way to improve system performance is to maximize the index read efficiency. Basically you want to read the minimum number of rows that you need for any statement.

Andy

High bufferpool hit ratio is sometimes misleading ... Eg. a small non-indexed table scanned a few hundred thousand times when involved in a NL Join.

As Andy says, first look at the indexes ...

I'd say performance is most frequently defined in terms of how much useful work is done per time unit. From that point of view neither BP hit ratio nor index utilization are indicative. One would need to find where most time is being spent, and try to improve that. It could be physical I/O, sorts, lock waits, query compilation, etc. Once the bottleneck is known, you can determine possible solutions and, at that point, appropriate metrics to verify them.

How often are those columns included in a SELECT? I'd place LOB's in a separate tablespace and enable file system caching for that tablespace. To compensate for the absence of the bufferpool.
A better solution would be to convice your boss to upgrade to V9.7 a.s.a.p. to benefit from the inline LOB's.

does it mean that it would not be any need to move LOBs to separate TSs in v9.7?

Depends on the size. You specify the size of inline LOB's. LOB's which are bigger are stored externally and treated as usual. Smaller LOB's will be stored like normal columns, included in the buffer pools and compressed as well.

thanks everyone