DB2更新執行計劃的方法

和ORACLE資料庫一樣，DB2資料庫裡面也是通過優化器來分析你的SQL，生成它認為最優的執行計劃(Access Plan)。下面小編準備了關於DB2更新執行計劃的方法，提供給大家參考!

DB2的優化器實際上是一個標準規則集合，一般來說我們只要告訴DB2要檢索什麼，而不是如何檢索。 那麼DB2的優化器是根據什麼來判斷SQL的最優存取路徑呢?

DB2的優化器是基於成本的優化器，也就是CBO(Cost Based Optmizer)。也就是說DB2 優化器會應用查詢成本公式，該公式對每條可能的存取路徑的四個因素進行評估和權衡：CPU 成本、I/O 成本、DB2 系統目錄中的統計資訊和實際的 SQL 語句。

　　DB2的優化器的工作流程：

1. DB2的優化器，在接收到SQL語句後，會首先校驗SQL的語法，確保是正確的SQL

2. 根據當前的系統環境資訊，生成最優的執行計劃來優化SQL語句

3. 把SQL翻譯成計算機指令語言，並執行這個優化後的SQL

4. 返回結果，或者儲存它們，以便將來的執行

在我們看來，DB2 系統目錄中統計資訊是讓DB2優化器正確工作的一個非常重要的依據。這些統計資訊向優化器提供了與正在被優化的 SQL 語句將要訪問的表狀態相關的資訊。這些資訊主要包括：

Table--包括表的記錄數、PAGE、PCTFREE以及COMPRESS等資訊，相關的系統檢視是：es、es

Columns—包括COLUMNS的數量、長度、分佈特徵以及COMPRESS等資訊，相關的系統檢視是：mns、syscat. columns

Index--包括是否存在索引、索引的組織(葉子頁的數量和級別的數量)、索引鍵的離散值的數量以及是否群集索引， 相關的系統檢視是：xes、syscat. indexes

其他的還有分割槽/節點組資訊和表空間的資訊

　　如何及時更新這些資訊呢?保證DB2優化器正確的工作，在DB2裡面提供了以下的辦法。

RUNSTATS與REOGCHK

Runstats這個命令的功能主要就是收集資料庫物件的狀態資訊，這對資料庫使用合理的ACCESS PLAN是至關重要的。一般來說，以下幾種情況下面，我們需要用runstats來收集統計資訊：

1. 在給表建立一個index後，我們最好做一次runstat。這個情況也是大家經常忽略的。很多時候大家在給表增加了一個index後，分析執行計劃，發現沒有變化，覺得很奇怪。其實這個時候，你需要做一次runstats，就可以了。在8.2裡面，DB2做了很好的改進，可以避免這個問題，在建立index的時候，可以立即更新你的資訊。

2. 在對table做了一次reorg後，記得要做一次runstats。因為對錶做reorg，會修改表的很多資訊，比如高水位等，所以做一次runstats，可以更新統計資訊。

3. 當你的表裡面的資料發生了比較大的變化，一般來說，大約表裡面的資料量的10%-20%發生了變化，就應該作一次runstats。這些變化包括刪除，修改，插入。對於一些非常大的表，比方在資料倉庫的`專案裡面，某些事實表非常巨大。這個時候，完整的對一個大表作runstats可能花費時間相當大，DB2 8.1裡面支援我們對這些大表作抽樣，比方說只對20%的資料作runstats，這樣的話，一般來說也能保證得到正確的執行計劃。當然首先要確保這個表裡面的資料最好分佈比較均勻。

4. 當你在分割槽(DPF)資料庫裡面使用了REDISTRIBUTE DATABASE PARTITION GROUP這個命令，那麼就需要用runstats來收集新的統計資訊。

　　RUNSTATS命令的語法如下：

如果表名為F，表上有索引，則可以用下面的例子完成RUNSTATS命令：

db2 runstats on table f with distribution and detailed indexes all

在實際的專案裡面，對於變化比較大的表，需要我們定時對資料庫做runstats，一般來說runstats和reorg可以結合起來做，首先對錶作reorg，然後做runstats，最後REBIND資料庫根據最新的統計資訊生成合適的統計計劃。

值得注意的是，如果我們要處理的表資料量是快速變化的，比如在電信移動行業，需要在月末進行處理的彙總表。在不長的時間範圍內資料量變化特別大，從而使得RUNSTATS 得到的統計資訊不準確，原因是這些統計資訊只是某個時間點的資訊。您可以用這條語句來把表修改為volatile。

alter table table_name volatile cardinality

這樣優化器將考慮使用索引掃描而不是表掃描。無論統計資訊如何，優化器將使用索引掃描而不是使用表掃描。

IBM的文件裡面還提供了REORGCHK這個命令，可以根據統計公式計算表是否需要重整。

比如可以分為對系統表和使用者表兩部分分別進行REORGCHK：

1) 針對系統表進行REORGCHK

db2 reorgchk update statistics on table system

2) 針對使用者表進行REORGCHK

db2 reorgchk update statistics on table user

需要注意的是，如果資料庫中資料量比較大，這些操作一般所需時間比較長，所以儘量安排在資料庫比較空閒的時候做。

db2 update db cfg using AUTO_MAINT off AUTO_TBL_MAINT off AUTO_RUNSTATS off

在DB2 8.2裡面資料庫可以自動進行統計資訊收集，不過這樣的動作還是會帶來額外的負載，一般情況下面可以關掉，只在我們需要的時候執行就可以了。

LOAD

Load這個工具是DB2裡面一個非常強大的資料遷移工具。一般用作大批量的資料插入。因為Load操作不記日誌，所以效率非常好。筆者曾經在RS6000平臺上面實現50-60m/s的速度Load資料。在這裡我想討論的是在DB2資料庫裡面如何用load來影響你的catalog檢視的統計資訊。

在Load的時候使用statistics選項可以在裝入的過程中生成統計資料，這些統計資料可以供優化器確定最有效的執行sql語句的方式。

比如：

對錶和索引產生最詳細的統計資料：

load from mobile_ of del replace into mobile statistics yes with distribution and detailed indexes all

對錶和索引都產生簡略的統計：

load from mobile_ of del replace into mobile statistics yes and indexes all

需要值得注意的時候在DB2 8.2新版本里面，可以這樣做：

load from mobile_ of del replace into mobile statistics use profile

DB2LOOK

Db2look是DB2一個相當強大的一輔助工具，可以幫助我們從當前資料庫裡面把資料庫結構抽取出來。在資料遷移的很多場合，我們都非常需要這個強大的工具。

在一些場合，特別是開發庫遷移到生產庫，生產庫遷移到開發庫的環境中，為了保證SQL執行計劃的一致，我們需要用db2look這個工具，利用 db2look 工具提供的抽取資料庫物件統計資訊的功能，把資料庫的統計資訊進行遷移。

比如：

1) 在使用者環境下提取統計資訊：

db2 runstats on table <使用者表模式名>.<表名>

db2look -d <使用者資料庫名> -t <表名> -m -o

輸出檔案中是對使用者的 DB2 系統編目表中與該表統計資訊相關的各欄位值的 UPDATE 語句。

db2 -svtf

2) 利用使用者提供的統計資訊更新測試環境下測試表的統計資訊：