Problem solve Get help with specific problems with your technologies, process and projects.

Pinning and caching

What is the difference between pinning and caching in Oracle?

Caching is when you specify CACHE as part of a CREATE or ALTER for an object to tell Oracle that when blocks are retrieved for that object those blocks should be put at the most recently used end of the LRU (least recently used) list in the buffer cache when a full table scan is performed. Under normal circumstances, when blocks are retrieved into memory they are placed at the least recently used end of the LRU list. This means that the data which has been most recently accessed will typically stay available in the buffer cache for a while. Therefore, future executions of the same query should find those blocks already in the buffer cache and not have to retrieve them from disk again. When you use the CACHE clause, you say that you want to put the retrieved blocks at the end of the list which means that they will be the first blocks "aged" out of the buffer cache when more space is needed. A couple of reasons to use CACHE are: 1) for small lookup tables (only a few blocks that need to be read to retrieve the whole table) and 2) when you are doing a select that will return a huge amount of data and you don't necessarily expect the query to be executed again anytime soon. In either case, you give Oracle the opportunity to flush out those blocks quickly to make room for more "needed" blocks instead of having to track them in the LRU list.

Pinning is when you want to keep objects in memory and to prevent them from being aged out by the normal LRU mechanism. You use the DBMS_SHARED_POOL package to "pin" PL/SQL code into memory so that users don't experience intermittent slow downs when code gets aged out of the shared pool and has to be reloaded.

The bottom-line is the caching allows blocks to be moved out quickly while pinning keeps information loaded in memory to avoid re-loads.

For More Information

Dig Deeper on Oracle database design and architecture