AWR
分析过程如下:
Top 5
中表明
log file
可能存在问题;
log file sync
、
log file parallel write
的
Avg Wait(ms)
都偏高;
Top 5
提供优化基本方向可能是
log file
问题,继续向下分析!
Log file parallel write
的
Avg Wait(ms)
指标超过
20
,根据经验意味着存在
IO
争用了。
说明
redo log files
存在
IO
争用
Per hour=9.95
约
6
分钟切换一次,这个是远高于
15~20
分钟公认的切换一次
说明
redo log files
过小
User calls/(user commits+user rollbacks) <30
这个时候数据库
commit
是频繁的
说明
commit
频繁
以上为分析
AWR
全过程!
分析出的问题:
1、
redo log files
存在
IO
争用
2、
redo log files
过小
3、
commit
频繁
解决方案:
1
、
log file parallel write IO
争用
:建议更换
IO
性能高的磁盘,此系统为在线生产系统目前先不做更换,做好更换的规划
2
、
log switches (derived)
:
添加日志组的大小
3
、
COMMIT
频繁
:
3-1
、把一些可以批量提交的事务批量处理
3-2
、一些进程可以
commit nowait
3-3
、适量的使用
nologging
附表:转
MOS
文档
1376916.1
(排除
log file sync
思路)
Troubleshooting: "log file sync" Waits (
文档
ID
1376916.1)
What is a 'log file sync' wait?
When a user session commits, the session's redo information needs to be flushed from memory to the redo logfile to make it permanent.
At the time of commit, the user session will post the LGWR to write the log buffer (containing the current unwritten redo, including this session's redo information) to the redo log file. When the LGWR has finished writing, it will post the user session to notify it that this has completed. The user session waits on 'log file sync' while waiting for LGWR to post it back to confirm all redo changes have made it safely on to disk.
The time between the user session posting the LGWR and the LGWR posting the user after the write has completed is the wait time for 'log file sync' that the user session will show.
Note