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

My most famous example of this was at a customer in Belgium, where people will either speak French or Flemish. Everytime the application put a text on the screen it would ask the database if the end-user wanted it in French or Flemish. Guess how often people change their mind during the day....

Rgds, Bjørn.

On Friday 01 March 2002 12:33, you wrote:
> James,
>
> YES YES !
> In tuning SQL people always look at the cost of the Access Plan (number
> LIOs basically), but the first question that you need to ask is "Do I need
> to execute this statement?".
> There are numerous examples of SQL statements that are very efficient, but
> just executed way too many times. If you database suffers from having the
> segment header of DUAL as one of the hottest blocks in your database, you
> have very efficient statements, but just executed too many times .....
>
> Anjo.
>
> James McCann wrote:
> > Hi,
> > while we are on this topic, I would to ask you all about a system I
> > was working on recently.
> >
> > The main problem was that approx. 64 threads were almost continuously
> > doing full table scans on a small table of 800 rows (the developers
> > insisted this was necessary). This table was in 1 or 2 blocks and was
> > having a huge amount of "cache buffers chains" latching.
> >
> > When we forced the sql to use the index, the latching moved to the index.
> >
> > I did all the usual tricks like spreading the table out across a lot more
> > blocks , increase the spin count etc. with limited success.
> >
> > My view was that with so much activity going on on this small table, the
> > latching would never be fully eliminated, and it was poorly designed
> > code.
> >
> > I just want to check that everyone agrees with me, or would you expect to
> > be able to eliminate the waits even under these conditions?
> >
> > Thanks for your advice,
> >
> > Jim
> >
> > -----Original Message-----
> > Manning
> > Sent: 28 February 2002 17:14
> > To: Multiple recipients of list ORACLE-L
> > _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_at_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: James McCann
> > INET: james_at_openet-telecom.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).

-- 
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  INET: bjorn_at_MiracleAS.dk

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