Re: Cache hash chains

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 3 Mar 2015 23:26:09 +0100 (CET)
Message-ID: <584642625.172974.1425421569494.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hi Paul,
looks like the demo case from my blog post here: http://tinyurl.com/le2r9mr

The four buffers (headers) are caused by the update statements with a full table scan. It is a special case called "switch current to new buffer". I just mentioned and linked this (used) special case in my blog post, but did not describe it in detail.

Jonathan Lewis explained this code path in his book "Oracle Core: Essential Internals for DBAs and Developers" very good - just quoting from page 115:

"For example, if I update the block through a tablescan, Oracle will switch current to new buffer rather than update the block in place. This means yet another mechanism where buffers can go on and off the replacement list and on and off the hash chains. When the current goes into a new buffer, the previous version becomes a read-consistent copy, of course, so if you update the block five times in a row, you will have reached the limit of six clones set by parameter _db_block_max_cr_dba. So, to avoid getting too much garbage into the cache, and to keep the length of the hash chain short, Oracle will start switching the older clones on to the replacement list if you continue updating the block."

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Paul Harrison <cure_at_austin.rr.com> hat am 3. März 2015 um 22:50 geschrieben:
> Hi Team,
>
> The query below returns 4 rows. The database object BUFFCACHETEST has 4 database buffer headers(doubled linked list) attached to a hash bucket? Why
> do we have 4 database buffer headers linked to 1 working data set( database buffer cache block )?
>
>
> select HLADDR, decode(STATE,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,
> 'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16,
> 'flashfree', 17, 'flashcur', 18, 'flashna') as STATE, PRV_HASH, NXT_HASH,
> BA, DBARFIL, DBABLK
> from X$BH where OBJ = 78451 and DBABLK = 196393;
>
> ##
> update BUFFCACHETEST set NUM=13 where NUM=3;
>
> HLADDR STATE PRV_HASH NXT_HASH BA
> ---------------- ---------- ---------------- ---------------- ---------------- DBARFIL DBABLK---------- ----------
> 000000007A3E3548 xcur 000000007A3E38E8 0000000066FD2FF0 0000000066F04000 1 196393
> 000000007A3E3548 cr 0000000066FD2EA8 0000000066FD3138 0000000066F06000 1 196393
> 000000007A3E3548 cr 0000000066FD2FF0 0000000068FB7C50 0000000066F08000 1 196393
> 000000007A3E3548 cr 0000000066FD3138 000000007A3E38E8 0000000068C5E000 1 196393
>
> Thanks,
> Paul

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 03 2015 - 23:26:09 CET