Tuning database buffer cache | Oracle DBA Tutorial pdf

Tuning database buffer cache

It caches most recently accessed data blocks into memory. If data is not found from cache, it is fetched from disk. It is operated on LRU mechanism (except, only for full table scan, it is placed on MRU end.)
Buffer may be free, pinned, clean or dirty.
DBWn writes dirty (changed) buffer to disk.

=> Measuring buffer cache hit ratio

SELECT 1-(physical.VALUE - direct.VALUE - lobs.VALUE)
/(logical.VALUE) "Buffer cache hit ratio"
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'
The ratio should be more than 0.90 for OLTP systems.

=>  Improving buffer cache hit ratio

=> Add more space
ALTER SYSTEM SET DB_CACHE_SIZE = 100M
Set DB_nK_CACHE_SIZE parameters in initialization file to access multiblock database.
To get an idea how much you should increase buffer cache size
=> Set DB_CACHE_ADVICE = ON initialization parameter.
=> Query V$DB_CACHE_ADVICE view.
=> Use multiple buffer pools
Use keep, recycle and default pool by setting DB_KEEP_CACHE_SIZE,
DB_RECYCLE_CACHE_SIZE and DB_CACHE_SIZE (default) initialization parameters.
Determining which segments to cache in keep and recycle pools, you must have in depth knowledge of the application.
SELECT username "owner", NAME "seg name", kind "seg type",
COUNT(DISTINCT BLOCK#) "No. buffers"
FROM v$cache v, dba_users d
WHERE v.owner# = d.user_id
GROUP BY NAME, username, kind
HAVING COUNT(DISTINCT BLOCK#)>10
ORDER BY 3 DESC
Oracle recommends you consider caching of segments in keep pool whose total size is less than 10% of default pool size.
To assign segments to pool, use
ALTER TABLE schema.package STORAGE (BUFFER_POOL KEEP);
ALTER TABLE schema.package STORAGE (BUFFER_POOL RECYCLE);
Monitor buffer pool statistics
SELECT NAME "Buffer pool",
1-(physical_reads/(db_block_gets+consistent_gets)) "Buffer pool hit ratio"
FROM v$buffer_pool_statistics
ORDER BY NAME
=>  Cache tables in memory
CREATE TABLE (…) CACHE
ALTER TABLE name CACHE
=> Use proper indexes
Build indexes on foreign key columns of tables that reference a primary key column in another table.