我用DB2的這幾年（三）
2024-09-06 23:58:02
供稿：網友
系統運行一段時間以后，用戶抱怨某些操作響應速度過慢；這個在項目前期沒有出現過類似問題，因此懷疑是數據量過大造成的原因。但是，查詢相關業務表中僅僅只有3萬多的的數據量，不足以構成影響程序響應速度過慢的瓶頸。更奇怪的是采用導入的方法將此表數據裝載進來卻沒有發現上述現象，我百思不得其解。
幾天后，無意間翻閱一本雜志，其中有這么一段話——“每當sql語句被發送到到db2 數據庫管理器中處理時，sql 優化器會去讀取系統編目表來確定被引用的列的特性以及在被引用的表中時候已經定義了索引，同時被語句引用的每個表的大小也包括在內。根據這些得到的信息，優化器可以估算出能滿足sql語句需要的每一種數據存取路徑的成本，然后推薦最佳的一個。 優化器用于做決策的數據庫統計集合數據在系統編目表中是一個關鍵性的元素。所以，統計的變化可能導致選擇存取路徑的變化；如果信息丟失或過時，優化器也許選擇出來的存取計劃將導致sql語句執行時間比正常的要長。例如，一個刪除操作可能留下以后不能再使用的空的數據頁面。對各種長度的字段進行更新可能導致新的字段值不適合在同一個數據頁面中存放。這將導致某些行被移動到不同得頁面并且在表里產生內部空隙或者未使用空間。因此，db2不得不去讀取更多的物理頁面來取回應用程序所需要的數據”。結合前面遇見的這個問題，該操作所涉及的物理表的確是經常進行增刪改操作的，是不是因為這個原因呢？剛好前段時間學習過關于表重組和運行統計的內容，知道db2有runstats和reorg工具來完成表的運行統計和重組。于是我就做了以下試驗：
---1首先檢查是否要重新組織數據 reorgchk current statistics on table db2admin.t_ckd 得到表的統計信息和索引的統計信息顯示如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
表統計信息：
表統計信息：
f1: 100 * overflow / card < 5
f2: 100 * tsize / ((fpages-1) * (tablepagesize-76)) > 70
f3: 100 * npages / fpages > 80
creator name card ov np fp tsize f1 f2 f3 reorg
--------------------------------------------------------------------------------
db2admin t_ckd 1 0 1 12 9 0 0 8 -**
--------------------------------------------------------------------------------
索引統計信息：
f4: clusterratio 或正常化的 clusterfactor > 80
f5: 100 * (keys * (isize+8) + (card-keys) * 4) / (nleaf * indexpagesize) > 50
f6: (100-pctfree) * (indexpagesize-96) / (isize+12) ** (nlevels-2) * (indexpagesize-96) / (keys * (isize+8) + (card-keys) * 4) < 100
creator name card leaf lvls isize keys f4 f5 f6 reorg
--------------------------------------------------------------------------------
表：db2admin.t_ckd
db2admin xak1t_ckd 1 1 2 28 1 100 - +++ ---
db2admin xie1t_ckd 1 1 1 10 1 100 - - ---
db2admin xie2t_ckd 1 1 1 10 1 100 - - ---
db2admin xie3t_ckd 1 1 1 4 1 100 - - ---
db2admin xie4t_ckd 1 1 1 18 1 100 - - ---
sysibm sql010510174815750 1 1 2 28 1 100 - +++ ---
--------------------------------------------------------------------------------
clusterratio 或正?；?clusterfactor (f4) 將指示索引需要 reorg，該索引與基表不在相同的序列中。當在表中定義了多個索引時，一個或多個索引可能被標記為需要 reorg。 指定 reorg 順序的最重要索引。
可以看到表統計信息中要求f1<5,f2>70,f3>80而實際的表的f1＝0，f2＝0，f3＝8不能滿足要求，索引的大部分f4,f5,f6也不能滿足要求，必須進行重新統計
----2重新組織數據庫表的索引
reorg table db2admin.t_ckd index db2admin.xie3t_ckd
----3重新統計索引
runstats on table db2admin.t_ckd and indexes all
----4重新統計后可以再看看數據表的信息 reorgchk current statistics on table db2admin.t_ckd 得到表的統計信息和索引的統計信息顯示如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
表統計信息：
表統計信息：
f1: 100 * overflow / card < 5
f2: 100 * tsize / ((fpages-1) * (tablepagesize-76)) > 70
f3: 100 * npages / fpages > 80
creator name card ov np fp tsize f1 f2 f3 reorg
--------------------------------------------------------------------------------
db2admin t_ckd 4893 0 401 401 1546188 0 96 100 ---
--------------------------------------------------------------------------------
索引統計信息：
f4: clusterratio 或正?；?clusterfactor > 80
f5: 100 * (keys * (isize+8) + (card-keys) * 4) / (nleaf * indexpagesize) > 50
f6: (100-pctfree) * (indexpagesize-96) / (isize+12) ** (nlevels-2) * (indexpagesize-96) / (keys * (isize+8) + (card-keys) * 4) < 100
creator name card leaf lvls isize keys f4 f5 f6 reorg
--------------------------------------------------------------------------------
表：db2admin.t_ckd
db2admin xak1t_ckd 4893 49 2 28 4893 81 87 2 ---
db2admin xie1t_ckd 4893 7 2 10 3 99 68 18 ---
db2admin xie2t_ckd 4893 7 2 10 2 99 68 18 ---
db2admin xie3t_ckd 4893 7 2 4 18 100 68 18 ---
db2admin xie4t_ckd 4893 6 2 18 6 90 80 18 ---
sysibm sql010510174815750 4893 49 2 28 4893 81 87 2 ---
--------------------------------------------------------------------------------
clusterratio 或正常化的 clusterfactor (f4) 將指示索引需要 reorg，該索引與基表不在相同的序列中。當在表中定義了多個索引時，一個或多個索引可能被標記為需要 reorg。 指定 reorg 順序的最重要索引。
至此，試驗完成。接下來比較一下運行統計和重組前后運行成本，如下圖：
運行重組統計前
運行重組統計后
對比運行統計前后的sql語句成本可以看出由運行前的4469變成了運行后的1572，運行成本是原來的三分之一多。然后再運行程序發現響應速度比以前有大幅度的提高，到此這個棘手的問題算是解決了（當然這是治標不治本，要從根本改變就應該從sql語句本身入手優化它的性能）。同時我對于“采用導入的方法將此表數據裝載進來卻沒有發現上述現象”這個問題也找到了答案，那就是——在import過程中由于導入目標表示新表，import工具將會用類似運行統計的方式將數據均勻填充到葉面當中，因此速度也會加快。這個問題說明對于在數據庫中那些經常發生變動的表，定期進行運行統計是對數據庫性能提高是有幫助的。
【附錄：一些其他的背景知識】
對 reorgchk 所使用的度量的考慮因素包括：（當查看 reorgchk 工具的輸出時，找到用于表的 f1、f2 和 f3 這幾列，以及用于索引的 f4、f5、f6、f7 和 f8 這幾列。如果這些列中的任何一列有星號 (*)，則說明當前的表和/或索引超出了閾值。） f1: 屬于溢出記錄的行所占的百分比。當這個百分比大于 5% 時，在輸出的 f1 列中將有一個星號 (*)。
f2: 數據頁中使用了的空間所占的百分比。當這個百分比小于 70% 時，在輸出的 f2 列上將有一個星號 (*)。
f3: 其中含有包含某些記錄的數據的頁所占的百分比。當這個百分比小于 80% 時，在輸出的 f3 列上將有一個星號 (*)。
f4: 群集率，即表中與索引具有相同順序的行所占的百分比。當這個百分比小于 80% 時，那么在輸出的f4 列上將有一個星號 (*)。
f5: 在每個索引頁上用于索引鍵的空間所占的百分比。當這個百分比小于 50% 時，在輸出的 f5 列上將有一個星號 (*)。
f6: 可以存儲在每個索引級的鍵的數目。當這個數字小于 100 時，在輸出的 f6 列上將有一個星號 (*)。
f7: 在一個頁中被標記為 deleted 的記錄 id（鍵）所占的百分比。當這個百分比大于 20% 時，在輸出的 f7 列上將有一個星號 (*)。
f8: 索引中空葉子頁所占的百分比。當這個百分比大于 20% 時，在輸出的 f8 列上將有一個星號 (*)。
對所有表運行 reorgchk 工具，并確保您正在使用當前統計信息，可使用命令：
reorgchk update statistics on table user
可以使用如下語句來檢查任何沒有統計信息的表：
select tabname from syscat.tables where stats_time is null
可以使用如下語句來檢查任何沒有統計信息的索引：
select indname from syscat.indexes where stats_time is null
可以使用如下語句來查找具有時間超過 30 天的統計信息的表和索引：
select tabname from syscat.tables where stats_time < current timestamp - 30 days select indname from syscat.indexes where stats_time < current timestamp - 30 days
注意： 在使用 runstats 命令的時候，必須指定表所在的模式。
