CACHE & NOCACHE Hint

When we come to usage of the WSO2 products all product include  db scripts to both oracle.sql and oracle_rac.sql db script. In this two products oracle.sql include NOCACHE and oracle_rac.sql include CACHE.I intend to writing blog post about CACHE and NOCACHE hint.Furtherthis will be useful when we interacting WSO2 product with Oracle.


CACHE & NOCACHE Hint

The CACHE hint instructs the optimizer to place the blocks retrieved for the table at the most recently used (MRU) end of the least recently used (LRU) list in the
buffer cache when a full table scan is performed.
The buffer cache holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk.Blocks within the buffer cache are ordered from MRU (most recently used) blocks to LRU (least recently used) blocks. Whenever a block is accessed, the block goes to the MRU end of the list.This hint is useful
for small lookup tables.

In the following example, the CACHE hint overrides the default caching
specification of the table:
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
 FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name
 FROM employees hr_emp;


The different between CACHE/NOCACHE hint is Cache blocks from Full Table Scan & Nocache do not cache blocks from a Full Table Scan.When we comes to Oracle RAC enviornment do need to cache blocks from a full table scan because it has two clustering nodes that needs to Cache blocks.The CACHE and NOCACHE hints affect system statistics table scans (long tables) and table scans (short tables), as shown in the V$SYSSTAT data dictionary view.


Above are collection of things that i gathered relevant to this subject