為了幫助 DB2 DBA 避免性能災(zāi)難并獲得高性能，本文主要介紹了一套故障診斷流程。下文中將著重講解在Unix、Windows和OS/2 環(huán)境下使用DB2 UDB電子商務(wù)（OLTP應(yīng)用程序）的10條性能改善技巧。

10. 監(jiān)視開(kāi)關(guān)

確保已經(jīng)打開(kāi)監(jiān)視開(kāi)關(guān)。如果它們沒(méi)有打開(kāi)，您將無(wú)法獲取您需要的性能信息。要打開(kāi)該監(jiān)視開(kāi)關(guān)，請(qǐng)發(fā)出以下命令： db2 'update monitor switches using

lock ON sort ON bufferpool ON uow ON

table ON statement ON'

9. 代理程序

確保有足夠的 DB2 代理程序來(lái)處理工作負(fù)載。要找出代理程序的信息，請(qǐng)發(fā)出命令： db2 'get snapshot for database manager'

并查找以下行： 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

如果您發(fā)現(xiàn)Agents waiting for a token或Agents stolen from another application不為 0，那么請(qǐng)?jiān)黾訉?duì)數(shù)據(jù)庫(kù)管理器可用的代理程序數(shù)（MAXAGENTS 和／或 MAX_COORDAGENTS取適用者）。

8. 最大打開(kāi)的文件數(shù)

DB2 在操作系統(tǒng)資源的約束下盡量做一個(gè)“優(yōu)秀公民”。它的一個(gè)“優(yōu)秀公民”的行動(dòng)就是給在任何時(shí)刻打開(kāi)文件的最大數(shù)設(shè)置一個(gè)上限。數(shù)據(jù)庫(kù)配置參數(shù)MAXFILOP約束 DB2 能夠同時(shí)打開(kāi)的文件最大數(shù)量。當(dāng)打開(kāi)的文件數(shù)達(dá)到此數(shù)量時(shí)，DB2 將開(kāi)始不斷地關(guān)閉和打開(kāi)它的表空間文件（包括裸設(shè)備）。不斷地打開(kāi)和關(guān)閉文件減緩了 SQL 響應(yīng)時(shí)間并耗費(fèi)了 CPU 周期。要查明 DB2 是否正在關(guān)閉文件，請(qǐng)發(fā)出以下命令： db2 'get snapshot for database on DBNAME'

并查找以下的行： Database files closed = 0

如果上述參數(shù)的值不為 0，那么增加MAXFILOP的值直到不斷打開(kāi)和關(guān)閉文件的狀態(tài)停止。使用以下命令： db2 'update db cfg for DBNAME using MAXFILOP N'

7. 鎖

LOCKTIMEOUT的缺省值是 -1，這意味著將沒(méi)有鎖超時(shí)（對(duì) OLTP 應(yīng)用程序，這種情況可能會(huì)是災(zāi)難性的）。盡管如此，我還是經(jīng)常發(fā)現(xiàn)許多 DB2 用戶用LOCKTIMEOUT= -1。將LOCKTIMEOUT設(shè)置為很短的時(shí)間值，例如 10 或 15 秒。在鎖上等待過(guò)長(zhǎng)時(shí)間會(huì)在鎖上產(chǎn)生雪崩效應(yīng)。

首先，用以下命令檢查L(zhǎng)OCKTIMEOUT的值： db2 'get db cfg for DBNAME'

并查找包含以下文本的行： Lock timeout (sec) (LOCKTIMEOUT) = -1

如果值是 -1，考慮使用以下命令將它更改為 15 秒（一定要首先詢問(wèn)應(yīng)用程序開(kāi)發(fā)者或您的供應(yīng)商以確保應(yīng)用程序能夠處理鎖超時(shí)）： db2 'update db cfg for DBNAME using LOCKTIMEOUT 15'

您同時(shí)應(yīng)該監(jiān)視鎖等待的數(shù)量、鎖等待時(shí)間和正在使用鎖列表內(nèi)存（lock list memory）的量。請(qǐng)發(fā)出以下命令： 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 memory in use (Bytes)超過(guò)所定義LOCKLIST大小的 50%，那么在LOCKLIST數(shù)據(jù)庫(kù)配置中增加 4k 頁(yè)的數(shù)量。

