Buffered Read or Direct Path Read? Understand query behavior with the help of "ENQ: KO - FAST OBJECT CHECKPOINT" event.

Today while doing some performance research on wait event "ENQ: KO - FAST OBJECT CHECKPOINT", I experienced that it's not Query Optimizer who decides, whether query will perform Buffered Read or it will perform Direct Path Read operation. Optimizer just decides the access method of operations involved in query execution. For example, Optimizer just decides if query will use FULL TABLE SCAN or INDEX SCAN. But FULL TABLE SCAN will be performed by buffered read operation or direct path read, it is decided at query run time during the execution phase, not in parsing phase.

To demonstrate this, I performed a little experiment. I performed two simple tests.

In the first test case, I forced "ENQ: KO - FAST OBJECT CHECKPOINT" wait event. I updated a single row of a big table, then I performed a full table scan operation and monitored the behavior of SELECT query. In the second test case, I updated 99% rows of a big table, then again performed a full table scan and again I monitored the behavior of SELECT query.

The results were little surprising...

During the first test case, I updated only 1 row of the table TEST_A. When I executed SELECT query,  it performed direct path read because table size is big as compared to buffer cache size. Before direct path read, a wait event ENQ: KO - FAST OBJECT CHECKPOINT was posted. This is because, before reading data blocks from data file directly to PGA,  object level checkpoint was occurred and dirty blocks of the table TEST_A were written from buffer cache to data file. 

In the second test case, I updated 99% rows of the table TEST_A.  During update operation, buffered read operation was performed. But this time, when I performed SELECT query (Full table scan), query did not performed DIRECT PATH READ, but it performed buffered read. This is because, most of table block data (buffers) were already found in buffer cache, so query did not perform DIRECT operation and hence event ENQ: KO - FAST OBJECT CHECKPOINT did not occur.

 Conclusion -

Important to note here is that SQL_ID, HASH VALUE and PLAN HASH VALUE of SELECT query are same in both the cases. So, we can conclude that optimizer has chosen same plan in both cases, but at run time, query performed differently.