Buffer Cache Tuning

Buffer States:

Pinned: Currently used.
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.
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.
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:

Keep
Recycle
Default

Database Buffer Cache Parameter:

DB_CACHE_SIZE (BLOCK SIZE will be the size declare in DB_BLOCK_SIZE)
DB_KEEP_CACHE_SIZE
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
DB_RACYCLE_CACHE_SIZE
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:

Make it bigger
Use multiple buffer pool
cache table in memory
bypass the buffer cache
use index appropriately
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:

Off(Default)
Ready(First time for allocating memory)
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