6. 臨時(shí)表空間

為了改善 DB2 執(zhí)行并行 I/O 和提高使用TEMPSPACE的排序、散列連接（hash join）和其它數(shù)據(jù)庫(kù)操作的性能，臨時(shí)表空間至少應(yīng)該在三個(gè)不同的磁盤驅(qū)動(dòng)器上擁有三個(gè)容器。

要想知道您的臨時(shí)表空間具有多少容器，請(qǐng)發(fā)出以下命令： db2 'list tablespaces show detail'

查找與以下示例類似的TEMPSPACE表空間定義： 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

注意Number of containers的值是 3，而且Prefetch size是Extent size的三倍。為了得到最佳的并行 I/O 性能，重要的是Prefetch size為Extent size的倍數(shù)。這個(gè)倍數(shù)應(yīng)該等于容器的個(gè)數(shù)。

要查找容器的定義，請(qǐng)發(fā)出以下命令： db2 'list tablespace containers for 1 show detail'

1 指的是tablespace ID #1，它是剛才所給出的示例中的TEMPSPACE1。

5. 內(nèi)存排序

OLTP 應(yīng)用程序不應(yīng)該執(zhí)行大的排序。它們?cè)?CPU、I/O 和所用時(shí)間方面的成本極高，而且將使任何 OLTP 應(yīng)用程序慢下來(lái)。因此，256 個(gè) 4K 頁(yè)（1MB）的缺省SORTHEAP大小（1MB）應(yīng)該是足夠了。您也應(yīng)該知道排序溢出的數(shù)量和每個(gè)事務(wù)的排序數(shù)。

請(qǐng)發(fā)出以下命令： 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 ((Sort overflows * 100) / Total sorts )大于 3 個(gè)百分點(diǎn)，那么在應(yīng)用程序 SQL 中會(huì)出現(xiàn)嚴(yán)重的或意外的排序問(wèn)題。因?yàn)檎且绯龅拇嬖诒砻靼l(fā)生了大的排序，所以理想的情況是發(fā)現(xiàn)沒(méi)有排序溢出或至少其百分比小于一個(gè)百分點(diǎn)。

如果出現(xiàn)過(guò)多的排序溢出，那么“應(yīng)急”解決方案是增加SORTHEAP的大小。然而，這樣做只是掩蓋了真實(shí)的性能問(wèn)題。相反，您應(yīng)該確定引起排序的 SQL 并更改該 SQL、索引或群集來(lái)避免或減少排序開(kāi)銷。

如果SortsPerTX大于 5 （作為一種經(jīng)驗(yàn)之談），那么每個(gè)事務(wù)的排序數(shù)可能很大。雖然某些應(yīng)用程序事務(wù)執(zhí)行許多小的組合排序（它們不會(huì)溢出并且執(zhí)行時(shí)間很短），但是它消耗了過(guò)多的 CPU。當(dāng)SortsPerTX很大時(shí)，按我的經(jīng)驗(yàn)，這些機(jī)器通常會(huì)受到 CPU 的限制。確定引起排序的 SQL 并改進(jìn)存取方案（通過(guò)索引、群集或更改 SQL）對(duì)提高事務(wù)吞吐率是極為重要的。

4. 表訪問(wèn)

對(duì)于每個(gè)表，確定 DB2 為每個(gè)事務(wù)讀取的行數(shù)。您必須發(fā)出兩個(gè)命令： db2 'get snapshot for database on DBNAME'

db2 'get snapshot for tables on DBNAME'

在發(fā)出第一個(gè)命令以后，確定發(fā)生了多少個(gè)事務(wù)（通過(guò)取Commit statements attempted和Rollback statements attempted之和 － 請(qǐng)參閱 技巧 3）。

在發(fā)出第二個(gè)命令以后，將讀取的行數(shù)除以事務(wù)數(shù)（RowsPerTX）。在每個(gè)事務(wù)中，OLTP 應(yīng)用程序通常應(yīng)該從每個(gè)表讀取 1 到 20 行。如果您發(fā)現(xiàn)對(duì)每個(gè)事務(wù)有成百上千的行正被讀取，那么發(fā)生了掃描操作，也許需要?jiǎng)?chuàng)建索引。（有時(shí)以分布和詳細(xì)的索引來(lái)運(yùn)行 runstats 也可提供了一個(gè)解決的辦法。）

