Date: Tue, 3 Mar 2015 23:26:09 +0100 (CET)
From: Stefan Koehler <contact@soocs.de>
To: oracle-l@freelists.org, cure@austin.rr.com
Message-ID: <584642625.172974.1425421569494.JavaMail.open-xchange@app04.ox.hosteurope.de>
In-Reply-To: <003c01d055fc$032e4d30$098ae790$@austin.rr.com>
References: <003c01d055fc$032e4d30$098ae790$@austin.rr.com>
Subject: Re: Cache hash chains
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: contact@soocs.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 u=
pdate 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@austin.rr.com> hat am 3. M=C3=A4rz 2015 um 22:50 gesc=
hrieben:
>  Hi Team,
>=20
>  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( databa=
se buffer cache block )?
>=20
>=20
>  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 =3D 78451 and DBABLK =3D 196393;
>=20
>  ##
>  update BUFFCACHETEST set NUM=3D13 where NUM=3D3;
> =20
>  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
>=20
>  Thanks,
>  Paul
--
http://www.freelists.org/webpage/oracle-l