（轉(zhuǎn)）https://mp.weixin.qq.com/s?__biz=MjM5NTk0MTM1Mw==&mid=2650629396&idx=1&sn=3ec17927b3d32c7bc9692c809d1f69cb&chksm=bef91b92898e928417a7608b2ef56deef78b184c65e5fef118dbe8708d4a2d1ebd717979e7f2&mpshare=1&scene=1&srcid=08212GNx1FDgPZQOXMQKKF3l#rd
為了幫助大家更好地進(jìn)行DB2的性能優(yōu)化，社區(qū)組織社區(qū)專家針對一些共性問題及難點(diǎn)分享經(jīng)驗(yàn)。以下內(nèi)容來自活動“Db2數(shù)據(jù)庫性能優(yōu)化經(jīng)驗(yàn)交流”，主要由以下社區(qū)專家及會員分享：leilin、topzgm、岳彩波、beyondmch、yellow-fin等
提醒：文章末尾有彩蛋，如果你是Db2達(dá)人，可不要錯過~
01
如何發(fā)現(xiàn)性能問題？通過什么定位？
1、收集信息。
2、分析
3、找到問題點(diǎn)解決
第一步 操作系統(tǒng)級別性能
CPU監(jiān)控：
ps -elf | sort 5 -rn | more 第6列代表CPU使用的計數(shù)器
I/O使用率：
iostat -D 收集磁盤I/O信息
內(nèi)存占用率：
討論的內(nèi)存指的是虛擬內(nèi)存（virtual memory），包括物理內(nèi)存（physical memory）與交換空間（swap space）
vmstat -> avm 當(dāng)前系統(tǒng)中已經(jīng)激活的虛擬內(nèi)存頁的數(shù)量（該數(shù)值不包含文件系統(tǒng)緩存）
vmstat -> fre 系統(tǒng)中平均空閑頁的數(shù)量（不能完全代表系統(tǒng)中可用的空閑內(nèi)存：文件系統(tǒng)緩存駐留內(nèi)存，并不會返還給空閑列表，除非被虛擬內(nèi)存管理器盜取）
svmon -> clnt與in use交叉項 代表有多少內(nèi)存被文件系統(tǒng)使用（加上free項，可以初步認(rèn)為是該系統(tǒng)中可以被應(yīng)用程序所使用的內(nèi)存）
第二步 數(shù)據(jù)庫級別性能
1. db2grep -dump | more 查看服務(wù)器安裝了幾個DB2版本
2. ps -elf | grep db2inst1 查看數(shù)據(jù)庫進(jìn)程的CPU計數(shù)器
3. db2 get dbm cfg | grep -i dft_mon 確認(rèn)快照打開
4. 實(shí)例級快照，了解當(dāng)前實(shí)例有多少應(yīng)用程序在執(zhí)行
db2 get snapshot for database manager -> Remote connections & Local connections
5. 數(shù)據(jù)庫級快照
連接數(shù)信息：applications connected currently，appls executing in db manager currently
鎖信息：鎖總數(shù)，鎖等待數(shù)量，鎖等待總時間，當(dāng)前數(shù)據(jù)庫鎖列表占用內(nèi)存，死鎖次數(shù)，鎖升級次數(shù)，鎖超時次數(shù)
排序信息：
排序是CPU殺手，過多的排序會造成CPU的極大消耗；
排序溢出是說，如果排序堆無法容納排序數(shù)據(jù)，就會被溢出到臨時空間；
排序是一種狀態(tài)，根源在SQL語句；
數(shù)據(jù)索引I/O信息：
邏輯讀 DB2向緩沖池請求的次數(shù) 邏輯讀越多，需要的物理I/O就越少
物理讀 如果請求的數(shù)據(jù)頁不在緩沖池，需要從磁盤中讀取數(shù)據(jù)頁的次數(shù)
吞吐量或事務(wù)信息：
提交/回滾事務(wù)數(shù)，執(zhí)行動態(tài)和靜態(tài)語句次數(shù)，增刪改查次數(shù)
( rows read / rows selected ) 是一個非常重要的性能指標(biāo)，它表示為了檢索一行數(shù)據(jù)需要讀取多少行，該值越大，表示代價越高，需要的I/O越多，可調(diào)優(yōu)的余地越大
事務(wù)日志信息：日志I/O在很大程度上會影響數(shù)據(jù)庫整體的性能
6. 應(yīng)用程序快照
在數(shù)據(jù)庫快照中發(fā)現(xiàn)存在大量的邏輯讀，通過應(yīng)用程序快照可以細(xì)化到某條特定的語句
7. 表空間快照
在數(shù)據(jù)庫快照中發(fā)現(xiàn)存在大量的邏輯讀，通過表空間快照可以輕松地定位哪個表空間被頻繁使用
8. 表快照
如果發(fā)現(xiàn)一個表的頁數(shù)很少，但是讀的行數(shù)非常多，那么可以合理地猜測該表在某些查詢語句中可能處于NLJOIN的內(nèi)部子節(jié)點(diǎn)
9. 動態(tài)SQL快照：SQL執(zhí)行次數(shù)，總共讀的行數(shù)，消耗的CPU，邏輯物理讀數(shù)量，排序數(shù)量等
第三步 內(nèi)存使用監(jiān)控
1. db2pd -osinfo
系統(tǒng)內(nèi)存使用情況
2. db2pd -dbptnmem
整個實(shí)例的內(nèi)存使用情況
3. db2pd -memsets
內(nèi)存段使用情況
在實(shí)例中會有多個不同的內(nèi)存段，每一個內(nèi)存段中可能有一個或者多個內(nèi)存池
ipcs -a | grep 578814120 內(nèi)存段映射到操作系統(tǒng)共享內(nèi)存IPC段
FMP與trace內(nèi)存段很少造成性能問題
4. db2pd -mempool
深入內(nèi)存池信息
5. db2pd -db <dbname> -memsets / -mempool
數(shù)據(jù)庫級別內(nèi)存段和內(nèi)存池信息
02
優(yōu)化過程中的優(yōu)先級問題？
在Db2優(yōu)化過程中，我已知的有如下手段
1.索引
2.sql語句優(yōu)化（分析執(zhí)行語句后重寫sql）
3.runstats信息收集
請問優(yōu)化過程中，入手的優(yōu)先級順序是什么呢，還有其他手段嗎？
這“三板斧”已經(jīng)可以解決很多問題了，DB2的優(yōu)化手段很多，如果想深入了解，上傳幾個文件供參考。
附件：
（以下附件在如下地址可下載：http://www.talkwithtrend.com/Question/403149）
DB2BP_System_Performance_0813.pdf
DB2BP_Query_Tuning_0508I.pdf
DB2BP_Storage_1009I.pdf
DB2BP_Physical_Design_OLTP_0412.pdf
DB2BP_Physical_Design_0508I.pdf
03
如何監(jiān)控到db2某個時段內(nèi)發(fā)生的sql？以及sql的響應(yīng)時間和資源消耗情況？
這是個共性問題，實(shí)現(xiàn)這個目標(biāo)的DB2工具也比較多，例如：
1)SNAPSHOT管理視圖，示例腳本如下:
db2 'select SNAPSHOT_TIMESTAMP,NUM_EXECUTIONS,TOTAL_EXEC_TIME,STMT_TEXT from sysibmadm.snapdyn_sql with ur' | more
以上快照結(jié)果存儲在數(shù)據(jù)庫中，讀取和分析方便。
2)db2top工具，示例腳本如下:
a）db2top -d xdb -f test1.txt -C -m 5 -i 30
每隔30秒取得快照一次，時間段為5分鐘
b）db2top -d xdb -f test1.txt -b D
分析剛才取得的快照數(shù)據(jù)
以上快照結(jié)果存儲在文件中，讀取和分析可能不太方便，但是收集的信息寬度更大。
04
臨時表的創(chuàng)建和維護(hù)？
在做復(fù)雜業(yè)務(wù)分析時，一個存儲過程也會用到很多臨時表（存儲業(yè)務(wù)分析某一步的中間結(jié)果），這些表的數(shù)據(jù)經(jīng)常變化（每個周期都會被清空再裝入），還需要和別的表做關(guān)聯(lián)，那么這種表在建表的時候有什么要注意的嗎？為了提升程序性能，優(yōu)化時考慮這些表嗎？要建立索引嗎？runstats應(yīng)該保持在什么狀態(tài)？需要reorg嗎？
分享一：
這些臨時表不是會話表（DGTT 或 CGTT）吧？如果每次調(diào)用存儲過程生成的臨時表數(shù)據(jù)變化都比較大，建議在存儲過程中搜集統(tǒng)計信息（調(diào)用sysproc.admin_cmd('runstats on table <臨時表>')，因?yàn)榕R時表每次調(diào)用一般都清空，沒有必要reorg；建不建索引，具體看表關(guān)聯(lián)的需要，存儲過程一般是加工數(shù)據(jù)的，臨時表一般不需要建索引。另外，建議將存儲過程對應(yīng)的package綁定成 REOPT ALWAYS的，這樣每次調(diào)用該存儲過程都會根據(jù)最新的統(tǒng)計信息生成新的執(zhí)行計劃，通常也會提高性能。
分享二：
根據(jù)個人實(shí)踐經(jīng)驗(yàn)，分享如下2點(diǎn)：
1)如果是DGTT(DECLARE GLOBAL TEMPORARY TABLE)/CGTT(CREATE GLOBAL TEMPORARY TABLE)，則一般不必對其建立index，也不需對其進(jìn)行runstats、reorg，因?yàn)镈B2查詢優(yōu)化器在每次分析和執(zhí)行SQL時，都會對包含DGTT/CGTT的SQL進(jìn)行重新優(yōu)化并且生成新的最優(yōu)access plan。
2)如果不是DGTT/CGTT，而是自己create table x1的普通表，即使這些普通表經(jīng)常變化，只要建立index、runstats、reorg帶來的收益遠(yuǎn)遠(yuǎn)大于建立index、runstats、reorg耗費(fèi)的成本，就應(yīng)該建立index、runstats、reorg，否則不必。復(fù)雜業(yè)務(wù)分析的存儲過程，更加需要遵從這個“收益與成本原則”。例如，一個復(fù)雜分析，不建立index/runstats/reorg時查詢需要5分鐘時間；但是如果建立index/runstats/reorg需要耗時3秒種時間，而此時查詢提高到只需10秒時間;這樣的3秒種的成本，帶來了查詢時間減少4分50秒的收益，這樣的成本與收益是有利的。
05
怎么查什么數(shù)據(jù)占用了系統(tǒng)臨時表空間呢？
Db2 在排序、表關(guān)聯(lián)等處理時會用到系統(tǒng)臨時表空間，順序是Sortheap不足時溢出到臨時表空間對應(yīng)的bufferpool，buffpool再不足時溢出到磁盤。如果想看數(shù)據(jù)是否使用了系統(tǒng)臨時表空間、使用了多少，直接db2 list tablespaces show detail 看看系統(tǒng)臨時表空間的Used pages即可, 或者db2top 進(jìn)去看表空間（按 t ），再看系統(tǒng)臨時表空間上是否有Writes。
06
有個表空間一直都在增長，但是查數(shù)據(jù)庫正在執(zhí)行哪些sql又沒查到東西，請問是不是load的原因呢?
分享一：
如果沒有SQL在運(yùn)行，可以用db2 list utilities 或 db2pd -util 看看是否有l(wèi)oad在執(zhí)行，并找到load表的表空間或其索引表空間，對應(yīng)看一下。
分享二：
一點(diǎn)思路，僅供參考:
使用db2top -d xdb1，然后切換到工具的Table頁，看看這個表空間下哪些/那個表在一直讀寫。如果表空間在增長，但是在db2top中看不到該表空間下任何表在讀寫，再從load方面入手，適用db2 list utilities來看看有何進(jìn)展。
07
lob存儲優(yōu)化的問題？
請教個問題
我們有一個表很大，500G，lob大對象沒有采用內(nèi)聯(lián)方式，現(xiàn)在要想讓它的大小縮小點(diǎn)，
改成內(nèi)聯(lián)會有效果嗎？？
2.內(nèi)聯(lián)后有啥負(fù)面影響嗎？
（對查詢，DML等的性能方面）
v10.1的版本
分享一：
Db2 支持單獨(dú)存放大對象，也支持內(nèi)聯(lián)（INLINE)方式，將大對象字段數(shù)據(jù)和別的字段數(shù)據(jù)都存放在同一個頁面中，但是LOB的大小受到Db2 Pagesize 的限制，超過頁面大小還是會單獨(dú)存放。如果您的LOB數(shù)據(jù)大多小于32K，建議使用32K的表空間，LOB INLINE方式，并且開啟Db2 壓縮，如果是聯(lián)機(jī)系統(tǒng)，建議使用經(jīng)典壓縮（Static）方式，LOB數(shù)據(jù)通常會縮小2-3倍。由于Db2的交易日志是否壓縮取決于表是否壓縮，開啟LOB INLINE并壓縮后，數(shù)據(jù)庫的日志也會縮小很多，對于該表的交易性能也會大幅提升。查詢時，LOB INLINE通常也會提升性能，壓縮后變小使得內(nèi)存利用率更充分是一個方面，批量掃描數(shù)據(jù)時，可以順序的將LOB讀進(jìn)Db2的bufferpool，效率高，單獨(dú)存放時，每條記錄中的LOB字段需要1次隨機(jī)IO單獨(dú)讀取，導(dǎo)致性能低下，特別是是使用低性能磁盤的時候。
分享二：
縮小表可以使用壓縮
lob是否可以inline存儲取決于lob的實(shí)際長度，如果大于32K就無法使用inline存儲了
使用inline存儲性能會提高，沒啥壞處
分享三：
首先，你的lob字段應(yīng)該單獨(dú)分離出來，你不管怎么改，如果和你的業(yè)務(wù)表混在一起，速度都不會有什么提升，在就是你改什么連接，你的條件不變，查出來的數(shù)據(jù)都不會改變，如何減少？
分享四：
可以考慮試試inline
08
有沒有一些好用的db2數(shù)據(jù)庫優(yōu)化工具推薦？
IBM 官方的工具是DSM（Data Server Manager）,包含數(shù)據(jù)庫性能監(jiān)控、數(shù)據(jù)庫調(diào)優(yōu)（OQWT, Optim Query Workload Tuner, 原來主機(jī)DB2上的查詢調(diào)優(yōu)工具）、配置變更管理（數(shù)據(jù)庫參數(shù)變更、數(shù)據(jù)庫對象定義變更）、數(shù)據(jù)庫管理等功能。DSM支持歷史性能數(shù)據(jù)管理，不僅僅是實(shí)時性能監(jiān)控。另外，在實(shí)時性能監(jiān)控上，db2top就是一個很好的工具，可以幫助定位很多性能問題。
09
分區(qū)表刪除分區(qū)會不會出現(xiàn)索引失效的情況？
deatch 分區(qū)不會出現(xiàn)索引失效的情況。如果是分區(qū)索引（parttiitoned index，或本地索引），deatch分區(qū)成功后，被刪除分區(qū)和數(shù)據(jù)立刻不可見；如果是非分區(qū)索引（not partitioned index，或全局索引），detach分區(qū)成功后，Db2采用了異步 清理的方式，將對應(yīng)分區(qū)在全局索引上的頁面進(jìn)行清除處理，在清除期間，不影響對外提供服務(wù)，針對該表的DML操作依然可以正常進(jìn)行。
10
容量很大的庫，一般采取什么策略來進(jìn)行清理？是定期刪除，還是使用分區(qū)表？
對于數(shù)據(jù)庫容量很大的庫，一般采取什么策略來進(jìn)行清理？是定期刪除，還是使用分區(qū)表？不同的方案對日志需求，備份恢復(fù)策略有什么影響？
定期刪除使用load還是delete的區(qū)別？
分享一：
數(shù)據(jù)清理的策略和業(yè)務(wù)直接相關(guān)，不一定按業(yè)務(wù)時間分區(qū)，清理時做分區(qū)detach就可以，在數(shù)據(jù)倉庫和分析領(lǐng)域，歷史數(shù)據(jù)歸檔通常卻是可以采用分區(qū)detach方式的。
刪除（delete）通常消耗大量的日志，而分區(qū)detach則不會。另外一個可選的方案是MDC fast rollout, 通過設(shè)置db2set DB2_MDC_ROLLOUT=DEFERRED實(shí)現(xiàn)，當(dāng)delete 語句的where條件中只有MDC字段限定時，可以實(shí)現(xiàn)快速刪除并且只記很少的日志。
對于聯(lián)機(jī)系統(tǒng)，如果一次性刪除大量數(shù)據(jù)可能導(dǎo)致鎖升級，影響交易的并發(fā)性，建議多次小批量刪除，通常的方法是多次執(zhí)行：delete from (select * from <table name> where ... fetch first 10000 rows only);
detele不影響備份恢復(fù)策略；detach 是DDL操作，影響了表的定義，也就影響了表空間的MRT（Minimum Recovery Time），PIT恢復(fù)時必須恢復(fù)到detach操作時間點(diǎn)以后。
表清空可以采用load/import （load／import from /dev/null of del replace into <table name>) ，或是truncate （truncate table <table name> immediate), 或是關(guān)閉日志的操作（alter table <table name> activate not logged with empty table)，也可以是是帶任何條件的delete。除了delete需要記錄大量的日志外，別的操作記錄的日志很少或不記日志。
分享二：
第一，容量很大的必須用分區(qū)表
第二、刪除的可以可以按分區(qū)刪除，或者按分區(qū)歸檔數(shù)據(jù)。
分享三：
數(shù)據(jù)生命周期的管理是數(shù)據(jù)倉庫（容量很大的庫）的管理重點(diǎn)之一。
1.要有嚴(yán)格的建表審查機(jī)制，在表建立的時候就應(yīng)對表的數(shù)據(jù)增長有預(yù)期，選擇合適的表屬性
2.對于大容量表，分區(qū)表是最合適的，卸載分區(qū)和重新掛載分區(qū)都很方便
3.很少有表會全部清空，所以如果不是分區(qū)表一般都在做delete
4.備份一般都是增量的，花的時間比刪除還多
分享四：
采用分區(qū)表，以日期作為分區(qū)鍵，按分區(qū)的周期拆離分區(qū)
按這個方法跑了6年左右了，效果還行
