Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Perf Advice Needed: cache buffers chains, high waits, _db_block_hash_buckets

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

From: <hemantchitale_at_charteredsemi.com>
Date: Mon, 04 Mar 2002 00:28:19 -0800
Message-ID: <F001.0041DE8E.20020304002819@fatcity.com>


From my R11 11.0.3 system : (11.0.3 against 8.1.7.2.1 on Tru64, 4CPUs, db_block_lru_latches=16)

MIN(BUFFERS_PER) MAX(BUFFERS_PER) AVG(BUFFERS_PER) SUM(BUFFERS_PER)

---------------- ---------------- ---------------- ----------------
              69              119       93.8242188            96076

Do I have a problem ? Very long buffer-chains, huh ! Nobody's complaining about
performance. CPU util is generally 40% to 80% only occasssionally at 90%+ when there
are more than 6 reports running concurrently. Can't remember why I set db_block_lru_latches to 16, back in June 2001 when the DB
was 8.0.5

Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd

Jared.Still_at_radisys.com 01/03/2002 02:23 AM Sent by: root_at_fatcity.com

Please respond to ORACLE-L

                                                                                                               
             To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                                   
             cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)                                             
             Subject: Re: Perf Advice Needed: cache buffers chains, high waits,                                
             _db_block_hash_buckets                                                                            
                                                                                                               
                                                                                                               
                                                                                                               





>From my biggest problem child. No bind variables, tends to get busy at
times.
Mission critical of course.

MIN(BUFFERS_PER) MAX(BUFFERS_PER) AVG(BUFFERS_PER) SUM(BUFFERS_PER)

---------------- ---------------- ---------------- ----------------
               1               17       5.45231072             5545

1 row selected.

>From our production SAP system:

MIN(BUFFERS_PER) MAX(BUFFERS_PER) AVG(BUFFERS_PER) SUM(BUFFERS_PER)

---------------- ---------------- ---------------- ----------------
               1               13       4.05840759            41899

1 row selected.

Jared

James Manning <oracle_at_sublogic.com>
Sent by: root_at_fatcity.com
02/28/02 09:13 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:
        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 <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:
  INET: Jared.Still_at_radisys.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: 
  INET: hemantchitale_at_charteredsemi.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 Mon Mar 04 2002 - 02:28:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US