Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Perf Advice Needed: cache buffers chains, high waits, _db_block_hash_buckets
Hi,
while we are on this topic, I would to ask you all about a system I was working on recently.
When we forced the sql to use the index, the latching moved to the index.
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 %'
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).Received on Fri Mar 01 2002 - 04:33:21 CST
![]() |
![]() |