Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

CHAPTER 13: Statement Caching > PL/SQL Cursor Cache

PL/SQL Cursor Cache

In the previous section, you saw how you can make a soft parse softer by using session cursor cache. Before 10g, the PL/SQL language cached only static cursors (excluding ref cursors), thus allowing you to avoid subsequent soft parses completely in such cases. Starting with 10g, PL/SQL also caches native dynamic SQL (which uses execute immediate syntax) in a loop, assuming that the exact same statement is being executed for each iteration in the loop (i.e., the statement is not changing for different iterations) and the compiler can determine this at compile time.

PL/SQL avoids soft parses by performing a soft close on the cursor. In other words, when you close a cursor explicitly (e.g., by using the close command) or implicitly (e.g., when the cursor goes out of scope in the for loop in which it was defined), PL/SQL does not actually close the cursor—it caches it in its own separate cursor cache, hoping that your application will reuse the cursor in its next attempt. Of course, if the maximum limit for opened cursors is about to be reached, then it closes the cursor for real. The beauty of this feature is that for the class of cursors for which it is applicable, you can avoid soft parses completely!


  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint