等待事件 enq: KO - fast object checkpoint

查看相关文档：

You see this event because object level checkpoint which happens when you run direct path reads on a segment (like with serial direct read full table scan or parallel execution full segment scans).
Now the question is how much of your sessions response time is spent waiting for that event - and whether the winning in query speed due direct read/parallel execution outweighs the time spent waiting for this checkpoint.
Waiting for KO enqueue - fast object checkpoint means that your session has sent CKPT process a message with instruction to do object level checkpoint and is waiting for a reply.
CKPT in turn asks DBWR process to perform. the checkpoint and may wait for response.
So you should check what the DBWR and CKPT processes are doing - whether they're stuck waiting for something (such controlfile enqueue) or doing IO or completely idle.

 

该等待事件是由于当进行TABLE FULL SCAN或并行查询整个段时，对象级别发生checkpoint,由于direct path read必须要从磁盘中读入到PGA中，因此必须等待checkpoint完成，将脏块写回磁盘。相当于写阻塞了读。当发生该等待事件，一个简单的查询就将变得非常慢。

1.查看检查点及写入进程

select sid,program from v$session where program like '%CKPT%' or program like '%DBW%';

2.查看这两个进程等待的内容

select sid,event,state,p1,p2,p3,seconds_in_wait from v$session_wait where sid in (select sid  from v$session where program like '%CKPT%' or program like '%DBW%');

3.发现 db file async I/O submit 等待事件

解决方法:从metalink 文档来看，即使disk_asynch_io参数设置为true，操作系统本身也支持AIO；也并不代表oracle就能进行AIO操作。可以看到，还必须设置filesystem_options参数，建议设置为ASYNCH或setall即可

4.查看参数 show parameter filesystemio_options 和 show parameter disk,并修改参数

附加解决方法:1.db_writer_processes

                      2.增加REDO GROUP