| Lesson 10 | Use Oracle table caching |
| Objective | Issue the cache directive on a table. |
Issue Cache Directive on Table
As you know from prior lessons, Oracle does not use the MRU end of the data buffer cache when performing full-table scans.
The MRU end of the data buffer is reserved only for table blocks that have been accessed via an index.If Oracle were to allow full-table scan blocks in the MRU end of the data buffer, other data blocks would be quickly aged-out of the buffer, causing a dismal data buffer hit ratio. Hence, Oracle reserved data buffer space at the LRU and of the data buffer exclusively for blocks that are being accessed by full-table scans. This prevents full table scans of large tables from flushing the entire cache.
The MRU end of the data buffer is reserved only for table blocks that have been accessed via an index.If Oracle were to allow full-table scan blocks in the MRU end of the data buffer, other data blocks would be quickly aged-out of the buffer, causing a dismal data buffer hit ratio. Hence, Oracle reserved data buffer space at the LRU and of the data buffer exclusively for blocks that are being accessed by full-table scans. This prevents full table scans of large tables from flushing the entire cache.
Frequently accessed Tables
We know that an FTS is fastest for tiny reference tables such as lookup tables, but how can we keep the blocks from these tables in the buffer cache?
Oracle's table cache option directs Oracle to use the MRU end of the data buffer when the table is accessed via a full-table scan, bypassing the default behavior of using the LRU end of the buffer for full table scans. The table cache option serves the same function as assigning a table to the KEEP pool in Oracle. The rules for identifying table for table caching is the same as for the KEEP pool. You want to cache tables that are small and frequently referenced.
The SlideShow below illustrates the process.
Oracle's table cache option directs Oracle to use the MRU end of the data buffer when the table is accessed via a full-table scan, bypassing the default behavior of using the LRU end of the buffer for full table scans. The table cache option serves the same function as assigning a table to the KEEP pool in Oracle. The rules for identifying table for table caching is the same as for the KEEP pool. You want to cache tables that are small and frequently referenced.
The SlideShow below illustrates the process.
Oracle Table Cache Directive
2) Other incoming data blocks will enter the MRU after the customer blocks
Oracle Table Cache Directive
The syntax for the table cache option is:
The syntax for the table cache option is:
ALTER TABLE tablename CACHE.
Using Cache Hints
In addition, you can direct the Oracle SQL optimizer to place data blocks into the MRU end of the data buffer with a cache hint. However, this hint will do nothing if the query is not performing a full table scan since data blocks would go into the MRU end of the buffer cache anyway. We will learn about SQL hints in a later course, but the "cache" hint has the same result as the
command, (where table1 is the table that you want to alter) except that only the rows that are returned from the query will be pinned at the MRU end of the cache.
ALTER TABLE table1 CACHE;
command, (where table1 is the table that you want to alter) except that only the rows that are returned from the query will be pinned at the MRU end of the cache.
Select /*+ cache */ State_name, State_abbr From State_codes;
Just like assigning the table to the KEEP pool, this statement serves to keep the table rows in the buffer cache longer.
However, the method for accomplishing this task is very different from the KEEP pool. The table cache option specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. The KEEP pool assignment directs all of the data blocks to another isolated pool; an entirely different method for accomplishing the same result. The next lesson concludes this module.
However, the method for accomplishing this task is very different from the KEEP pool. The table cache option specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. The KEEP pool assignment directs all of the data blocks to another isolated pool; an entirely different method for accomplishing the same result. The next lesson concludes this module.
