Re: Multiple copies of the same block in the buffer cache

The multiple appearance of the same block is part of Oracle's strategy for minimising work. When you update a block, then Oracle may clone it, then update the clone, leaving a trailing of slightly out of date blocks in the buffer for processes that want to get recent point in time versions of the block.

There is a parameter named something like _max_cr_dba_clone = 6 which limits this, as too many clones could result in heavy contention on the cache buffers chains
latch as all clones of a block go into the same hash bucket, and the latch has to be held whilst the bucket is searched.

Notwithstanding this parameter value, I
have seen (oracle 8.1.5 I think) up to 12 copies of a block in the buffer. But this isn't something I have seen recently.

The oddity probably shouldn't be anything to do with cloning. Remember that x$bh
is never guaranteed to be in any way
subject to conditions of read-consistency. 350,000 blocks is a fairly big buffer - so it is possible that by the time you have finished selecting from it, a number of
blocks have been cloned and the chain
you are following has been relinked a
number of times. The silliest consequence of this I saw was in an experiment I was running to try and pin a block. I kept get reports from x$bh that I had two different versions of a block that were both THE
current version of the block.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Vsevolod Afanassiev wrote in message
<4f7d504c.0203252255.1b8cc83b_at_posting.google.com>...


>When I look in V$CACHE I often see multiple (2-6) copies of the same

>block. Yes, I know that V$CACHE is an Oracle Parallel Server view,

>and it is not even created by default during installation.

>However, many people told me that V$CACHE is perfectly valid for

>non-OPS instances. What really puzzles me is that there are

>multiple copies of the same block even for the table that

>is queried/updated by only one session.

>

>Also, I noticed that the total number of rows in V$CACHE

>is slightly different from db_block_buffers. For example,

>I have db_block_buffers = 350,000 (database with 2K blocks)

>and select count(*) from v$cache returns 362,971.

>It may be a coincidence, but the difference between these two

>numbers (12,971) is approx. half of the total number of

>blocks with more that one copy (25,060).