Question: What does
the wait event ENQ: KO - FAST OBJECT CHECKPOINT mean?
Can you show an example of the ENQ: KO - FAST OBJECT
CHECKPOINT wait event? I get this event:
WAITING: enq: KO - fast object
checkpoint
Answer:
You see this enq: ko - fast object checkpoint event
because an object level checkpoint that happens when you run
direct path reads on a table.
When you run full table scan (or a parallel query
full-table scan), you will see direct path reads wait event.
But in the beginning you will also see
the enq: ko - fast object checkpoint wait
event. This will checkpoint any blocks that belong to the
object you are doing direct path read so that
latest change goes into the datafile.
Waiting for the enq: ko - fast object checkpoint means
that your session wants to run a full-table scan and has
sent the CKPT process a message with instruction to do an
object level checkpoint. The CKPT process, in turn, then
directs the database writer process (DBWR) to perform the
checkpoint.
Deepak Bhatnagar notes a test to see the enq: ko - fast
object checkpoint:
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.
Rampant author Mladen Gogala writes this about the enq:
ko - fast object checkpoint
Prior to Oracle Database 10g
administrators could specify the expected crash recovery
time (MTTR) by setting the value of a checkpoint-related
initialization parameter (FAST_START_MTTR_TARGET). They
could do so by using the MTTR advisory, which helps predict
the number of physical writes that would arise with
different MTTR target values.
With Oracle Database 10g, the
database can self-tune checkpoints activity to achieve good
recovery times with low impact on normal throughput.
With automatic checkpoint tuning,
Oracle Database takes advantage of periods of low I/O usage
to write out data modified in memory to the data files
without adverse impact on the throughput. Consequently, a
reasonable crash recovery time can be achieved even if the
administrator does not set any checkpoint-related parameter
or if this parameter is set to a very large value.
Another enhancement done in the
second release of Oracle Database 10g dramatically improves
the performance of object-checkpoint requests issued for
objects accessed through direct path reads, a situation that
can occur with parallel query.
Before an object can be accessed
through direct path reads, dirty buffers of the object must
be written to data files on disk via an object-checkpoint
request. Prior to Oracle Database 10g Release 2, the
checkpoint request is handled by issuing a checkpoint for
the tablespace the object belongs to, writing out all the
dirty buffers for the entire tablespace.
Since a large number of objects may
reside in the same tablespace, this implementation may cause
large number of unnecessary disk writes. With the new
release, a checkpoint request for a target object will only
write out the dirty buffers of that object, without
incurring any additional writes for the dirty buffers of
other objects"
Of course, this is a very important
new performance feature. Many people have noticed and
inquired about the "KO locks", queried v$lock_type and
didn't investigate any further, but this dramatically
changes the way the database functions.
It also dramatically impacts
performance consideration as a big buffer cache in which
large parts of a big table can be cached can cause a serious
I/O contention and a lock contention. I'm looking for a
mechanism to turn off this new behavior, at least until the
next patch version, if not until the next major version.
Reference: MOSC
bug 9881076
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2017
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
Remote Emergency Support provided by
Conversational
|
|