“get snapshot for tables on DBNAME”的樣本輸出如下： 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 的數(shù)量很大就可能意味著您需要重組表。當(dāng)由于更改了行的寬度從而 DB2 必須在一個(gè)不夠理想的頁(yè)上定位一個(gè)行時(shí)就會(huì)發(fā)生溢出。

3. 表空間分析

表空間快照對(duì)理解訪問(wèn)什么數(shù)據(jù)以及如何訪問(wèn)是極其有價(jià)值的。要得到一個(gè)表空間快照，請(qǐng)發(fā)出以下命令： db2 'get snapshot for tablespaces on DBNAME'

對(duì)每個(gè)表空間，回答以下問(wèn)題：

平均讀取時(shí)間（ms）是多少？

平均寫入時(shí)間（ms）是多少？

異步（預(yù)取）相對(duì)于同步（隨機(jī)）所占的物理 I/O 的百分比是多少？

每個(gè)表空間的緩沖池命中率是多少？

每分鐘讀取多少物理頁(yè)面？

對(duì)于每個(gè)事務(wù)要讀取多少物理和邏輯頁(yè)面？

對(duì)于所有表空間，回答以下問(wèn)題：

哪個(gè)表空間的讀取和寫入的時(shí)間最慢？為什么？是因?yàn)槠淙萜髟诼俚拇疟P上嗎？容器大小是否相等？對(duì)比異步訪問(wèn)和同步訪問(wèn)，訪問(wèn)屬性是否和期望的一致？隨機(jī)讀取的表應(yīng)該有隨機(jī)讀取的表空間，這是為了得到高的同步讀取百分比、通常較高的緩沖池命中率和更低的物理 I/O 率。

對(duì)每個(gè)表空間，確保預(yù)取大小等于數(shù)據(jù)塊大小乘以容器數(shù)。請(qǐng)發(fā)出以下命令： db2 'list tablespaces show detail'

如果需要，可以為一個(gè)給定表空間改變預(yù)取大小。可以使用以下命令來(lái)檢查容器定義： db2 'list tablespace containers for N show detail'

在此，N 是表空間標(biāo)識(shí)號(hào)。

2. 緩沖池優(yōu)化

我時(shí)常發(fā)現(xiàn)一些 DB2 UDB 站點(diǎn)，雖然機(jī)器具有 2、4 或 8GB 內(nèi)存，但是 DB2 數(shù)據(jù)庫(kù)卻只有一個(gè)緩沖池（IBMDEFAULTBP），其大小只有 16MB！

如果在您的站點(diǎn)上也是這種情況，請(qǐng)為 SYSCATSPACE 目錄表空間創(chuàng)建一個(gè)緩沖池、為TEMPSPACE表空間創(chuàng)建一個(gè)緩沖池以及另外創(chuàng)建至少兩個(gè)緩沖池：BP_RAND和BP_SEQ。隨機(jī)訪問(wèn)的表空間應(yīng)該分配給用于隨機(jī)訪問(wèn)的緩沖池（BP_RAND）。順序訪問(wèn)（使用異步預(yù)取 I/O）的表空間應(yīng)該分配給用于順序訪問(wèn)的緩沖池（BP_SEQ）。根據(jù)某些事務(wù)的性能目標(biāo)，您可以創(chuàng)建附加的緩沖池；例如，您可以使一個(gè)緩沖池足夠大以存儲(chǔ)整個(gè)“熱”（或者說(shuō)訪問(wèn)非常頻繁的）表。當(dāng)涉及到大的表時(shí)，某些 DB2 用戶將重要表的索引放入一個(gè)索引（BP_IX）緩沖池取得了很大成功。

太小的緩沖池會(huì)產(chǎn)生過(guò)多的、不必要的物理 I/O。太大的緩沖池使系統(tǒng)處在操作系統(tǒng)頁(yè)面調(diào)度的風(fēng)險(xiǎn)中并消耗不必要的 CPU 周期來(lái)管理過(guò)度分配的內(nèi)存。正好合適的緩沖池大小就在“太小”和“太大”之間的某個(gè)平衡點(diǎn)上。適當(dāng)?shù)拇笮〈嬖谟诨貓?bào)將要開(kāi)始減少的點(diǎn)上。如果您沒(méi)有使用工具來(lái)自動(dòng)進(jìn)行回報(bào)減少分析，那么您應(yīng)該在不斷增加緩沖池大小上科學(xué)地測(cè)試緩沖池性能（命中率、I/O 時(shí)間和物理 I/O 讀取率），直到達(dá)到最佳的緩沖池大小。因?yàn)闃I(yè)務(wù)一直在變動(dòng)和增長(zhǎng)，所以應(yīng)該定期重新評(píng)估“最佳大小”決策。

