RE: Perf Advice Needed: cache buffers chains, high waits, _db_blo

>From one of our datamarts:

min = 19
max = 43
avg = 31.47
sum = 32220

>From a small HR DB:

min = 15
max = 39
avg = 26.35
sum = 26984

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: James Manning [SMTP:oracle_at_sublogic.com]
> Sent: Thu, February 28, 2002 7:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Perf Advice Needed: cache buffers chains, high waits,
> _db_block_hash_buckets
>
> [Mogens Nørgaard]
> > Amen. Contention for cache buffers chains means too much logical
> IO,
> > ie. find and exterminate heavy SQL.
>
> I don't see why the heavy SQL would result in the chain having 66 buffer
> heads in it, though, or why the sleep count would be so skewed.
>
> And my core question is still whether the number of buckets being
> non-prime is "normal" or not - it seems awfully wrong to me.
>
> That there's a lot of contention *is* a factor of the SQL, but the
> fact that it's so skewed to only a few chains is what worries me more.
>
> Once I have the contention down to a particular latch, but that latch
> protects a buffer chain with 66 buffer heads in it, how can I find out
> which ones of the 66 are generating the most attempts at that latch?
>
> Tell ya what - can I get a few ppl to run this query? It tells the
> min/max/avg for the number of buffers associated with each chain and if
> my numbers are high I can at least have a chance of spreading out the
> buffers over more chains (by upping the number of latches from 4k to 16k,
> 32, whatever) - it won't drop the actual IO any, of course, but since
> I don't have a hard fix on which buffers of the 66 are really the source
> of my contention, I'm not sure where to go from here.
>
>
> SELECT min(buffers_per), max(buffers_per),
> avg(buffers_per), sum(buffers_per)
> FROM (
> SELECT count(*) buffers_per, hladdr
> FROM x$bh b, all_objects o, v$latch_children v
> WHERE
> b.HLADDR=v.addr
> AND b.obj=o.object_id
> AND v.name LIKE '%cache buffers %'
> GROUP BY hladdr
> )
>
> My results:
> min = 39
> max = 119
> avg = 55.06
> sum = 225555
>
> If this shows to be about the same in other (well-tuned) Oracle DB's, then
> I won't worry as much about the number of buffers in each chain and would
> then focus on trying to isolate the specific buffers, then the source SQL
> causing the problem, etc.
>
> Given my previous sql trace analyses, I have a good idea what the problem
> SQL statement is, but it's a bit of a necessary evil right now (a join
> of a table (260k rows) and a materialized view (2k rows), 6 conditions
> in there where, and it gets executed a ton, probably on the order of 10x
> a second at peak) - all indexes that helped performance are created and
> around already. :( But, ideally I'd like to be able to prove this is
> the cause of the "hot buffers" before fixing anything.
>
> Thanks, guys!!
>
> James
> --
> James Manning <jmm_at_sublogic.com>
> GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E A0BF B026 EEBB F6E4
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: James Manning
> INET: oracle@sublogic.com
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author:   INET: adary_at_mehish.co.il

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).