BPHR%=(1-((729703541+15869646)/( 22387318588+7098769339)))*100
= 97.4714408
緩衝池一: IBMDEFAULTBP
Buffer pool data logical reads = 480958147
Buffer pool data physical reads = 753565
Buffer pool index logical reads = 154431264736
Buffer pool index physical reads = 27011133
BPHR%=(1-((753565+27011133)/( 480958147+154431264736)))*100
=99.9820771
結論/建議:從檢查時間時刻來看,buffer命中率良好
11.3.2 LOCK
數據庫相關鎖的snapshot信息如下：
Locks held currently = 47
Lock waits = 24429
Time database waited on locks (ms) = 224678916
Lock list memory in use (Bytes) = 85888
Deadlocks detected = 669
Lock escalations = 368
Exclusive lock escalations = 368
Agents currently waiting on locks = 0
Lock Timeouts = 23
Internal rollbacks due to deadlock = 547
相關數據庫配置：
Max storage for lock list (4KB) (LOCKLIST) = 65536
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = 600
鎖平均等待時間= 224678916/24429=9197.22117 ms
結論/建議: 應用中有死鎖和由於死鎖產生回滾的現象,鎖等待平均時間偏長,參數locktimeout即使是olap應用設置也有點相對偏高。建議結合應用特點降低lock timeout參數配置
11.3.3 SORT
檢測到的sort信息如下：
Total Private Sort heap allocated = 50000
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 237500
Total sorts = 14580461
Total sort time (ms) = 377549081
Sort overflows = 154890
Active sorts = 2
Commit statements attempted = 23266951
Rollback statements attempted = 569882
指標計算：
SortsPerTransaction=14580461/(23266951+569882)= 0.611677776
PercentSortOverflow=(154890*100)/ 14580461=1.1%
結論／建議: 對於SortsPerTransaction的經驗參考值為<5, PercentSortOverflow<3%,上述情況屬於正常範圍之內，未發生嚴重的未曾預料到的大型排序。
11.3.4 AGENT
Agent相關信息如下：
High water mark for agents registered = 338
High water mark for agents waiting for a token = 0
Agents registered = 34
Agents waiting for a token = 0
Idle agents = 1
Agents assigned from pool = 0
Agents created from empty pool = 434132276
Agents stolen from another application = 0
High water mark for coordinating agents = 114
Max agents overflow = 0
Agents associated with applications = 28
Maximum agents associated with applications= 325
Maximum coordinating agents = 79
Agents currently waiting on locks = 0
相關配置:
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 500
Agent pool size (NUM_POOLAGENTS) = 0
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS – NUM_INITAGENTS)
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
SPM resync agent limit (SPM_MAX_RESYNC) = 20
分析: High water mark for agents registered < 500
High water mark for agents waiting for a token = 0
Max agents overflow = 0
結論/建議:對於olap系統， NUM_POOLAGENTS,NUM_INITAGENTS參數設置為0,無問題.但對於
Oltp系統應該配置一個合理的值,以加快並發連接.
對於經分應用特點,Agent設置無問題，使用良好
11.3.5 LOG
Log使用信息:
Log space available to the database (Bytes)= 48548940041
Log space used by the database (Bytes) = 650245879
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 31267285053
Secondary logs allocated currently = 0
Log pages read = 1003368
Log read time (sec.ns) = 961.000000004
Log pages written = 1021638479
Log write time (sec.ns) = 394019.000000004
Number write log IOs = 328312533
Number read log IOs = 125437
Number partial page log IOs = 256773865
Number log buffer full = 114484
Log data found in buffer = 14366966
Appl id holding the oldest transaction = 1114246
Log to be redone for recovery (Bytes) = 1031361758
Log accounted for by dirty pages (Bytes) = 1031361758
Node number = 1
File number of first active log = 38
File number of last active log = 37
File number of current active log = 39
分析：
當明事務日誌使用率: 650245879/48548940041=1.3%
最高日誌使用率: 31267285053/48548940041=64.4%
日誌頁面讀/寫比: 1003368/1021638479=0.98%
結論/建議: 日誌頁面讀/寫比理想狀態下值為0.可適當的增加LOGBUFSZ值. 最高日誌使用率曾達到過64.4%,應該加強對日誌使用率的檢測，關注應用事務是否正常提交
11.3.6
PKGCACHE/CATALOGCACHE
包緩存與編目緩存信息如下:
Package cache lookups = 95392532
Package cache inserts = 5226692
Package cache overflows = 7
Package cache high water mark (Bytes) = 8600159
Application section lookups = 180378750
Application section inserts = 61015428
Catalog cache lookups = 3381776
Catalog cache inserts = 141501
Catalog cache overflows = 0
Catalog cache high water mark = 0
PCHR=(1-(5826692/95392532))=93.89%
CCHR=(1-(141501/3381776))=95.81%
相關配置:
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 4096
Package cache size (4KB) (PCKCACHESZ) = 2048
結論/建議:PCHR命中率偏低,<95%
Package cache high water mark> CATALOGCACHE_SZ(8600159>2048*4*1024))
併產年了Package cache overflows現象, 建議增大PCKCACHESZ值
11.3.7 FCMBP
節點通信管理器信息如下:
Node FCM information corresponds to = 1
Free FCM buffers = 64926
Free FCM buffers low water mark = 20254
Free FCM message anchors = 4853
Free FCM message anchors low water mark = 4082
Free FCM connection entries = 4792
Free FCM connection entries low water mark = 551
Free FCM request blocks = 6238
Free FCM request blocks low water mark = 1586
Number of FCM nodes = 32
FCM配置信息如下:
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 65536
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC(6400)
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC(4864)
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC(-1)
分析:
Free FCM buffers low water mark/ FCM_NUM_BUFFERS:20254/65536=30.9%
Free FCM connection entries low water mark/FCM_NUM_CONNECT:956/4864=11.33%
Free FCM request blocks low water mark/ FCM_NUM_RQB : 1586/6400=24.78%
結論/建議:
參照ibm對該參數設置參考:,“Free FCM buffers low water mark”小於 FCM_NUM_BUFFERS 的百分之 15，那麼可以增加 FCM_NUM_BUFFERS，直到
“Free FCM buffers low water mark”大於或等於 FCM_NUM_BUFFERS 的百分之 15，以確保總有足夠的 FCM 資源可供使用.
目前節點通信指標性能無問題
第12章 備份恢復
圖 2. DB2 UDB 體系結構
包緩存 —— 為存儲靜態和動態 SQL 語句而分配的內存。
緩衝池 —— 在將數據刷新到磁盤之前，為存儲數據而分配的內存。
日誌緩衝區 —— 在將所有對數據庫的更改刷新到磁盤上的日誌之前，用來存儲這些更改的內存。
以下文章點擊率最高
Loading…