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)
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.
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
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.
We can use below query to verify the table testa use the db_keep_cache
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.
Set Data Buffer Cache
This is dynamic value .
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
If the pfile has SGA_TARGET or/and MEMORY_TARGET , even it set to 0, you will get ORA_00824 error.
create table space that use the 16K db_cache
Verify the table space
Write Data Cache to the disk
To move the dirty buffer to the disk
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 .
To check the current LRU count, use below query.
Data Buffer Cache hit ratio
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.
To enable DB_CACHE_ADVICE, we need to set DB_CACHE_ADVICE to on
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.
Display the multiple buffer pool status( Default buffer pool, keep buffer pool and recycle buffer pool)
Display the buffer pool over statistic.
Below query show the buffer pool hit ratio
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.
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.