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 <[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: James McCann > > 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). -- 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: [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).
