enq: KO - fast object checkpoint事件优化

在测试库上跑存储过程，更新300万条数据，不经意在另一个窗口观察等待事件：

SQL> select sql_text,event from v$session a,v$sqlarea b where a.sql_id=b.sql_id and a.program not like '%JDBC%' and a.event not like  '%mess%';

SQL_TEXT                                                                                             EVENT

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

UPDATE HX_FP.FP_WLFP_KJXX_CYH1 B SET B.SFYYJ='X' WHERE B.UUID=:B1                                    free buffer waits

select count(*) from hx_fp.FP_WLFP_KJXX_CYH1                                                         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完成，将脏块写回磁盘。相当于写阻塞了读。当发生该等待事件，一个简单的查询就将变得非常慢。

                                                                                                     

SQL> select a.sid,a.event,a.state,a.p1,a.p2,a.p3,a.seconds_in_wait,b.program from v$session_wait a, v$session b where  (b.program like '%CKPT%' or b.program lik

       SID EVENT                          STATE                       P1         P2         P3 SECONDS_IN_WAIT PROGRAM

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

      1134 db file async I/O submit       WAITING                   2023          0          0            2 oracle@gnhxdb01 (DBW0)

      2266 rdbms ipc message              WAITING                    300          0          0            1 oracle@gnhxdb01 (CKPT)

通过以上查询可以看到DBWN发生数据文件异步写等待（当设置DISK_ASYCH_IO=false时该等待将变为db file parallel write，其实就是换汤不换药），发生该类等待说明当前磁盘繁忙，CKPT进程在等待rdbms ipc message事件说明CKPT进程正在等待前台进程对其发送IPC消息。

观察主机发现IO子系统一直比较繁忙，由于是测试机，因此尝试增大db_writer_processes，增加REDO GROUP后，情况有所好转，但该等待事件还是存在。修改存储过程为批绑定后，该等待事件彻底消失。

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

转载于:http://blog.itpub.net/23371754/viewspace-757216/