1. SQL COST ANALYSIS

許多情況下，一個簡單的SQL就可能讓DB2系統處于尷尬的狀態。調整參數也不能解決此問題。由于DBA很難去改變這些垃圾SQL的現狀，所以留給DBA的就是下面的情況：

(1). Change or add indexes

(2). Change clustering

(3). Change catalog statistics.

注：一個SQL語句的cost= 每次執行的資源代價*執行的次數。

目前，DBA面臨的挑戰就是要找到那些有很高cost的語句，并且盡力去減少它的代價。可以借助DB2 Explain 工具或者DB2 UDB SQL Event Monitor數據來分析SQL語句的代價。尤其是對SQL Event Monitor的數據分析，但這么做需要耗費很大的精力和時間。

一般DBA的流程是：

(1). Create an SQL Event Monitor, write to file:

$> db2 'create event monitor SQLCOST for statements write to ...'

(2). Activate the event monitor (be sure ample free disk space is available):

$> db2 'set event monitor SQLCOST state = 1'

(3). Let the application run.

(4). Deactivate the event monitor:

$> db2 'set event monitor SQLCOST state = 0'

(5). Use the DB2-supplied db2evmon tool to format the raw SQL Event Monitor data (hundreds of megabytes of free disk space may be required depending on SQL throughput rates):

$> db2evmon -db DBNAME -evm SQLCOST

> sqltrace.txt

(6). Browse through the formatted file scanning for unusually large cost numbers, a time-consuming process:

$> more sqltrace.txt

(7). Undertake a more complete analysis of the formatted file that attempts to identify unique statements (independent of literal values), each unique statement's frequency (how many times it occurred), and the aggregate of its total CPU, sort, and other resource costs. Such a thorough analysis could take a week or more on just a 30-minute sample of application SQL activity.

為了以最快的速度找到相應的SQL，我們可以考慮上文講過的一些方法：

針對第4個tip：計算每個交易從一個table里面取出的行數。如果數值很高，就可以找到相應的語句。

針對第3個tip：計算每個tablespace的asynchronous read percentage and physical I/O read rates.如果一個tablespace有很高的asynchronous read percentage 和高于平均的physical I/O read rates,那么有可能這個tablesapce里面有table scan情況。從catalog中可以找尋tablespace中相應的table(如果一個tablespace上只有一個表，那么很容易定位了)，然后從SQL Event Monitor 中尋找相關的table。這樣也可以縮小范圍。

觀察DB2 Explain信息，尋找可疑的地方。有時候，經常執行的、而且是代價比較低的語句也會瘋狂占用系統資源！

很多時候，我們可以充分借助工具！這樣能省時省力。

Staying in Tune

需要特別注意的是，性能優化不能僅僅只是消除那些好的SQL語句，也要保證合理的物理構架，確保高性能的結果、內存分配在pool和heap中，I/O都在DISk之間平衡分布。

2. BUFFER POOL OPTIMIZATION

目前一般的系統內存都可以達到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.

太小的buffer pool會導致大量的、不必要的物理I/O。太大的biffer pool有可能會產生系統paging，增加不必要的CPU管理內存開銷。

buffer pool的大與小是相對的，一個系統的buffer pool大小應該'合適的'！當達到diminishing return達到時，就是合適的。如果不是使用自動工具，應該有條理的測試buffer pool性能，比如命中率，I/O次數，物理I/O讀的比率，直到達到合適狀態。當然，應用是變化的，所以最優狀態不是不邊的，也是要定期的評估。

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

這里表示有3個container，Prefetch size是Extent size的3倍。為了最好的并行性能，最好Prefetch size是Extent size的倍數。一般倍數是container的數目。

db2 'list tablespace containers for 1 show detail'

可以看到containers的定義。

7. Locks

缺省的LOCKTIMEOUT=-1，就是說不設置lock的timeout，在OLTP中這可能是一個災難。我們要設置比較小的數值，比如設置LOCKTIMEOUT=10或者15秒。

查看命令:

db2 'get db cfg for DBNAME',

繼續查看下面的信息：

Lock timeout (sec) (LOCKTIMEOUT) = -1

要和應用人員將明白，他們是否已經在程序中可以處理timeout的情況。然后設置：

db2 'update db cfg for DBNAME using LOCKTIMEOUT 15'

可以在系統中察看lock wait的數目，lock wait time, lock list 使用的內存量。

db2 'get snapshot for database on DBNAME'

查看：

Locks held currently= 0

Lock waits= 0

Time database waited on locks (ms)= 0

Lock list memory in use (Bytes)= 576

Deadlocks detected= 0

Lock escalations= 0

Exclusive lock escalations= 0

Agents currently waiting on locks= 0

Lock Timeouts= 0

假如lock list的內存量(bytes)超過LOCKLIST 的50%，那么需要增加LOCKLIST的量，LOCKLIST是按4k計算。

8. Maximum Open Files

最大的打開文件數目

DB2限制同時打開的文件數目，數據庫參數'MAXFILOP'限定了并發打開的文件數目。如達到這個數目，DB2就會開始關閉和打開Tablespace文件，包括raw device，這樣會降低SQL反映時間和占用CPU。

使用命令來查看是否有文件關閉情況:

db2 'get snapshot for database on DBNAME'

看看其中的 'Database files closed = 0'

如果值不是零,就需要修改MAXFILOP,

db2 'update db cfg for DBNAME using MAXFILOP N'

9. Agents

需要保證有足夠的agent應付系統負載。

命令：db2 'get snapshot for database manager'

此時需要觀察“Agents waiting for a token” 或者“ Agents stolen from another application”，假如有值，就需要增加DB manager的agent值，也就是修改MAXAGENTS 和/或者 MAX_COORDAGENTS的值。

High water mark for agents registered = 7

High water mark for agents waiting for a token = 0

Agents registered= 7

Agents waiting for a token= 0

Idle agents= 5

Agents assigned from pool= 158

Agents created from empty Pool = 7

Agents stolen from another application= 0

High water mark for coordinating agents= 7

Max agents overflow= 0

10. Monitor Switches

打開Monitor Switch后才可以獲得性能方面的信息,詳細命令如下:

db2 'update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON'