BUFFER CACHE hits & DB_BLOCK_BUFFERS
I have 3 identikit 126.96.36.199 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:
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.
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.
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'! ;-)
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
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 10:48 AM.
Dont forget to stop the trace when done
"What is past is PROLOGUE"
I still have lots to learn. And I've only been a DBA for 7 years.
Also any advice on my queries re. SGA size and BLOCK BUFFERS?
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?
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...
Click Here to Expand Forum to Full Width