1. SQL 成本分析

一條糟糕的 SQL 語(yǔ)句會(huì)徹底破壞您的一整天。我不止一次地看到一個(gè)相對(duì)簡(jiǎn)單的 SQL 語(yǔ)句搞糟了一個(gè)調(diào)整得很好的數(shù)據(jù)庫(kù)和機(jī)器。對(duì)于很多這些語(yǔ)句，天底下（或在文件中）沒(méi)有 DB2 UDB 配置參數(shù)能夠糾正因錯(cuò)誤的 SQL 語(yǔ)句導(dǎo)致的高成本的情況。

更糟糕的是，DBA 常常受到種種束縛：不能更改 SQL（可能是因?yàn)樗菓?yīng)用程序供應(yīng)商提供的，例如 SAP、 PeopleSoft或 Siebel）。這給 DBA 只留下三條路可走：

(1)更改或添加索引

(2)更改群集

(3)更改目錄統(tǒng)計(jì)信息

另外，如今健壯的應(yīng)用程序由成千上萬(wàn)條不同的 SQL 語(yǔ)句組成。這些語(yǔ)句執(zhí)行的頻率隨應(yīng)用程序的功能和日常的業(yè)務(wù)需要的不同而不同。SQL 語(yǔ)句的實(shí)際成本是它執(zhí)行一次的成本乘以它執(zhí)行的次數(shù)。

每個(gè) DBA 所面臨的重大的任務(wù)是，識(shí)別具有最高“實(shí)際成本”的語(yǔ)句的挑戰(zhàn)，并且減少這些語(yǔ)句的成本。

通過(guò)本機(jī) DB2 Explain 實(shí)用程序、一些第三方供應(yīng)商提供的工具或 DB2 UDB SQL Event Monitor 數(shù)據(jù)，您可以計(jì)算出執(zhí)行一次 SQL 語(yǔ)句所用的資源成本。但是語(yǔ)句執(zhí)行頻率只能通過(guò)仔細(xì)和耗時(shí)地分析 DB2 UDB SQL Event Monitor 的數(shù)據(jù)來(lái)了解。

在研究SQL語(yǔ)句問(wèn)題時(shí)，DBA 使用的標(biāo)準(zhǔn)流程是：

1. 創(chuàng)建一個(gè) SQL Event Monitor，寫入文件： ___FCKpd___22gt; db2 'create event monitor SQLCOST for statements write to ...'

2. 激活事件監(jiān)視器（確保有充足的可用磁盤空間）： ___FCKpd___23gt; db2 'set event monitor SQLCOST state = 1'

3. 讓應(yīng)用程序運(yùn)行。

4. 取消激活事件監(jiān)視器： ___FCKpd___24gt; db2 'set event monitor SQLCOST state = 0'

5. 使用 DB2 提供的 db2evmon 工具來(lái)格式化 SQL Event Monitor 原始數(shù)據(jù)（根據(jù) SQL 吞吐率可能需要數(shù)百兆字節(jié)的可用磁盤空間）： ___FCKpd___25gt; db2evmon -db DBNAME -evm SQLCOST

> sqltrace.txt

6. 瀏覽整個(gè)已格式化的文件，尋找顯著大的成本數(shù)（一個(gè)耗時(shí)的過(guò)程）： ___FCKpd___26gt; more sqltrace.txt

7. 對(duì)已格式化的文件進(jìn)行更完整的分析，該文件試圖標(biāo)識(shí)唯一的語(yǔ)句（獨(dú)立于文字值）、每個(gè)唯一語(yǔ)句的頻率（它出現(xiàn)的次數(shù)）和其總 CPU、排序以及其它資源成本的總計(jì)。如此徹底的分析在 30 分鐘的應(yīng)用程序 SQL 活動(dòng)樣本上可能要花一周或更多的時(shí)間。

