Embedded Files
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.
Page updated
Google Sites
Report abuse