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…