要減少確定高成本 SQL 語(yǔ)句所花的時(shí)間，您可以考慮許多可用的信息來(lái)源：

從技巧4，務(wù)必要計(jì)算在每個(gè)事務(wù)中從每個(gè)表中讀取的行數(shù)。如果產(chǎn)生的數(shù)字看上去很大，那么 DBA 可以在 SQL Event Monitor 格式化輸出中搜索有關(guān)的表名稱（這將縮小搜索范圍而且節(jié)省一些時(shí)間），這樣也許能夠找出有問(wèn)題的語(yǔ)句。 從 技巧 3，務(wù)必計(jì)算每個(gè)表空間的異步讀取百分比和物理 I/O 讀取率。如果一個(gè)表空間的異步讀取百分比很高并遠(yuǎn)遠(yuǎn)超過(guò)平均的物理 I/O 讀取率，那么在此表空間中的一個(gè)或更多的表正在被掃描。查詢目錄并找出哪些表被分配到可疑的表空間（每個(gè)表空間分配一個(gè)表提供最佳性能檢測(cè)），然后在 SQL Event Monitor 格式化輸出中搜索這些表。這些也可能有助于縮小對(duì)高成本 SQL 語(yǔ)句的搜索范圍。 嘗試觀察應(yīng)用程序執(zhí)行的每條 SQL 語(yǔ)句的 DB2 Explain 信息。然而，我發(fā)現(xiàn)高頻率、低成本語(yǔ)句經(jīng)常爭(zhēng)用機(jī)器容量和能力來(lái)提供期望的性能。 如果分析時(shí)間很短而且最大性能是關(guān)鍵的，那么請(qǐng)考慮使用供應(yīng)商提供的工具（它們能夠快速自動(dòng)化識(shí)別資源密集的 SQL 語(yǔ)句的過(guò)程）。 Database-GUYS Inc.的 SQL-GUY 工具提供精確、實(shí)時(shí)且均衡的 SQL 語(yǔ)句的成本等級(jí)分析。

繼續(xù)調(diào)節(jié)

最佳性能不僅需要排除高成本 SQL 語(yǔ)句，而且需要確保相應(yīng)的物理基礎(chǔ)結(jié)構(gòu)是適當(dāng)?shù)摹．?dāng)所有的調(diào)節(jié)旋鈕都設(shè)置得恰到好處、內(nèi)存被有效地分配到池和堆而且 I/O 均勻地分配到各個(gè)磁盤時(shí)，才可得到最佳性能。雖然量度和調(diào)整需要時(shí)間，但是執(zhí)行這 10 個(gè)建議的 DBA 將非常成功地滿足內(nèi)部和外部的 DB2 客戶。因?yàn)殡娮由虅?wù)的變化和增長(zhǎng)，即使是管理得最好的數(shù)據(jù)庫(kù)也需要定期的微調(diào)。DBA 的工作永遠(yuǎn)都做不完！

技巧總結(jié)：

對(duì)工作負(fù)載使用足夠的代理程序。

不允許 DB2 不必要地關(guān)閉和打開(kāi)文件。

不允許長(zhǎng)期的鎖等待。

確保數(shù)據(jù)庫(kù)的 TEMPSPACE 表空間的并行 I/O 能力。

保守地管理 DB2 排序內(nèi)存并不要以大的 SORTHEAP 來(lái)掩蓋排序問(wèn)題。

分析表的訪問(wèn)活動(dòng)并確定具有特別高的每個(gè)事務(wù)讀取行數(shù)或溢出數(shù)的表。

分析每個(gè)表空間的性能特性，并尋求改善讀取時(shí)間最慢、等待時(shí)間最長(zhǎng)、物理 I/O 讀取率最高、命中率最差的表空間性能以及與所期望的不一致的訪問(wèn)屬性。

創(chuàng)建多個(gè)緩沖池，有目的地將表空間分配到緩沖池以便于共享訪問(wèn)屬性。

檢查 DB2 UDB SQL Event Monitor 信息以找到哪個(gè) SQL 語(yǔ)句消耗計(jì)算資源最多并采取正確的措施。

一旦排除了高成本 SQL，馬上重新評(píng)估配置和物理設(shè)計(jì)設(shè)置。