>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 [EMAIL PROTECTED] > -----Original Message----- > From: James Manning [SMTP:[EMAIL PROTECTED]] > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: =?iso-8859-8?Q?���_�����? INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
