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.
