- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
2#
发表于 2013-8-22 20:49:37
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
read by other session 7,384,622 387,112 52 51.18 User I/O
db file sequential read 3,751,120 200,498 53 26.51 User I/O
gc buffer busy acquire 4,458,115 68,376 15 9.04 Cluster
db file parallel read 193,931 25,286 130 3.34 User I/O
latch: cache buffers chains 2,396,773 21,078 9 2.79 Concurrency
大量物理读等待事件
Begin End
Buffer Cache: 8,704M 3,584M Std Block Size: 8K
Shared Pool Size: 13,568M 18,688M Log Buffer: 117,268K
buffer cache收缩 原因是 shared pool要膨胀
版本 11.1.0.6.0
memory_target 32749125632 使用11g AMM 管理
每秒有一些硬解析
Parses: 61.7 25.4
Hard parses: 3.2 1.3
Pool Name Begin MB End MB % Diff
java free memory 512.00 512.00 0.00
large free memory 254.97 254.97 0.00
shared CCursor 241.77 -100.00
shared KGH: NO ACCESS 3,306.37 3,415.77 3.31
shared KGL handle 390.43 346.48 -11.26
shared PCursor 417.36 398.51 -4.52
shared free memory 9,425.51 14,792.79 56.94
shared gcs resources 334.55 334.55 0.00
shared gcs shadows 238.09 238.09 0.00
shared kglsim object batch 309.00 309.00 0.00
shared sql area 1,436.76 1,167.22 -18.76
streams free memory 256.00 256.00 0.00
buffer_cache 8,704.00 3,584.00 -58.82
fixed_sga 2.04 2.04 0.00
log_buffer 114.52 114.52 0.00
shared free memory 有 14,792 MB ,但是怀疑有大量碎片
sql area evicted 23,134 6.41 2.64
sql area purged 26 0.01 0.00
action plan:
1、看下shared pool的 碎片化程度
2、 关闭shared_pool duration 特性
alter system set "_enable_shared_pool_durations"=false scope=spfile;
3、 关闭AMM 并考虑升级到 11.2.0.3
查看碎片 可以借助下面的脚本
- select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
- count(*) "Count" , max(KSMCHSIZ) "Biggest",
- trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
- from x$ksmsp
- where KSMCHSIZ<140
- and KSMCHCLS='free'
- group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
- UNION ALL
- select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
- count(*) , max(KSMCHSIZ) ,
- trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
- from x$ksmsp
- where KSMCHSIZ between 140 and 267
- and KSMCHCLS='free'
- group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
- UNION ALL
- select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
- count(*) , max(KSMCHSIZ) ,
- trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
- from x$ksmsp
- where KSMCHSIZ between 268 and 523
- and KSMCHCLS='free'
- group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
- UNION ALL
- select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
- count(*) , max(KSMCHSIZ) ,
- trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
- from x$ksmsp
- where KSMCHSIZ between 524 and 4107
- and KSMCHCLS='free'
- group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
- UNION ALL
- select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
- count(*) , max(KSMCHSIZ) ,
- trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
- from x$ksmsp
- where KSMCHSIZ >= 4108
- and KSMCHCLS='free'
- group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
- SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
- To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
- FROM X$KSMSP GROUP BY KSMCHCLS;
复制代码 |
|