Sunday, October 21, 2012

0 Data Buffer Cache

Oracle instance use the Data Buffer Cache to store the data. When the query first run, the process look for the data in the memory (SGA-> Data Buffer Cache). If the data is found, then it use it. It is called logical read.  If the data is not found , it looks for the disk. It is called Hard read.

The buffer has 3 status

  • Dirty Buffer: The data has been modified but not yet reflect on the disk
  • Free( Clean) Buffer: The data s in sync with the file hence the instance mark the memory as FREE and can be overwritten when necessary.
  • Pinned Buffer: The data buffer is current being used by the process.

The data buffer use below 2 lists to control the status

  • Dirty List: store the dirty buffer list
  • LRU List: Store the Dirty buffer, Free Buffer and pinned buffer

Oracle use DBWRn to write the Dirty buffer into the disk.

LRU ( Least Recent Use)

Oracle instance use the LRU algorism to determine what segments are most commonly used and what are not. This can help to prioritize what data need to be move to the disk and free up the space for the new data. over the years, oracle has improve the LRU algorism ( standard LRU –> modified LRU –>Touch-Count LRU)

Touch-Count LRU

Introduce from 8i. the list being split to the 2 area ( hot and cold). For the single point read, it place the data in the middle belong to the hot area ( middle point read). For multiple point read, it place to the LRU end. Each buffer has the touch count, whenever the buffer is being access, the count increase hence move toward the MRU( Most recent used). The touch count would only be increased once if the same buffer being access frequently in the short period of time.

  • The middle point can be adjust by the internal parameter _db_percent_hot_default
  • The time period can be adjust by the internal parameter _db_aging_touch_time 

Check Data Buffer Cache size

Each instance has default block size. The default block size can be found in the DB_BLOCK_SIZE. The DB_BLOCK_SIZE is decided when the database is created. It can not be changed afterward.

image_thumb13

Other then the default block, oracle allow to create multiple nk cache size. It is used by the nk table space. For example if the 16K cache size is used by the 16K table space.

  • db_nk_cache_size: oracle support 2k/4k/8k/16k/32k block size
  • db_cache_size: the default data cache’s size. In this example, the db_block_size is 8K, the db_cache_size is the 8K data cache size. sine the db_block_size is 8K then we can not set the db_8k_cache_size
show parameter _CAHCE_SIZE

image_thumb7

image_thumb[1]

Multiple Buffer Pool

From Oracle 8, Oracle introduce the multiple buffer pool.

  • Default Pool: DB_CACHE_SIZE
  • Keep Pool: DB_KEEP_CACHE_SIZE:  use to store the common data segment
  • Recycle Pool: DB_RECYCLE_CACHE_SISE: use to store the rare use data segment

Each Pool has their own LRU list. Application can decide what buffer they want to use. If no buffer pool is specify, it use the Default pool ( DB_CACHE_SIZE). The reason for the multiple buffer pool is that if you have the full table scan, it require read a lot of data segment to the data cache. when this happen, it may potential move other commonly use data to the disk hence cause the later performance penalty.and usually the full table scan’s data segment only being used once. With the multiple buffer pool, we can put those type of data to other buffer pool.

Keep Pool and recycle Pool all use the default DB_BLOCK_SIZE. So if the DB_BLOCK_SIZE is 8K, then the Keep Pool and recycle Pool are 8K. The other nK_CACHE_SIZE does not have keep pool and recycle pool.

alter system set db_keep_cache_size=10M;

image_thumb[5]

CREATE TABLE TESTA ( A NUMBER ) STORAGE ( BUFFER_POOL KEEP);

We can use below query to verify the table testa use the db_keep_cache

select table_name,buffer_pool from DBA_TABLES where TABLE_NAME='TESTA';

image_thumb[7]

Other examples:

CREATE TABLE TESTA ( A NUMBER ) STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TESTA STORAGE ( BUFFER_POOL RECYCLE);
ALTER INDEX testIndex STORAGE ( BUFFER_POOL KEEP);

DB_FLASH_CACHE_SIZE

If the host has SSD install, then we can create the DB_FLASH_CACHE. DB_FLASH_CACHE_FILE is the location of the file. DB_FLASH_CACHE_SIZE is the size of the file.  When the data block is evicted from SGA, it write to the disk and also keep the copy in the flash cache. When the block  is needed again, the process look for the SGA Data cache first , if it can not find it, it look for FLASH_CACHE, if it can not find in the FLASH_CACHE, it will look it in the disk.

image_thumb11

Set Data Buffer Cache

set DB_CACHE_SIZE

This is dynamic value .

alter system set db_cache_size=80M;

