RE: Cache hash chains
- From: "Paul Harrison" <cure@xxxxxxxxxxxxx>
- To: <contact@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Mar 2015 16:39:39 -0600
Hi Stefan,
I see now... So, the BA(Address of data block buffer) is the address of the
data block. In this case, I have 4 data blocks and each data block has a
buffer header(linked list) associated with it?
Thanks,
Paul
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Stefan Koehler
Sent: Tuesday, March 03, 2015 4:26 PM
To: oracle-l@xxxxxxxxxxxxx; cure@xxxxxxxxxxxxx
Subject: Re: Cache hash chains
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: @OracleSK
> Paul Harrison <cure@xxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
