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.