enq: KO - fast object checkpoint - a described bug in Oracle

Bug fixes on wait even "enq: KO - fast object checkpoint"

I found "enq: KO - fast object checkpoint" wait event one of the production database. When I serach from the metalink, I found this is one of bug where are fixed in higher versions:

Bug in : 11.2.0.2 / 11.2.0.3 
-- Bug 16342845  Excessive CPU in DBW processes for fast object checkpoints

Fixed on : 

•12.2 (Future Release)
•12.1.0.2 (Server Patch Set)
•11.2.0.4 (Server Patch Set)


See the below Documents:

1) Doc ID 16463153.8
2) Doc ID 16342845.8
3) Doc ID 1377830.1

Symptoms:

•Excessive CPU Usage
•Performance Affected (General)
•Waits for "enq: KO - fast object checkpoint"


But you analyze the application code and find which procedure / query is causing the issue:

Reason:1 - Analyzing "enq: KO - fast object checkpoint" enque

It is normal you get this wait event and this "slow" as TRUNCATE is a heavy and complex operation due to the fact that Oracle must guarantee database consistency even if there is a crash during this operation.

The only action you can make is to truncate less.

(You can also put this table in recycle buffer cache knowing this will slow down queries.)

Reason:2 -  If Less DBWR process

As per your system configuration increase DB writers. Follow Oracle document.

Reason:3 - Give value to DB_CACHE_SIZE

Fix value for DB_CACHE_SIZE parameter. Usually give 25% to 30% of SGA to this parameter. Follow Oracle document.

Reason:4 - "none" may be in filesystemio_options

Change filesystemio_option parameter "none" to "ASYNCH" in case better storage configuration or "SETALL". Follow Oracle document.

Here are some parameters I have changed ( SGA set based on my database requirement and DB_WRITERs based on my avialble cores and transaction ratios)

filesystemio_options= none # Set to 'ASYNCH' ( set SETALL for local disks)
sga_max_size =10737418240 # set to 12G
sga_target= 8589934592 # set 12G
DB_CACHE_SIZE = # set to 4G
DB_WRITER_PROCESSES = 2 set to 3

and Set " DB_KEEP_CACHE_SIZE" value to a non-zero value ( as granule_size * cpu_count )

Note: DB_KEEP_CACHE_SIZE is a dynamic parameter, you can change online.

You can calculate the value for " DB_KEEP_CACHE_SIZE" as like following:
Example:
1) No. of CPUs in the system/ server ( from show parameter cpu)
Assume CPU count is =10

2) Granule size :
SQL> connect / as sysdba;
SQL> select name,bytes from V$SGAINFO where name='Granule Size';

NAME                                  BYTES
-------------------------------- ----------
Granule Size                       33554432

i.e., 33554432*10=335544320 to be set for DB_KEEP_CACHE_SIZE parameter.

If above steps not help you, you may follow below steps with the help of Oracle Support. Must be executed in complete off peak time.

 Check below kernel parameters in your linux environment. If any thing missing add it.

$cat /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768

Set the below value :

# ulimit -Hs 32768
-- Check the output
# ulimit -Hs
32768

-- If you are observing the same issue again, change the below parameter and fush buffer_cache and shared_pool. ( you may do at your own risk).

SQL> connect / as sysdba;
SQL> alter system set "_db_fast_obj_ckpt"=FALSE';
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> alter system flush shared_pool;

I hope this may help you.
Add your feedbacks....