What is lock type 'KO' in v$lock table?
Bart the bear
Charles Hooper
> Does anyboy know?
Try here for an answer:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/enqueues.htm
Maybe this SQL statement will help:
SELECT
*
FROM
V$LOCKED_OBJECT;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Bart the bear
Charles Hooper wrote:
> Bart the bear wrote:
> > Does anyboy know?
>
> Try here for an answer:
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/enqueues.htm
>
> Maybe this SQL statement will help:
> SELECT
> *
> FROM
> V$LOCKED_OBJECT;
It doesn't tell me anything. What the heck is "multiple objects
checkpoint"?
Charles Hooper
A search of Metalink and other documentation fails to provide a
definition of "multiple object checkpoint", as does a Google search.
Given the amount of information that you have provided, the only
suggestion that I can offer is to interpret the phase literally - there
are more than one table, index, or other object that is waiting for a
checkpoint to complete.
Are you using RAC? Are you trying to determine how fast the database
instance can recover from a SHUTDOWN ABORT? Does the server have 256MB
of memory, while you have allocated 1.5GB to the database buffer cache,
you have redo logs set at 5MB each, and are trying to perform a full
table scan on a table that is 2GB in size?
What is happening in the system before you see the lock type "KO" in
the V$LOCK view? Have you checked the alert log, or looked for bdump
or udump logs?
Bart The Bear
Charles, here is what happens:
I have a script which has to update ~60M rows. I had to kill it and
re-start it. The new incarnation did not wait for a bunch of TX locks, it
was waiting for the following event:
ENQ: KO - fast object checkpoint
Surprisingly enough, this lock was held by the CKPT process, not PMON, as I've
expected. Very few undo blocks were actually consumed in v$undostat. My question
is what is "fast object checkpoint" and is there a new method of reclaiming resources
held by murdered transactions? Is it documented anywhere?
DA Morgan
> Does anyboy know?
>
I think the SQL statement you are looking for is:
SELECT type, name, description
FROM v$lock_type
WHERE type = 'KO';
and it reports back:
KO
Multiple Object Checkpoint
Coordinates checkpointing of multiple objects
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Charles Hooper
The clarification helps.
I will have to defer to the experts:
(Jonathan Lewis posting from 5/23/2006):
"Re: KO - I think that appeared in 10.2. There is a new entry in the
buffer header structure which allows for a linked list to be built
between buffer headers of the same object. This, of course, means yet
another little overhead when reading a block into the buffer in the
first place. But it is useful for truncates, drops, and shrinks, as it
avoids a massive scanning process if you drop a large object which has
not been subject to much update."
(Tom Kyte
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:968789918647):
WAIT #0: nam='enq: KO - fast object checkpoint' ela= 72
name|mode=1263468550
2=65551 0=1 obj#=-1 tim=1108075404820109
"It flushed the buffer cache of blocks for this segment.."
http://orafaq.com/parms/parm266.htm
_db_fast_obj_ckpt: enable fast object checkpoint, first available in
Oracle 10.2
PMON is responsible for cleaning up after killed sessions, the CKPT
process assists DBWr during a checkpoint by updating the file headers
of data files. The multiple objects may be the table and its indexes
that are being rolled back from the undo, but it could be that Oracle
also needs to roll back changes made by triggers on the table.
Tom Kyte's books provide a good description of what the PMON process
does:
"This process is responsible for cleaning up after abnormally
terminated connections. For example, if your dedicated server 'fails'
or is killed for some reason, PMON is the process responsible for
releasing your resources. PMON will rollback uncommitted work, release
locks, and free SGA resources allocatedto the failed process. In
addition to cleaning up after aborted connections, PMON is responsible
for monitoring the other Oracle background processes and restarting
them if necessary (and if possible)..."
Mladen Gogala
> Bart the bear wrote:
>> Does anyboy know?
>>
>>
> I think the SQL statement you are looking for is:
>
> SELECT type, name, description
> FROM v$lock_type
> WHERE type = 'KO';
>
> and it reports back:
>
> KO
> Multiple Object Checkpoint
> Coordinates checkpointing of multiple objects
And there lies the problem: what is checkpointing of objects and when does
it occur? The term checkpoint means, when referring to Oracle RDBMS,
flushing dirty buffers to database files and it happens on certain
occasions, like log switch or every 3 seconds. Judging by analogy, "object
checkpoint" would mean flushing dirty buffers for certain segment to the
disk. I am also very interested to learn what is it good for, when does it
happen and what exactly do KO locks protect? If my assumption is right,
"KO enqueue" will lock the entire segment or large parts of it, somewhat
like a shared table lock. That cannot be good for concurrency. I smell a
mysterious new feature here, which is supposed to improve performance but
will be most useful if turned off by using the undocumented parameter
mentioned by Charles below. That will probably be a great performance
feature in Oracle 11f, the same story as with cache fusion in Oracle 8i:
there was a bug which could hang the entire cluster and causing an
enormous amount of global waits. Cache fusion is of course, very useful
feature of Oracle 9.2 RAC implementations.
Mladen Gogala
> And there lies the problem: what is checkpointing of objects and when
> does it occur? The term checkpoint means, when referring to Oracle
> RDBMS, flushing dirty buffers to database files and it happens on
> certain occasions, like log switch or every 3 seconds. Judging by
> analogy, "object checkpoint" would mean flushing dirty buffers for
> certain segment to the disk. I am also very interested to learn what is
> it good for, when does it happen and what exactly do KO locks protect?
> If my assumption is right, "KO enqueue" will lock the entire segment or
> large parts of it, somewhat like a shared table lock. That cannot be
> good for concurrency. I smell a mysterious new feature here, which is
> supposed to improve performance but will be most useful if turned off by
> using the undocumented parameter mentioned by Charles below. That will
> probably be a great performance feature in Oracle 11f, the same story as
> with cache fusion in Oracle 8i: there was a bug which could hang the
> entire cluster and causing an enormous amount of global waits. Cache
> fusion is of course, very useful feature of Oracle 9.2 RAC
> implementations.
I found the description of this new feature in the following document:
"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. My analogy with the cache
fusion seems rather accurate.
yon...@yahoo.com
> On Sat, 23 Sep 2006 02:23:02 +0000, Mladen Gogala wrote:
>
> > analogy, "object checkpoint" would mean flushing dirty buffers for
> > certain segment to the disk. I am also very interested to learn what is
> > it good for, when does it happen and what exactly do KO locks protect?
> > If my assumption is right, "KO enqueue" will lock the entire segment or
> > large parts of it, somewhat like a shared table lock. That cannot be
> > good for concurrency. I smell a mysterious new feature here, which is
> > supposed to improve performance but will be most useful if turned off by
>
> I found the description of this new feature in the following document:
>
> www.oracle.com/technology/deploy/performance/pdf/twp_perf_database%20performance%20with%20oracle10gr2.pdf
>
> 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"
Jonathan Lewis briefly talked about segment checkpoint on p.30 of his
"Cost Based Oracle". I was also looking for more information about it.
The white paper you cited tells us that before 10gR2, it's actually
implemented as a tablespace checkpoint. I was wondering how the
checkpoint queue could have a more granular "sub-queue" or something.
Object or segment checkpoint sounds like a good idea not just for
direct path read, but I think also for truncate or drop table / drop
index (they shouldn't happen on production databases though). If the
feature is turned off, every direct path read probably incurs a whole
tablespace checkpoint.
Yong Huang
Mladen Gogala
> Jonathan Lewis briefly talked about segment checkpoint on p.30 of his
> "Cost Based Oracle". I was also looking for more information about it.
> The white paper you cited tells us that before 10gR2, it's actually
> implemented as a tablespace checkpoint. I was wondering how the
> checkpoint queue could have a more granular "sub-queue" or something.
>
> Object or segment checkpoint sounds like a good idea not just for direct
> path read, but I think also for truncate or drop table / drop index
> (they shouldn't happen on production databases though). If the feature
> is turned off, every direct path read probably incurs a whole tablespace
> checkpoint.
>
> Yong Huang
Why would that be? Other then that, I lived without object
checkpoints from the version 4 to version 10.1 and things were
fine. I don't see why would turning those new checkpoints off be
such a catastrophe. Checkpoint occurs when DB buffers are written to the
data files. Let's see what the documentation says:
Administrators Guide:
Checkpoint (CKPT) At specific times, all modified database buffers in the
system global area are written to the datafiles by DBWn. This event is
called a checkpoint. The checkpoint process is responsible for signalling
DBWn at checkpoints and updating all the datafiles and control files of
the database to indicate the most recent checkpoint.
and:
Concepts:
The DBWn process writes dirty buffers to disk under the following
conditions:
When a server process cannot find a clean reusable buffer after
scanning a threshold number of buffers, it signals DBWn to write.
DBWn writes dirty buffers to disk asynchronously while performing
other processing.
DBWn periodically writes buffers to advance the checkpoint, which is
the position in the redo thread (log) from which instance recovery
begins. This log position is determined by the oldest dirty buffer
in the buffer cache.
In all cases, DBWn performs batched (multiblock) writes to improve
efficiency. The number of blocks written in a multiblock write varies by
operating system.
Also, from the Note:265831.1 one would conclude that checkpoints are
global events which occur:
1) During the log switch
2) At predefined intervals (archive_lag_target)
3) When explicitly invoked by "alter system" command.
During the checkpoint, DBWR is instructed to write DB blocks back to the
database files and, during that time, CKPT process updates the data file
headers and control file.
Why would a direct read trigger a checkpoint? My understanding is that
checkpoint is a global event that is triggered when certain critical
conditions are met to provide the system with the new baseline and to
free some DB buffers. So called "object checkpoint" is an antithesis
of a global event because it's localized to a segment. So, instead of a
single, globally controlled event, we can now have an army of checkpoints,
occurring for every object being modified. Objects that qualify for
triggering a checkpoint would be tables, materialized views, partitions,
indexes and clusters. There are several questions here:
1) when checkpoints for multiple objects gang up, which process writes
the blocks down? Can the writers keep up with the new "local
checkpoints"? What about piggybacking? Is there "checkpoint escalation"
mechanism which would re-request a global checkpoint, if the local one
is big? How about RAC? Are object checkpoints global in nature? How
do different instances react? Global lock? Has anybody thought of the
artificial deadlocks that can occur in RAC configurations if multiple
CKPT processes start waiting for each other to finish, so that they can
enqueue their own local checkpoints?
2) Apparently, there is a locking problem. CKPT locks the segment in the
"KO" mode and processes that are trying to update the database are
unable to proceed. What happens if I have a large SGA that takes a
long time to write? Will my transactions be blocked every now and then
and will users be made to wait for the "local checkpoint" to finish?
That is the behavior that prompted me to start looking into the
problem. As a company which experiences sudden bursts of update
activity, I am worried that this will prevent other users from
updating the segment for a long time. What locks is "KO" mode
compatible with? Does it block readers?
3) What triggers the "object checkpoint"? With all due respect, I don't
believe that checkpoints are triggered by a direct read. Checkpoints
used to be necessary, high intensity events which needed to be
performed as infrequently as possible to preserve performance and
as frequently as necessary to ensure timely recovery. Checkpoint as
term was introduced with Oracle6. Until Oracle10.2 they were also
well documented. Unfortunately, some @#$% #### at Oracle Corp.
did an artificial benchmark, found the new mechanism to be faster and
made a fundamental change, without telling anybody.
Now, checkpoints were an essential ingredient of the database, something
that every DBA had to think about every now and then. Tinkering with that
mechanism can produce a disaster and should not be done without first
documenting it and then providing an off switch which would enable users
to return to the previous behavior if this one was not well understood or
creates any problems. Yet, none of those two very common sense actions
were taken by Oracle Corp. It was just skipped and shoved down our throats
in a very arrogant manner that makes me very, very angry. If PostgresSQL
ever becomes a serious alternative, I will definitely have another reason
for endorsing them.
PS:
---
I really hope that somebody from the Oracle Corp. is reading this group.
This is the support that they charge dearly for:
13-SEP-06 17:51:02 GMT
New info : mgo...@yahoo.com : I knew that I have to rebuild the database. My
question was more to
find out what can I do to prevent this from happening the next time.
14-SEP-06 05:21:22 GMT
.
UPDATE
=======
o Haven't seen any case/bug report like this issue
o It is not able to determine what is the cause of this issue, but it looks that someone co
py the 3_6304.dbf on top of 3_6305.dbf
o As we cannot determine what is the cause of the problem, cannot recommend anything to prevent the issue.
SR hard closed. I lost a standby database because the archiver suddenly
produced two equal archives with different sequences. I asked for help
and was told something like: sorry, we have no clue. Please, let us know
if this happens again. Not only "it" was not able to give me any useful
information, "it" was dragging things on for so long that my database was
already rebuilt when I requested escalation. Of course, nothing was done.
Some similar kind of cousin it is probably also responsible for such a
brilliant way of informing us about the new checkpoint mechanism.
Jonathan Lewis
news:pan.2006.09.24....@verizon.net...
>
> Why would that be? Other then that, I lived without object
> checkpoints from the version 4 to version 10.1 and things were
> fine.
No you didn't, you just didn't know they were happening.
> Why would a direct read trigger a checkpoint?
Because there may be blocks in the buffer with commited
changes that have not been written to disc. Since a direct
path read goes to disc, bypassing the buffer, it won't see
those changes. So you have to issue a "localised" checkpoint
for the object just before the query starts to run.
This feature existed back in (at least) 7.2 where I first met it
as the "extent checkpoint" - where I think Oracle issued a
checkpoint once per extent. At some point this turned into
a tablespace checkpoint because "extent checkpoints" were
a disaster (no checkpoint queue until 8.1). The it became the
segment checkpoint - then in 10.2 Oracle put a linked list
into the buffer header to deal with all the problems if handling
a single segment in large buffers.
--
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Mladen Gogala
> "Mladen Gogala" <mgogala.s...@verizon.net> wrote in message
> news:pan.2006.09.24....@verizon.net...
>>
>> Why would that be? Other then that, I lived without object
>> checkpoints from the version 4 to version 10.1 and things were
>> fine.
>
> No you didn't, you just didn't know they were happening.
That's probably because I wasn't waiting for them long enough to get
alarmed. I was alarmed by the long waits for the 'KO' enqueue.
>
>> Why would a direct read trigger a checkpoint?
>
> Because there may be blocks in the buffer with commited
> changes that have not been written to disc. Since a direct
> path read goes to disc, bypassing the buffer, it won't see
> those changes. So you have to issue a "localised" checkpoint
> for the object just before the query starts to run.
>
> This feature existed back in (at least) 7.2 where I first met it
> as the "extent checkpoint" - where I think Oracle issued a
> checkpoint once per extent. At some point this turned into
> a tablespace checkpoint because "extent checkpoints" were
> a disaster (no checkpoint queue until 8.1). The it became the
> segment checkpoint - then in 10.2 Oracle put a linked list
> into the buffer header to deal with all the problems if handling
> a single segment in large buffers.
Thanks! This is a logical explanation. That probably means that putting
a /*+ PARALLEL(tab,n) */ hint in my query will inevitably cause a "local
checkpoint"?
I moved to New York City, so if you ever decide to teach a tuning class
there, let me know. The one I attended in CT was fantastic.