Oracle Corner

KEEP and RECYCLE caches

• KEEP cache is used for so called "hot objects": accessed very often, usually small in size and should be kept in memory at all times. Lookup tables are good candidates.
  
• RECYLE cache is used for objects that should be removed from the cache as soon as soon as SQL statement finishes. Criteria might be:
• Taking at least 5% of the cache, with tch<=1
• Very large table that is accessed randomly
• Any table which is accessed very rarely
  

X$BH
This view is a snapshot of buffer header, including objects' buffers, their position on the LRU list and number of touches.

• LRU flag tell you where the buffer is.
  
• 0 (zero) means on the cold end of the list (LRU)
  
• 8 means the hot (MRU) end
  
• 4 means removed from the hot end
  
• 2 means intentionally placed on the cold end
  
• full table scan or NOCACHE, for example
  
• TCH in X$BH view tells us how many times was that data block touched by a SQL query. This is pretty usefull in determining which objects are hot and which should be recycled.
  

The following SQL statement below tells you how many buffers an object takes and what is the average touch for that object. Sort it by "Average touches" to find the HOT blocks and sort it by "Object buffers" to see which objects are candidates for the RECYCLE cache. For HOT blocks, also check objects with lru_flag=8.

COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(1) number_of_blocks_per_object
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_name
ORDER BY count(1);

OR

PROMPT Number of buffers per object and per average touch
SELECT d_o.object_name as object_name,
sum(o.buffers) as obj_buffers, avg_touches
FROM (SELECT obj object, count(1) buffers, avg(tch) avg_touches
FROM x$bh
WHERE lru_flag <= 8 GROUP BY obj having avg(tch) >= 0) o, dba_objects d_o
WHERE o.object = d_o.data_object_id
GROUP BY d_o.object_name, avg_touches;