Buffer States:
Three Buffer Pool:
Database Buffer Cache Parameter:
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 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:
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. |