Tuning the Buffer Cache
In Oracle database all the operations ( select, DML, DDL) are not done directly in the physical files, but in an intermediate memory area named Buffer Cache. If a row is modified, first the modification is done in this area. If you want to access a row, first Oracle will take a look in this area to see if the information is here. If the information is here the data is sent to the user or modified in function of the user request. In this case, Oracle name this event a HIT. If the information is not present in this area (in the Buffer Cache) the information is read from the disk and put in this memory zone (in the Buffer Cache). In this case, Oracle name this event a MISS.
One time modified and written to the disk or sent to the user, data is kept in this area for further use. If new data has to be brought in the Buffer Cache and in no room there, the oldest data is replaced by the newest one.
A well optimized database mustn't have too many reloads of the data in the Buffer Cache. To evaluate the reload of data The Buffer Cache Hit Ratio is calculated.
This ratio could be calculated for the entire instance (since the database was started):
SQL> SELECT (P1.VALUE+P2.VALUE-P3.VALUE)/ (P1.VALUE+P2.VALUE)*100 "BUFFER HIT RATIO" FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
2 WHERE P1.NAME=LOWER ('DB BLOCK GETS')
3 AND P2.NAME=LOWER ('CONSISTENT GETS')
4 AND P3.NAME=LOWER ('PHYSICAL READS');
BUFFER HIT RATIO
----------------
96.5158211
Ratio should be above 90%, if not increase the shared_pool_size
If the cache-hit ratio goes below 90% then:
* For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
* For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.”
If the Buffer Cache is well tuned and no full table scans run in the database, this Ratio must be grater than 90%.
SQL> alter system set db_cache_advice=on ;
System altered.
SQL> select NAME,SIZE_FOR_ESTIMATE "BC size(M)",SIZE_FACTOR,ESTD_PHYSICAL_READ_FACTOR es_phy_r_f,ESTD_PHYSICAL_READS es_phy_r from v$db_cache_advice;
NAME BC size(M) SIZE_FACTOR ES_PHY_R_F ES_PHY_R
-------------------- ---------- ----------- ---------- ----------
DEFAULT 28 .0909 2.1867 27531
DEFAULT 56 .1818 1.3393 16862
DEFAULT 84 .2727 1.1498 14476
DEFAULT 112 .3636 1.0095 12709
DEFAULT 140 .4545 1 12590
DEFAULT 168 .5455 1 12590
DEFAULT 196 .6364 1 12590
DEFAULT 224 .7273 1 12590
DEFAULT 252 .8182 1 12590
DEFAULT 280 .9091 1 12590
DEFAULT 308 1 1 12590
DEFAULT 336 1.0909 1 12590
DEFAULT 364 1.1818 1 12590
DEFAULT 392 1.2727 1 12590
DEFAULT 420 1.3636 1 12590
DEFAULT 448 1.4545 1 12590
DEFAULT 476 1.5455 1 12590
DEFAULT 504 1.6364 1 12590
DEFAULT 532 1.7273 1 12590
DEFAULT 560 1.8182 1 12590
20 rows selected.
Acc to above :-
CASE 1: IF BUFFER CACHE is set to 84 mb , then physical reads are 14476.
CASE 2: IF BUFFER CACHE is set to 112 mb , then physical reads are 12709.
CASE 3: IF BUFFER CACHE is set to 140 mb , then physical reads are 12590.
If the BC will be bigger than 140 Mb there is no gain on the database performance, so having a 140 Mb Buffer Cache could be a good solution for this system. The size of the Buffer Cache is managed by DB_CACHE_SIZE initialization parameter.
Resizing Buffer Cache:
Now check the maximum size that SGA that can grow on.Compare the SGA Parameter size with the calculated size from the Shared Pool, Buffer Cache and Redo log sizes along with the java pool.
Sql>show parameter sga_max_size
Sql>select sum(value) as "SGA Size" from V$parameter
Where name in ('Shared_pool_size','db_cache_size','log_buffer','java_pool-size');
If the size is very close then decrease one size in order to increase the other.
Check the shared_pool and buffer_cache sizes individually.
Sql>alter system set DB_cache_size=100M;
Sql>alter system set shared_pool_size=200m;
Example—
In my database
acc to above 3 cases BC can be set 140MB, so as to give satisfactory performance .
ALTER SYSTEM SET db_cache_advice=OFF;
-- Then, decrease the Buffer Cache size to 140 Megabytes
ALTER SYSTEM SET db_cache_size=140M
-- Increase the Shared Pool size equal to the amount u decrease buffer cache size i.e equal to buffer cache size – 140M let’s say 300M
ALTER SYSTEM SET shared_pool_size= 300M
