control file parallel write 和control file sequential read 等待事件

在 数据库某份 AWR的 Backgroud Wait Events报告中 发现关于控制文件的等待事件，因为是第一次遇见，而且占的bg time比例很大，让我们来一探究竟关于控制文件的事件。
首先，当session把物理块写入控制文件的时候发生control file parallel write事件，发生在是进程DBWR上，取决于DBWR把信息写入控制文件的时间。
其产生的原因 如下：

• The session starts a control file transaction (to make sure that the control files are up to date in case the session crashes before committing the control file transaction)
• The session commits a transaction to a control file
• Changing a generic entry in the control file, the new value is being written to all control files

可行的方案是  

• Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.
• Use Asynchronous I/O if available on your platform.
• Move the controlfile copies to less saturated storage locations.

 当然也需要检查一下logfile文件是否被过度的交换使用，在视图 v$loglist 视图中检查logfie的使用频率。如果logfile太小，考虑增大避免logfile频繁交换。

点击(此处)折叠或打开

1. alter database drop logfile group 3;
   
2. ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 '/oracle/oradata/CURACAO9/redo03.log' size 500m reuse;
   
3. ALTER DATABASE ADD LOGFILE member '/oracle/oradata/CURACAO9/redo03b.log' reuse to GROUP 3;
   
4. alter system switch logfile;
   
5. alter system checkpoint;
   
6. alter database drop logfile group 2;

对于control file sequential read事件，控制文件是顺序写的，当然在读取的时候也是顺序读的， 该事件就是读控制文件消耗的时间
产生的原因

• Making a backup of the controlfiles
• Sharing information (between instances) from the controlfile
• Reading other blocks from the controlfiles
• Reading the header block

Control file sequential read waits occur when the control files are placed on the same physical disk, causing read-write head contention at the disk level.

Move the problematic controlfile copy to a less utilized disk. Use Asynchronous I/O if available.

当我检查以上信息时 ，不等不承认系统I/O瓶颈严重。


参考一下内容：
https://community.oracle.com/thread/370520?start=0&tstart=0
http://www.dba-oracle.com/m_control_file_parallel_write.htm
http://www.askmaclean.com/archives/control-file-sequential-read.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2121511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27039319/viewspace-2121511/