等待事件 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 等待事件


一些可行的解决方法：

调整数据库参数

1 调整异步IO   

2 增加日志组                  <<<<看日志自身状态确定

3 增加DB writer数量      <<<<IO撑不住，这个效果一般

4 解决存储自身问题

调整业务SQL

5 调优SQL消除全表扫和大排序 <<<<<核心解决方法

如果多数业务频繁大表扫描，无法避免，建议关闭该功能，此时内存压力会增大，CPU也会有压力，需要了解这些资源情况

6 调整隐藏参数

"_serial_direct_read"=NEVER

SYS@orcl1>SELECT   ksppinm, ksppstvl, ksppdesc FROM   x$ksppi x, x$ksppcv y WHERE   x.indx = y.indx AND  ksppinm ='_serial_direct_read';

KSPPINM                   KSPPSTVL                       KSPPDESC

------------------------- ------------------------------ ----------------------------------------------------

_serial_direct_read       auto                           enable direct read in serial

修改隐藏参数

alter system set "_serial_direct_read"=never;