CACHE and NOCACHE Hints

Introduction

In this article, we explore the CACHE hint as part of our series on Oracle hints. The CACHE hint instructs Oracle to place the retrieved data blocks of a table or index in the most recently used (MRU) end of the buffer cache’s Least Recently Used (LRU) list, increasing the likelihood that these blocks remain in memory for subsequent accesses. While this hint can improve performance for frequently accessed data, it should be used carefully as overusing the CACHE hint can flood the buffer cache with unnecessary data blocks and in the process evict other more critical data.  The result being a slow down in database performance. Unlike more targeted hints like INDEX or USE_NL, which affect specific optimizer pathways, the CACHE hint influences memory management and can have broader implications.  As in previous hints, use with caution and test, test, test.

Before we look at an example of the CACHE hint, lets just review how Oracle manages the LRU for the buffer cache.

 How Oracle Manages the Buffer Cache LRU 

Oracle manages the Buffer Cache using a Least Recently Used (LRU) mechanism to ensure frequently accessed data blocks remain in memory for as long as possible. 

Key Points

• The Buffer Cache stores data blocks read from disk, such as table data, indexes, and undo blocks. It is organized into one or more subpools, each managed by a single LRU list. Within each list, buffers are prioritized based on access frequency, with frequently accessed blocks considered “hot” and less frequently accessed blocks considered “cold”

• Each subpool’s LRU list tracks buffer block access. Buffers with frequent, or recent access, are prioritized to stay in the cache. Buffers with less recent, or infrequent access, are candidates for replacement.

• Each buffer has a touch count to track usage frequency. When a block is accessed, its touch count increments, and it moves toward the hot end of the LRU list. Blocks with low touch counts drift toward the cold end.

• To prevent short lived or one time access blocks from flooding the hot end, Oracle uses a midpoint insertion strategy. Newly read blocks are placed near the middle of the LRU list, not directly at the hot end, or cold end, allowing frequently accessed blocks to earn their place.  See important notes below.

• When the cache needs space for new blocks, Oracle evicts blocks from the cold end of the LRU list. These are the least recently or least frequently used blocks.

• Oracle ensures dirty buffers are managed to avoid performance bottlenecks, tracking them separately from the LRU list for efficient writing to datafiles during checkpoints.

Important Notes

Most read operations use midpoint insertion to place blocks near the middle of the LRU list, avoiding eviction of frequently accessed blocks. However, large or temporary reads often insert blocks at the cold end (or bypass the cache) to protect hot data.  Here is a list of operations that get read into the "cold" end of the LRU.

• Parallel Query Scans - If parallel queries use the buffer cache (i.e., not direct path reads), blocks are usually inserted at the cold end since they’re short-lived. If direct path reads are used (common for large scans), the buffer cache is bypassed entirely.

• Index Fast Full Scans - Large index fast full scans behave like full table scans, placing blocks at the cold end to avoid polluting the cache. Smaller indexes may still use midpoint insertion depending on the _small_table_threshold.

• LOB (Large Object) Reads - When LOBs are read using the buffer cache, blocks may be treated as temporary and placed at the cold end, especially for large CLOB/BLOBs. LOBs with NOCACHE bypass the cache or are marked for quick eviction.

• Large Partition Table Scans - Scanning large partitions is treated like a full table scan and blocks go to the cold end, particularly when the partition exceeds _small_table_threshold. Smaller partitions may behave like normal small table scans.

• Maintenance Operations (e.g., RMAN, Data Pump) - These operations often read large amounts of data sequentially, inserting blocks at the cold end or using direct path reads to bypass the cache. The goal is to avoid disrupting the working set of hot buffers.

Using the Cache Hint Example

SELECT /*+ CACHE */ * 

  FROM dept

/

Example Plan

Plan hash value: 1234567890

--------------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time          |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    10 |   200 |     2   (0)| 00:00:01      |

|   1 |  TABLE ACCESS FULL | DEPT |    10 |   200 |     2   (0)| 00:00:01      |

--------------------------------------------------------------------------------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

   - table cache hint used

In this example, the query produces an FTS, which would normally result in the bufefrs being placed at the cold end of the LRU.  The CACHE hint instructs Oracle to place the buffers closer to the top of the MRU of the LRU list.

Considerations

Instead of using the CACHE hint for a given query,  you could consider setting the table’s storage attribute to CACHE or use a buffer pool setting to control how its blocks are cached. For example

ALTER TABLE dept STORAGE (BUFFER_POOL KEEP);

or

ALTER TABLE dept CACHE;

The CACHE setting places the table’s blocks nearer to the hot end of the LRU list during full table scans (FTS), increasing the likelihood they stay in the buffer cache.
The BUFFER_POOL KEEP setting places the table’s blocks into the KEEP buffer pool, which is a separate area of the buffer cache meant to retain frequently accessed objects.

This permanently marks the table for caching, making it effective for all queries. However, the CACHE hint is more flexible as it can be applied to specific queries and is not limited to FTS behavior.

NOCACHE Hint

The NOCACHE hint places blocks at the cold end of the Buffer Cache’s LRU list, making them candidates for quick eviction. While blocks are still read into the cache, they are unlikely to remain in memory for subsequent queries, unlike with the CACHE hint, which keeps them in memory longer. 

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 30th April 2025