Tuning Buffer Cache and DBWR process for performance
Understand the terminology involved in Buffer Cache
Buffer Cache
Oracle having copies of database blocks for frequent access instead of fetching from disks known as buffer cache in SGA area. It’s increase the performance of Oracle because reading the disk is costlier & time consuming than reading from memory.
Dirty Blocks
Blocks which have been updated in memory(buffer cache) but not yet flushed back/written to disk is dirty blocks.
DBWR or DBWn processes write the dirty blocks to the disk.
Buffer Hit Ratio
Buffer cache hit ratio calculated how many times a block was found in memory rather than having to execute a expensive read operation on disk to get the block. You must have more than 80% of buffer hit ration as good sign in Oracle Database performance tuning.
Following query will help to find the buffer hit ration:
SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) ) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0;
BUFFER CACHE ADVISORY
It’s the feature present from Oracle 9i to gather the stats of Buffer cache behavior and
provide you the recommendation about the changes in size of Buffer Cache will how much help to increase the hit ratio.
Enabling this feature increase overhead of CPU and memory.
Enable or disable the cache advice parameter value:
Use of the DB_CACHE_ADVICE parameter is enabled.
Parameter to ‘OFF’ (which is the default value), would turn off the advisory.
Parameter to ‘READY’ would turn off the advisory, but the memory allocated to advisory is retained.
Parameter to ‘ON’ would turn on the advisory.
-- For enable db cache advice
alter system set db_cache_advice=on;
--View of DB Cache advice:
column size_for_estimate format 999,999,999,999 heading 'Cache Size (m)'
column buffers_for_estimate format 999,999,999 heading 'Buffers'
column estd_physical_read_factor format 999.90 heading 'Estd Phys|Read Factor'
column estd_physical_reads format 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate
, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';
Tuning the Buffer Cache
Following are the possible solution in case of Buffer Cache:
1. Check the full table scan and avoid them because increase in value of DB_BLOCK_BUFFERS not help always.
Use index based query which will reduce the full table scan.
2. Avoid useless sorting of data, it also has effect DB Cache the sort area is reached its limit then it use disk sorting which data sent by the utilization of DB cache buffer to Disk for sorting.
SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE has impact on it.
3. Buffer cache size can be increased with help of using the information by db cache advisory.
4. You different type of buffer cache if you know that this table is small in size and can be cached/no-cache in memory:
Set the following parameter to keep the objects in it:
SQL> show parameter buffer_pool
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_keep string buffer_pool_recycle string
Note: For more detail regarding BUFFER POOL KEEP
5. Increase the DBWR or DBWn writer process.
Tuning Waiting events
Buffer busy wait:
Cause: when multiple session are trying to read the same block or waiting for a change to complete in the same block.
Resolve:
1. Reduce size of DB_BLOCK_SIZE.
2. Reduce no of rows in block by PCTUSED/PCTFREE.
3. Reduce parameter value of TRANSACTIONS_PER_ROLLBACK_SEGMENT for reducing the number of transaction per rollback segment.
Identify the object for Buffer Busy Waits:
—-Find the block number causing the buffer busy waits
SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS
FROM v$session_wait
WHERE event = 'buffer busy waits'
GROUP BY p1, p2, p3;
NUM_WAITERS FILE# BLK# CLASS ------------ ----- ------- ------ 92 2 13487 1016 73 2 27762 1016 32 1 29697 1016
--Note: 92 waiters for file 2 block 13487
--Find the segment name by using this query
SELECT owner,segment_name,segment_type
FROM dba_extents
WHERE file_id=&file
AND &blockid BETWEEN block_id AND block_id + blocks;