Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is lock type 'KO' in v$lock table?

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

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Sat, 23 Sep 2006 17:27:28 GMT
Message-Id: <pan.2006.09.23.17.27.26.899379@verizon.net>


On Sat, 23 Sep 2006 02:23:02 +0000, Mladen Gogala wrote:

> 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:

www.oracle.com/technology/deploy/performance/pdf/twp_perf_database%20performance%20with%20oracle10gr2.pdf

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

-- 
http://www.mgogala.com
Received on Sat Sep 23 2006 - 12:27:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US