Re: What is lock type 'KO' in v$lock table?

Bart The Bear wrote:
> On Fri, 22 Sep 2006 17:10:00 -0700, Charles Hooper wrote:
>
> > Bart the bear wrote:
> >> It doesn't tell me anything. What the heck is "multiple objects
> >> checkpoint"?
> >
> > 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?
>
>
> 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?

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)..."

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Sep 22 2006 - 20:27:05 CDT