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"
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就会把一行放到不同的页中。
5. SORT MEMORY
OLTP应该没有大规模的sort，因为sort会消耗大量的CPU, I/O和时间。
缺省的SORTHEAP = 256*4K=1M，一般是足够了。应该知道sort overflows 的数目和每个交易的sort number。
Db2 "get snapshot for database on DBNAME"
察看如下项目：
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来解决问题。
6. Temporary Tablespaces
临时表空间一般要有3个containers在不同的disk上，可以实现并行I/O，提高sorts, hash joins,或者其他在TEMPSPACE上的动作的性能。
db2 "list tablespaces show detail"，可查看临时表空间的container：
Tablespace ID= 1
Name= TEMPSPACE1
Type= System managed space
Contents= Temporary data
State= 0x0000
Detailed explanation: Normal
Total pages= 1
Useable pages= 1
Used pages= 1
Free pages= Not applicable
High water mark (pages)= Not applicable
Page size (bytes)= 4096
Extent size (pages)= 32
Prefetch size (pages)= 96
Number of containers= 3
<<上一页
1
2
3
下一页>>
