DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: BUFFER CACHE hits & DB_BLOCK_BUFFERS

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    BUFFER CACHE hits & DB_BLOCK_BUFFERS

    I have 3 identikit 9.2.0.4 databases sitting on the same Windows 2003 server.
    They are a production (A21L) db, a validation or qualification (A21V) db, and a Testing (A21T) db. All were built with same way with the same configurations.

    Performance on A21L has suddenly dipped since last Thursday (when network admins started some domain reconfigurations co-incidentally). As A21V and A21T are no longer in use I can’t compare performance on A21V with that on A21L. However I have examined some stats on both instances and the buffer cache hit ratio on A21V and A21T is > 99.9%, but on A21L it is 34% (!!!)

    Some background: since A21L went into production it has also been used by an external system as a datasource (read only). A21L is hammered by another database, via a link. None of this external use was included when we validated and benchmarked the system. So the early hit Ratios of > 99.9% in A21V and A21T, have been gradually reduced to 34% on A21L by the external use. However, this ratio has been constant for several months/years even and cannot – in isolation – explain the recent (last week) slump in performance.

    The init.ora params in all 3 dbs are as follows:

    DB_BLOCK_SIZE=4096
    DB_CACHE_SIZE=25165824
    DB_FILE_MULTIBLOCK_READ_COUNT=16

    Note that DB_BLOCK_BUFFERS is not explicitly set, but querying the parameters’ v$ view shows it set to zero. Note that is A21T and A21V this also applies, and the hit Ratio there is > 99.9%.

    Has DB_BLOCK_BUFFERS assumed a default value somehow? What is the guide for setting this parameter?

    My Buffer cache in all 3 dbs was set to 36Mb – which, I know, is low. What proportion of this should I allocate to DB_BLOCK_BUFFERS? All of it? Should I multiply 4096 (block size) by number n, to come to 36Mb and then set DB_BLOCK_BUFFERS to number n? Or should it be set to a figure that is, say, 50% of 36Mb?

    The server has 1Gb of RAM. It has 4 Oracle DBs on it: the 3 above and a further Dev version. What size SGA should I make on the 3 DBs: I’m assuming I can’t make each SGA .75Gb!!! (as 3 x .75 exceeds 1Gb).

    Sorry – to ask a couple of different points here: I need advice on sizing the SGA given a 1Gb physical memory available on the box; I also need advice on setting the DB_BLOCK_BUFFERS parameter.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    First of all, look at the buffer cache advice on the production instance to see what difference it would make to the physical reads if you resize the buffer cache.

    Secondly, a high BCHR can actually be a sign on a problem -- excessive use of indexes or inefficient SQL can cause very high logical reads without a corresponding increase in physicval reads, hence a high BCHR.

    Thirdly, the performance problem needs to be diagnosed in terms of wait events, by setting event 10046 to level 8 for a session that exhibits a performance problem, and then looking at exactly what caused the problem. It could easily be that the network configuration is causing excessive waits outside of the database entirely.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Third Para Dave:

    This is a VB application, how can I set a Trace level on a session running that connects from a .exe 3rd party app to the DB? (Need some help here dave, tracing sessions is not a 'strong point'! ;-)

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    find the session in the database (sid and serial# from v$session) then use dbms_system.set_ev to start a trace on that session

    e.g

    exec dbms_system.set_ev(8, 1158, 10046, 12, '');

    8 is the sid, 1158 is the serial#, 10046 is the trace you want, 12 is the level
    Last edited by davey23uk; 08-02-2006 at 09:48 AM.

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Dont forget to stop the trace when done
    "What is past is PROLOGUE"

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Gracias!

    I still have lots to learn. And I've only been a DBA for 7 years.

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Also any advice on my queries re. SGA size and BLOCK BUFFERS?

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Managed to trawl through some stuff: DB_BLOCK_BUFFERS is now deprecated, DB_CACHE_SIZE is nowthe param that governs this. Which makes it a bit easier, no?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yup!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Get this one.

    Performance issue appears to have been resolved.

    Network Admin discovers that a cable from the server is plugged into a 10Mb switch, and not a 100Mb switch. Moving it to the 100Mb switch appears to have resolved the problem and the system is running as it was prior to the 'slump' that began last Thursday morning.

    So after 2 days of DBAs scratching their heads because they can't find anything wrong, the Net Admins come in and save the day.

    Would it me imprudent to suggest that recent concerns over IT job security here may have some bearing on this? Particularly as Net Admins cannot explain how this might have happened, but did notice it first thing on Monday morning that it had occurred and thought it important enough to report to senior management...

    Anyone suspicious?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width