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

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

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

lock ON sort ON bufferpool ON uow ON

table ON statement ON'

9. 代理程序

確保有足夠的 DB2 代理程序來處理工作負載。要找出代理程序的信息，請發(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，那么請增加對數(shù)據(jù)庫管理器可用的代理程序數(shù)（MAXAGENTS 和／或 MAX_COORDAGENTS取適用者）。

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

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

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

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

7. 鎖

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

首先，用以下命令檢查LOCKTIMEOUT的值： db2 'get db cfg for DBNAME'

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

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

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

6. 臨時表空間

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

要想知道您的臨時表空間具有多少容器，請發(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ù)。這個倍數(shù)應(yīng)該等于容器的個數(shù)。

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

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

5. 內(nèi)存排序

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

請發(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 個百分點，那么在應(yīng)用程序 SQL 中會出現(xiàn)嚴重的或意外的排序問題。因為正是溢出的存在表明發(fā)生了大的排序，所以理想的情況是發(fā)現(xiàn)沒有排序溢出或至少其百分比小于一個百分點。

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

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

4. 表訪問

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

db2 'get snapshot for tables on DBNAME'

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

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

“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 必須在一個不夠理想的頁上定位一個行時就會發(fā)生溢出。

3. 表空間分析

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

對每個表空間，回答以下問題：

平均讀取時間（ms）是多少？

平均寫入時間（ms）是多少？

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

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

每分鐘讀取多少物理頁面？

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

對于所有表空間，回答以下問題：

哪個表空間的讀取和寫入的時間最慢？為什么？是因為其容器在慢速的磁盤上嗎？容器大小是否相等？對比異步訪問和同步訪問，訪問屬性是否和期望的一致？隨機讀取的表應(yīng)該有隨機讀取的表空間，這是為了得到高的同步讀取百分比、通常較高的緩沖池命中率和更低的物理 I/O 率。

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

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

在此，N 是表空間標識號。

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

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

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

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

1. SQL 成本分析

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

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

(1)更改或添加索引

(2)更改群集

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

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

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

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

在研究SQL語句問題時，DBA 使用的標準流程是：

1. 創(chuàng)建一個 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)用程序運行。

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

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

> sqltrace.txt

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

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

要減少確定高成本 SQL 語句所花的時間，您可以考慮許多可用的信息來源：

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

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

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

技巧總結(jié)：

對工作負載使用足夠的代理程序。

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

不允許長期的鎖等待。

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

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

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

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

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

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

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