Ryan,
To see the number of cache buffers chains latches:
select count(*) from v$latch_children where name =3D 'cache buffers =
chains';
To see the number of hash buckets, which the cache buffers chains =
latches protect:
oradebug setmypid
oradebug dumpvar sga kcbnhb
Note that the values are both version dependent and buffer cache size =
dependent. In 8i and later, the number of buckets is =
next_prime(db_block_buffers*2). There's a formula for default number of =
cache buffers chains latches as well, but it's more complicated and I =
don't know it from memory. (If you're interested, do a search on IxOra, =
you'll find it.)
-Mark
-----Original Message-----
From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
Sent: Friday, February 13, 2004 11:03 AM
To: oracle-l_at_freelists.org
Subject: RE: Re: how does oracle manage hash maps?
how many buffer latch chains does oracle maintain? Im assuming its =
version dependent.=20
>=20
> From: "Khedr, Waleed" <Waleed.Khedr_at_FMR.COM>
> Date: 2004/02/13 Fri AM 09:25:14 EST
> To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
> Subject: RE: Re: how does oracle manage hash maps?
>=20
> Hash chains/buckets used for managing the buffer cache has nothing to =
do
> with the hash join. They are completely different topics.
> For a data blocks to come to buffer cache, a hash function gets =
applied to
> block address to determine which buckets it will end up in.
> For a hash a join to happen, the data needed are read either from =
buffer
> cache or directly from files, a hash function is applied to the values =
of
> the joined columns themselves, and the join process starts from here.
> The hash areas/buckets for the hash join is unrelated to hash buckets =
of the
> buffer cache.
>=20
> Regards,
>=20
> Waleed
>=20
> -----Original Message-----
> From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
> Sent: Friday, February 13, 2004 8:58 AM
> To: oracle-l_at_freelists.org
> Subject: Re: Re: how does oracle manage hash maps?
>=20
>=20
> I know how the LRU works with linked lists. I've written linked list =
code
> before.=20
>=20
> From your diagram I take it that, oracle builds the hash map in the =
pga. The
> number of buckets is a prime number.=20
>=20
> Its hard to read your diagram. I'm not quite sure how the hash map =
links to
> the buffer chain. In basic hashing methodology each hash bucket points =
to a
> specific value.=20
>=20
> Jonathan lewis stated that in making the hash map oracle grabs the =
actually
> values you want to return. However, those values don't go into the PGA =
with
> the hash map do they? I thought all data elements went into the buffer
> cache?=20
> >=20
> > From: Scott <oraracdba_at_yahoo.com>
> > Date: 2004/02/13 Fri AM 12:14:48 EST
> > To: oracle-l_at_freelists.org
> > CC: ryan.gaffuri_at_cox.net
> > Subject: Re: how does oracle manage hash maps?
> >=20
> > I hope this diagram shows up. Yahoo is not the best
> > for creating diagrams.
> >=20
> > This example applies to Oracle7 and Oracle8, although
> > in reality there may be multiple LRU chains. In
> > Oracle8i the LRU concept is replaced by a touch-count
> > algorithm but the idea is the same.
> >=20
> > LRU Chain:=09
> > Most Recent Doubly linked list Least Recent
> > +------+------+------+ - - - -
> > +------+------+------+------+------+
> > | | | | | | PTR | =20
> > | | |
> > +------+------+------+ - - - -
> > +------+------+------+------+------+
> > Newly used buffers | We search
> for
> > are placed at this end. | LRU just FREE buffers
> > | points from this
> end
> > LRUW Chain (Dirty List): | to of the LRU.
> > +------+------+ - - +------+ | buffer
> Dirty
> > buffers
> > | | | | | | headers. are
> moved
> > to
> > +------+------+ - - +------+ | LRUW
> if there=20
> > (Cleared by DBWR) | is room.
> > |
> > |
> > Hashing is |
> > based on DBA |
> > modulo the |
> > number of buckets. |
> > A LATCH protects |
> > each hash chain |
> > |
> > ,-------.
> > --------------------------------/----------------------+
> > Double
> > | Hash |
> > <------------------------------/---------------------+
> > | linked
> > | Bucket| ,--------. ,---------V----. =20
> > ,--------. | | hash=20
> > | 1 | ----->| Buffer |--->| Buffer =20
> > |---->| Buffer |--+ | chain.
> > `-------' <-----| Header |<---| Header =20
> > |<----| Header |<---+
> > | 40 | | 2 | =20
> > | 999 |
> > | | | | =20
> > | |
> > |--------| | | =20
> > | |
> > |Usr|Wait| | | =20
> > | |
> > `--------' `--------------' =20
> > `--------'
> > ,-------. | | | =20
> > :
> > |SO. |--------------' | | =20
> > =20
> > | | | | =20
> > =20
> > `-------' | | =20
> > =20
> > Buffer Handle | | =20
> > =20
> > state object | |
> > | | =20
> > (actual DB blocks)
> > | | =20
> > ,-------------------.
> > ,-------. | | =20
> > | 1 |
> > | Hash | | | =20
> > | |
> > | Bucket| | | =20
> > +-------------------+
> > | 2 | -->... | =20
> > `---------->| 2 |
> > `-------' <--... | =20
> > | |
> > | =20
> > +-------------------+
> > | =20
> > : :
> > | =20
> > +-------------------+
> > : =20
> > `-------------------------->| 40 Data Block |
> > : =20
> > | |
> > =20
> > +-------------------+
> > =20
> > : :
> > =20
> > +-------------------+
> > ,-------. =20
> > | 999 |
> > | Hash | =20
> > | |
> > | Bucket| =20
> > +-------------------+
> > | N | =20
> > | 1000 |
> > `-------' =20
> > | |
> > There are a PRIME =20
> > +-------------------+
> > number of Hash buckets. =20
> > =20
> >=20
> > --- Ryan <ryan.gaffuri_at_cox.net> wrote:
> > >=20
> > >=20
> > > When Oracle performs a hash-join it first creates a
> > > hashmap. This hash map is placed in the PGA. Now,
> > > Oracle uses linked lists to manage the buffer cache.
> > > Traditional hashing uses an array for the hash, then
> > > a linked list for the collisions. How does Oracle
> > > manage this?=20
> > >
> > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > >
> > ----------------------------------------------------------------
> > > To unsubscribe send email to:=20
> > > oracle-l-request_at_freelists.org
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at
> > > http://www.freelists.org/archives/oracle-l/
> > > FAQ is at
> > > http://www.freelists.org/help/fom-serve/cache/1.html
> > >
> > -----------------------------------------------------------------
> >=20
> >=20
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Finance: Get your refund fast by filing online.
> > http://taxes.yahoo.com/filing.html
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >=20
>=20
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>=20
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Feb 13 2004 - 10:18:28 CST
