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