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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anjo Kolk
INET: anjo_at_oraperf.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).
Received on Fri Mar 01 2002 - 05:33:17 CST
