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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.