- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
4#
发表于 2012-5-16 15:42:20
以 11.1.0.7.0 Linux x86 64-bit Inst num 4为例
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
gc buffer busy acquire 54,450 17,029 313 47.92 Cluster
gc cr multi block request 109,957 9,150 83 25.75 Cluster
DB CPU 4,133 11.63
read by other session 2,690 1,238 460 3.48 User I/O
gc cr block lost 1,522 998 656 2.81 Cluster
TOP 5中 gc buffer busy acquire 占了大头
Estd Interconnect traffic (KB) 1,767.45 =》评估的流量每秒 1.7MB 并不大
gc block的 flush time 并不高
Avg global cache cr block flush time (ms): 1.3
Avg global cache current block pin time (ms): 0.3
Avg global cache current block send time (ms): 0.1
Global cache log flushes for current blocks served %: 0.4
Avg global cache current block flush time (ms): 1.7
Cluster Wait Time (s) %Ela %Total Elapsed Time(s) CPU Time(s) Executions SQL Id SQL Module SQL Text
5,088.03 87.61 17.77 5,807.88 11.00 10 fny3k7jzhkhwd JDBC Thin Client select t1.moentityid from pm4h...
1,235.06 77.64 4.31 1,590.70 4.37 1 9gw7y7xk8z876 JDBC Thin Client merge into INDICATOR_75502_1 m...
1,040.64 36.65 3.63 2,839.35 1,750.20 115,805 a6wd5ddqyx4g3 JDBC Thin Client select t1.indicatorid, t1.indi...
SQL ID fny3k7jzhkhwd 1 9gw7y7xk8z876 a6wd5ddqyx4g3 产生了大量的 cluster wait time
db_block_size 32768 32k的 block size 本身可能加剧 gc busy buffer
Segments by Global Cache Buffer Busy
% of Capture shows % of GC Buffer Busy for each top segment compared
with GC Buffer Busy for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type GC Buffer Busy % of Capture
PM4H_DB PM4H_DB0_2 COUNTERLOADMESSAGE P2 TABLE PARTITION 19,468 39.37
PM4H_AD PM4H_DB0 CFG_SYSPARAMETER P1 TABLE PARTITION 9,489 19.19
PM4H_AD PM4H_AD MDL_INDICATOR TABLE 8,993 18.19
主要引起 Global Cache Buffer Busy 的是 COUNTERLOADMESSAGE.P2 和 CFG_SYSPARAMETER.P1 这2个 TABLE PARTITION
Interconnect Ping Latency Stats
Ping latency of the roundtrip of a message from this instance to
target instances.
The target instance is identified by an instance number.
Average and standard deviation of ping latency is given in miliseconds
for message sizes of 500 bytes and 8K.
Note that latency of a message from the instance to itself is used as
control, since message latency can include wait for CPU
Target Instance 500B Ping Count Avg Latency 500B msg Stddev 500B msg 8K Ping Count Avg Latency 8K msg Stddev 8K msg
1 573 3.60 6.57 573 4.50 6.44
2 573 4.67 4.66 573 5.25 4.84
3 573 4.04 5.95 573 5.05 5.96
4 573 0.12 0.00 573 0.11 0.00
5 573 4.33 4.38 573 4.76 4.35
ping Latency 较高 500B 达到 4-6ms
建议:
1.诊断 网络是否存在问题, 就AWR看 存在较高的延迟 4-6ms , 这不应当是 interconnect network的latecy
2. 考虑使用非标准块的 block size buffer pool, 如4k、8k 存放gc busy buffer高的对象 减少 争用
3. 调优SQL 语句 |
|