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

Mladen Gogala wrote:
> 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 Received on Sat Sep 23 2006 - 21:45:18 CDT