Pinning and caching
What is the difference between pinning and caching in Oracle?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top