如何調整DB2資料庫效能實用技巧分享

如何調整DB2 資料庫
效能實用技巧分享，呵呵，廢話就多說了，看資料了，可能對你有有所協助，學習咯

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?
  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"
查看執行計畫：
db2expln:
db2expln -d dbname -c pkgOwner -p pkgNmae -o expln.out
dynexpln:
dynexpln -d eos -q "select * from eosmenu" -g -t
dynexpln -d eos -f query.sql -o out.txt
更新統計資料：
runstats:
db2 runstats on table songxn.eosmenu and indexs songxn.ix1,songxn.ix2 allow read access
db2 runstats on table songxn.eosmenu with distribution and detailed index all
調整最佳化等級：
(0,1,2,3,5,7,9)：
更改DFT_QUERYOPT(db cfg)， 預設為5
SQL:set current query optimization = 3
索引：
建立索引：
create unique index ind1 on vicky.staff ( dept, lastname )
create unique index ind2 on vicky.emplyee ( empno ) include ( lastname, salary)
include中的欄位不列入排序範圍
叢集索引：
記錄與索引的順序一致
cretae index ind3 on vicky.staff ( dept ) CLUSTER
SCAN MODE:
Index Scan,Full Index Scan(Index only Access),Relation Scan
查詢索引：
select indname from syscat.indexes where tabname=’customer’
索引建議器：
db2advis -d dbname -i query.sql | -s "sql stmt" -o advis.out
在資料頁中預留空間：
alter table vicky.staff PCTFREE 30（預留30％）
load from staff.ixf of ixf modified by pagefreespace=30 replace into vicky.staff
緩衝池與IO
預設為IBMDEFAULTBP
建立緩衝池：
db2 create bufferpool bpname size 1000 pagesize 4k
alter tablespace tbname bufferpool bpname
當髒頁超過CHNGPGS_THRESH(%,db cfg)時，緩衝池將被清空並寫回
延展緩衝池:
ESTORE_SEG_SZ& NUM_ESTORE_SEGS(db cfg)
db2 alter bufferpool ibmdefaultbp [ not ] extended storage
I/O SERVER:
NUM_IOSERVERS(db cfg):一般為磁碟數+2
資料重整:
REORGCHK:
db2 reorgchk update statistics on table all 該語句也用來對所有表做RUNSTATS
db2 reorgchk current statistics on table vicky.staff
table statistics：
CARD:記錄筆數
OV(ERFLOW):overflow的記錄數
NP(AGES):含有記錄的頁數
FP(AGES):表格佔用的總頁數
TSIZE(Bytes):表格大小
index statistics:
LEAF:leaf page數
ELEAF:空葉數
NDEL:被刪除的RID數
LVLS:索引層級數
ISIZE:索引平均長度
KEYS:不同的索引值的個數
表格重整：
db2 reorg table vicky.staff index ind1 use tempspace2 indexscan
索引重整：
db2 reorg indexes all for table vicky.staff _options_
options:allow read|write|no access, cleanup only pages|all（不重建索引結構，只回收空索引頁）
聯機表格重整(V8+)：
db2 reorg table vicky.staff index vicky.ind2 inplace allow write access
db2 reorg table vicky.staff index vicky.ind2 inplace pause|resume
db2 list history reorg all for dbname
MQT
MQT:
建立MQT:
CREATE TABLE vicky.mqt1 AS
( SELECT name, location, salary
FROM vicky.staff, vicky.org
WHERE staff.dept=org.deptnumb AND salary>20000
) DATA INITIALLY DEFERRED REFRESH DEFERRED|IMMEDIATE
ENABLE|DISABLE QUERY OPTIMIZATION
REFRESH TABLE vicky.mqt1
利用MQT:
RUNSTATS ON TABLE vicky.mqt1
UPDATE DB CFG FOR sample USING DFT_REFRESH_AGE ANY
平行處理
設定平行處理：
UPDATE DBM CFG USING INTRA_PARALLEL YES
UPDATE DB CFG FOR EOS USING DFT_DEGREE ANY
平行處理上限：
應用級：
SET RUNTIME DEGREE FOR ( 25 ) TO 4
SET RUNTIME DEGREE FOR ALL TO 6
執行個體級：
UPDATE DBM CFG USING MAX_QUERYDEGREE 6
MDC表:
CREATE TABLE vicky.sales
( YearAndMonth CHAR(4),
Region CHAR(20),
Product CHAR(2),
Sales BIGINT
) ORGANIZE BY DIMENSIONS ( YearAndMonth, Region )
CREATE TABLE vicky.table1
( col1 CHAR(10),
col2 CHAR(10),
col3 CHAR(10),
col4 INTEGER,
col5 DECIMAL(10,2),
) ORGANIZE BY DIMENSIONS ( col1, ( col2, col3 ),col4 )