Total sort heap allocated= 0
Total sorts = 1
Total sort time (ms)= 8
Sort overflows = 0
Active sorts = 0
Commit statements attempted = 3
Rollback statements attempted = 0
Let transactions = Commit statements attempted + Rollback statements
attempted
Let SortsPerTX= Total sorts / transactions
Let PercentSortOverflows = Sort overflows * 100 / Total sorts
如果PercentSortOverflows 超过3%，可能说明应用中有比较严重的sort SQL。因为大量的overflows说明有大量的sort出现，为零或者小于1时比较理想的。
如果有大量的overflow出现，权宜之计是增加SORTHEAP，但是这么做只是隐藏了问题。根本解决是：要定位SQL，通过调整SQL，INDEX,clustering 来减少sort 代价。
如果SortsPerTX 大于5，说明每个交易的sort数目过多，某些应用可能执行了大量的小复合查询，不会overflow,但是有很小的时间段。但是会消耗大量的CPU。同样是要调整SQL，INDEX,clustering来解决问题。
4. TABLE ACCESS
要查出来每次查询读出的row，
1） db2 "get snapshot for database on DBNAME"
看到多少交易发生，the sum of Commit statements attempted + Rollback statements attempted
2） db2 "get snapshot for tables on DBNAME"
区分出交易读出的row。divide the number of rows read by the number of transactions (RowsPerTX).OLTP一般每次交易从一个table里面读出20 row,如果发现一个交易能读出成百上千行数据，表扫描就可能出现，可能需要看看index是否需要。简单情况下是运行runstats收集信息。
Sample output from "get snapshot for tables on DBNAME" follows:
Snapshot timestamp = 09-25-2000 4:47:09.970811
Database name= DGIDB
Database path= /fs/inst1/inst1/NODE0000/SQL00001/
Input database alias= DGIDB
Number of accessed tables= 8
Table List
Table Schema= INST1
Table Name= DGI_SALES_ LOGS_TB
Table Type= User
Rows Written= 0
Rows Read= 98857
Overflows= 0
Page Reorgs= 0
有很高的Overflows ，就需要re-org table。当一行宽度改变，可能DB2就会把一行放到不同的页中。
3. TABLESPACE ANALYSIS
tablespace snapshot对理解哪些数据被访问和怎么访问的有很大的价值。
db2 "get snapshot for tablespaces on DBNAME"
对每一个tablespace，要注意：
What is the average read time (ms)?
What is the average write time (ms)?
What percentage of the physical I/O is asynchronous (prefetched) vs. synchronous (random)?
What are the buffer pool hit ratios for each tablespace?
How many physical pages are being read each minute?
How many physical and logical pages are being read for each transaction?
对所有的tablespaces,注意：
Which tablespaces have the slowest read and write times? Why?
Containers on slow disks? Are container sizes unequal?
Are the access attributes, asynchronous versus synchronous access, consistent with expectations?
Randomly read tables should have randomly read tablespaces, meaning high synchronous read percentages, usually higher buffer pool hit ratios, and lower physical I/O rates.
对每个tablespace，要注意Prefetch size是Extent size的倍数。如果必要，可以修改tablespace的prefetch size。
显示tablespace信息：db2 "list tablespaces show detail"
显示containers 信息：db2 "list tablespace containers for N show detail"
2. BUFFER POOL OPTIMIZATION
终于讲到BufferPool了。
现在一般的系统内存都可以达到2G,4G,8G了，但是DB2缺省的IBMDEFAULTBP只有16M。所以呢，一般可以建立一个buffer pool 给SYSCATSPACE catalog tablespace, 一个buffer pool给 TEMPSPACE tablespace, 至少两个BP_RAND and BP_SEQ. 随机存取的Tablespaces 应该有一个buffer pool来应付随机的objectives,这就是 BP_RAND. 顺序存取的Tablespaces (with asynchronous prefetch I/O) 应该建立一个buffer pool给sequential objectives, BP_SEQ. 也可以建立其他的buffer pools，这要根据应用来说。比如可以建立一个足够大的buffer pool 来存放热点经常存取的数据。有时候需要为大的table建立单一的buffer pool.
标签:
db2数据库
