Re: Perf Advice Needed: cache buffers chains, high waits, _db_block_hash_buckets

Don't forget that Oracle has redesigned the whole area of hash buckets and hash latches.

In 8.1, the number of buckets is roughly 2 x db_block_buffers, but each latch covers multiple buckets (don't forget that a latch is about 160 bytes, so a noticeable amount of memory overhead)

Because each latch covers multiple buckets, the latches can be held for a short time, as they need only be held whilst one bucket is searched. Unfortunately, there is no easy way to see the number of buffered blocks per bucket.

Steve Adams has a formula relating db_block_buffers to the number of latches, but for most reasonable sized database, the answer is 1024. Typically you are likely to see between 64 and 128 buffers per latch.

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

Now running 3-day intensive seminars
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

-----Original Message-----
<hemantchitale_at_charteredsemi.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 04 March 2002 09:12
_db_block_hash_buckets

|$)CFrom my R11 11.0.3 system : (11.0.3 against 8.1.7.2.1 on Tru64,
4CPUs,
|db_block_lru_latches=16)
|
|MIN(BUFFERS_PER) MAX(BUFFERS_PER) AVG(BUFFERS_PER) SUM(BUFFERS_PER)
|---------------- ---------------- ---------------- ----------------
| 69 119 93.8242188 96076
|
|Do I have a problem ? Very long buffer-chains, huh ! Nobody's
complaining
|about
|performance. CPU util is generally 40% to 80% only occasssionally at
90%+
|when there
|are more than 6 reports running concurrently.
|Can't remember why I set db_block_lru_latches to 16, back in June
2001 when
|the DB
|was 8.0.5
|
|Hemant K Chitale
|Principal DBA
|Chartered Semiconductor Manufacturing Ltd

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).