Set DB_BLOCK_BUFFERS and DB_BLOCK_SIZE

  • Can not be set with DB_CACHE_SIZE
  • only can be set in the pfile and pfile can not have SGA_TARGET and MEMORY_TARGET  parameter.
  • The DB_BLOCK_SIZE is  in bytes
  • Total db buffer cache is DB_BLOCK_BUFFERS * DB_BLOCK_SIZE

image_thumb5

If the pfile has SGA_TARGET or/and MEMORY_TARGET , even it set to 0, you will get ORA_00824 error.

image_thumb3

set DB_nK_CACHE_SIZE

alter system set db_16k_cache_size=50M;

image_thumb15

create table space that use the 16K db_cache

create tablespace test1 logging
datafile '/home/oracle/app/oracle/oradata/orcl/test1.dbf' size 10M
blocksize 16384
extent management local
segment space management auto;

Verify the table space

SELECT
   TABLESPACE_NAME,
   BLOCK_SIZE,
   CONTENTS,
   EXTENT_MANAGEMENT,
   ALLOCATION_TYPE,
   SEGMENT_SPACE_MANAGEMENT
FROM
   DBA_TABLESPACES;

image_thumb[3]

Write Data Cache to the disk

To move the dirty buffer to the disk

alter system flush buffer_cache;
image_thumb9

 

_DB_BLOCK_LRU_LATCHES

Oracle process needs to obtain the latch before it can scan the LRU list to find out the free space.

Below query can show the LRU hit .

SELECT NAME, ROUND(( 100 - ( SLEEPS/GETS) * 100),3)|| '%' "LRU HIST%" FROM V$LATCH
WHERE NAME = 'cache buffers lru chain';

image_thumb[11]

To check the current LRU count, use below query.

select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode( bitand(y.ksppstvf,7),1, 'MODIFED',4, 'SYSTEM_MOD','FALSE') ismod, 
decode( bitand(y.ksppstvf,2),2, 'TRUE','FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id= userenv('Instance')
and y.inst_id=  userenv('Instance')
and x.indx= y.indx
and x.ksppinm = '_db_block_lru_latches';

image_thumb[13]

Data Buffer Cache hit ratio

SELECT 1- (P3.value/(P1.value+P2.value)) "cache hit rathio"
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
     WHERE  P1.name = 'db block gets'
     AND    P2.name = 'consistent gets'
     AND    P3.name = 'physical reads' ;

 

image_thumb[15]

DB_WRITE_PROCESSES

DB_WRITER_PROCESSES decide the number of DBWRn. Max number is 10. Recommend not to exceed the CPU count on the host. It is the static value.

show parameter db_writer_processes

image_thumb[9]

 

DB_CACHE_ADVICE

To enable DB_CACHE_ADVICE, we need to set DB_CACHE_ADVICE to on

image

There are three option for db_cache_advice, on, off and ready. when set it as ready, the db_cache_advice would only effective after the instance bounce.

select name,block_size,advice_status,size_for_estimate,estd_physical_read_factor,estd_cluster_reads from v$db_cache_advice;

image

V$BUFFER_POOL

Display the multiple buffer pool status( Default buffer pool, keep buffer pool and recycle buffer pool)

select * from v$buffer_pool;

image_thumb[18]

V$BUFFER_POOL_STATISTICS

Display the buffer pool over statistic.

image_thumb[20]

Below query show the buffer pool hit ratio

SELECT name, block_size,1 - ( physical_reads/ (db_block_gets + consistent_gets)) "Hist ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE db_block_gets + consistent_gets > 0;

 image

The hit ratio higher means the data in the cache is being reused more often. However, if the recycle hit ratio is higher, that means you may want to move the data from recycle to the keep.

V$BH

This view is usually used to check the SGA in the RAC. RAC use to check the object consistence between instance. hence it also contents the  Data buffer cache usage.

select owner,object_name,count(*) buffers, (count(1)/(select count(*) from v$bh))* 100 "% of Data Buffer" 
from dba_objects o, v$bh bh
where o.object_id = bh.objd
and o.owner not in ('SYS','SYSTEM')
group by owner , object_name
order by count(1) desc;

 image

 

Reference

http://www.pafumi.net/multi_buffers.htm

Flash Cache size: http://www.oracle.com/technetwork/articles/systems-hardware-architecture/oracle-db-smart-flash-cache-175588.pdf

http://www.dba-oracle.com/art_dbazine_9i_multiblock.htm

http://www.adp-gmbh.ch/ora/concepts/cache.html

http://www.wisdomforce.com/resources/docs/internals_latches.pdf

http://www.dbspecialists.com/files/presentations/buffercache.html

IceRocket Tags:

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates