No great genius has ever existed without some touch of madness.

Wednesday 25 January 2012

TUNING BUFFER CACHE


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