Buffer Cache Tuning


Buffer States:

  1. Pinned: Currently used.
  2. Clean: Meaning the buffer is now unpinned and is a candidate for immediate aging out if the current (data blocks) are not referenced again. The contents are either in synch with disk or the buffer contains a CR snapshot of a block.
  3. Free/Unused:  Meaning the buffer is empty because the instance just started. This state is very similar to Clean, except that the buffer has not been used.
  4. Dirty: Buffer is no longer pinned but the contents (data block) have changed and must be flushed to disk by DBWn before it can be aged out.

 Three Buffer Pool:

  1. Keep
  2. Recycle
  3. Default 

Database Buffer Cache Parameter:

  1. DB_CACHE_SIZE (BLOCK SIZE will be the size declare in DB_BLOCK_SIZE)
  2. DB_KEEP_CACHE_SIZE
  3. DB_2K_CACHE_SIZE
  4. DB_4K_CACHE_SIZE
  5. DB_8K_CACHE_SIZE
  6. DB_16K_CACHE_SIZE
  7. DB_32K_CACHE_SIZE
  8. DB_RACYCLE_CACHE_SIZE
  9. DB_BLOCK_SIZE(Default Block size which can not be changed later anyway)

 

SQL> SHOW PARAMETER DB_BLOCK_SIZE;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     8192

 

SQL> CREATE TABLESPACE TEST

             DATAFILE 'D:\oracle\oradata\dba50\test01.dbf' SIZE 200M

              BLOCKSIZE 2K;

CREATE TABLESPACE TEST

*

ERROR at line 1:

ORA-29339: tablespace block size 2048 does not match configured block sizes

 

SQL> alter system set db_2k_cache_size=2M;

alter system set db_2k_cache_size=2M

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

 

NOTE: Because SGA MAX size already fill-up so need to increase SGA_MAX size or decrease database buffer cache size/redolog buffer cache size/shared pool cache size.

 

SQL> show parameter db_cache_size;

 NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 25165824

 

SQL> alter system set db_cache_size=20M;

 System altered.

 

SQL> alter system set db_2k_cache_size=2M;

 System altered.

 

SQL> CREATE TABLESPACE TEST

           DATAFILE 'D:\oracle\oradata\dba50\test01.dbf' SIZE 200M

           BLOCKSIZE 2K;

 Teblespace created.


Show Buffer Cache Hit:

SQL> select (1-((physical.value-direct.value-lobs.value)/logical.value))*100

           "Buffer hit"

            from v$sysstat physical,

            v$sysstat direct,

            v$sysstat lobs,

            v$sysstat logical

            where physical.name='physical reads'

             and direct.name='physical reads direct'

             and lobs.name='physical reads direct (lob)'

             and logical.name='session logical reads';

 

Buffer hit

----------

97.0100575

 

Buffer Cache Performance Indicators:

SQL> select name,value

           from v$sysstat

          where name='free buffer inspected';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

free buffer inspected                                                     0

 

This statistics is the number of buffers skipped to find a free buffer. Buffers are skipped because they are dirty or pinned.

 

SQL> select event,total_waits

           from v$system_event

           where event in

           ('buffer busy waits');

EVENT                                                            TOTAL_WAITS

---------------------------------------------------------------- -----------

buffer busy waits                                                         24

 

buffer busy waits indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.

 

Tuning Steps:

  1. Make it bigger
  2. Use multiple buffer pool
  3. cache table in  memory
  4. bypass the buffer cache
  5. use index appropriately
  6. Improve free buffer waits


1. Make it Biger:

SQL> show parameter db_cache_size;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 20971520

 

SQL> show parameter sga_max_size;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 135338868

 

SQL> alter system set sga_max_size=160M scope=spfile;

System altered.

 

SQL> startup force;

ORACLE instance started.

Total System Global Area  168893060 bytes

Fixed Size                   453252 bytes

Variable Size             134217728 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

 

SQL> alter system set db_cache_size=40M;

System altered.

 

SQL> show parameter db_cache_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 41943040

 

You can take advice before increasing your db_cache_size.

Db_cache_advice values:

  1. Off(Default)
  2. Ready(First time for allocating memory)
  3. On

 

SQL> show parameter db_cache_advice;

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------------------

db_cache_advice                      string      OFF

 

SQL> alter system set db_cache_advice=ready;

System altered.

 

SQL> alter system set db_cache_advice=on;

System altered.

 

SQL> select name,SIZE_FOR_ESTIMATE,ESTD_PHYSICAL_READS

            from v$db_cache_advice

            where block_size=8192;

 

NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS

-------------------- ----------------- -----------------------------------------------------

DEFAULT                              8                   0

DEFAULT                             16                   0

DEFAULT                             24                   0

DEFAULT                             32                   0

DEFAULT                             40                   0

DEFAULT                             48                   0

DEFAULT                             56                   0

DEFAULT                             64                   0

DEFAULT                             72                   0

DEFAULT                             80                   0

DEFAULT                             88                   0

DEFAULT                             96                   0

DEFAULT                            104                   0

DEFAULT                            112                   0

DEFAULT                            120                   0

DEFAULT                            128                   0

DEFAULT                            136                   0

DEFAULT                            144                   0

DEFAULT                            152                   0

DEFAULT                            160                   0

20 rows selected.

 

2. USE MULTIPLE BUFFER POOL:

SQL> alter system set db_keep_cache_size=2M;

System altered.

 

SQL> alter system set db_recycle_cache_size=2M;

System altered.

 

SQL> alter table scott.dept

            storage(buffer_pool keep);

Table altered.

 

SQL> create table d(id number) storage (buffer_pool keep);

Table created.

 

SQL> create table dd(id number) storage (buffer_pool recycle);

Table created.

 

SQL> select owner,segment_type,segment_name,buffer_pool

            from dba_segments

            where buffer_pool='KEEP';

 

OWNER               SEGMENT_TYPE           SEGMENT_NAME            BUFFER_POOL

------------------------------------------------------------------------------------------------------------

SCOTT                          TABLE                             DEPT                                    KEEP

SYS                               TABLE                                    D                                     KEEP

 

SQL> select owner,segment_type,segment_name,buffer_pool

            from dba_segments

            where buffer_pool='RECYCLE';

 

OWNER                SEGMENT_TYPE         SEGMENT_NAME          BUFFER_POOL

------------------------------------------------------------------------------------------------------------

SYS                            TABLE                                  DD                               RECYCLE

 

Calculating the Hit Ratio for multiple Pools

 

SQL> select name,1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"

FROM v$buffer_pool_statistics

WHERE db_block_gets+consistent_gets>0;

 

NAME                  HIT_RATIO

-------------------- ----------

DEFAULT              .988217737

RECYCLE              .503866235

KEEP                       .983520845

 

3 Cache Table in Memory

SQL> alter table scott.emp cache;

Table altered.

 

SQL> create table xyz(ID number) cache;

Table created.

 

If only FTS occur normally this table result will store LRU but after executing this query always this table result store in LRU.

 

SQL> alter table scott.emp nocache;

Table altered.

 

After this query only if FTS occurred it will store in LRU.

 

4.Bypass in Buffer Cache:

C:\>exp scott/tiger tables=emp file=d:\test.dmp DIRECT=Y

 

5. use index appropriately

If where clause use then use there index.

 

6. Improve free buffer waits

Check that the files are equally distributed across all devices. If that produces no effect get faster disks or place offending files onto faster disk.

Comments