Cursor reuse in PL/SQL static SQL

Background

In order that a SQL satement can be executed, it has to be parsed (checked for syntactic and semantic correctness) and the execution plan has to be calculated. All this costs computational resources.

To save on these costs, the Oracle instance maintains a system-wide LRU cache (aka the shared cursor cache) - exposed via v$sqlarea - of previously encountered SQL statements and appropriate derived information so that when the next SQL statement is submitted for parsing it is checked for match against the cached ones. (The definition of the criterea for a match is beyond the scope of this tutorial. Roughly speaking, the current statement must be both textually identical to its match candidate, famously to the extent of whitespace and upper/lower case identity, and the types of the bind variables must match.) When the current statement is matched, the stored derived information (parse tree, execution plan, etc) is reused and computational cost is saved.

Moreover, the above processing has to be done in the context of a cursor which itself has to be opened and associated with the SQL statement in question, again at some cost.

A given session might have one or several concurrently open cursors. Information on these, including the foreign key reference to v$sqlarea is exposed via v$open_cursor.

Advanced programmers using the "difficult" interfaces to cursor manipulation - eg OCI in a C programming environment or the Dbms_Sql package in a PL/SQL programming environment - typically code explicit, relatively elaborate approaches to minimize cursor costs. This cost-saving paradigm is described below.

Programmers whose requirements can be satisfied by static SQL in a PL/SQL programming environment enjoy the benefits of an implicit implementation of this cost-saving paradigm while writing simple, easy-to-maintain, code.

However, it is possible to subvert these implicit benefits by careless programing. This tutorial presents the conceptual background for understanding this and some guidelines for avoiding such mistakes.

• The explicitly programmed cost-saving paradigm
• Observing v$open_cursor
• Benefiting from the cost-saving paradigm implicitly
  by using PL/SQL static SQL
• Demonstrating the LRU behavior of the PL/SQL cursor cache
• How careless programing can subvert the benefit
  of the PL/SQL cursor cache
• How to diagnose and avoid subverting the benefit
  of the PL/SQL cursor cache
• PL/SQL REF CURSORs don't (up to Oracle9i) implement
  the cost-saving paradigm
• REF CURSORs: the bigger picture
• PL/SQL native dynamic SQL does not
  implement the cost-saving paradigm

Conclusion

• Using Dbms_Sql gives you the ultimately fine-grained level of control over cursors and thus it allows you to implement an approach which makes maximal possible reuse of the resources used to set up a cursor and to parse the SQL statement, even when this is a soft-parse (ie the statement is found in the v$sqlarea cache).

  Moreover using Dbms_Sql develops your mental model in this critical area of functionality, so it's good at least to study it and practice with it a little.

  There are some use cases (for example when you don't know the number of bind variables until runtime) which are not supported using native dynamic SQL, and in such cases Dbms_Sql is the only viable approach.

  Thus Oracle recommends that you use native dynamic SQL when its syntax supports your requirements, and that you use Dbms_Sql only when your functional requirements cannot be satisfied by native dynamic SQL. The theoretical performance advantage of Dbms_Sql over native dynamic SQL is in fact balanced by the performance advantages of the latter's tighter integration into the PL/SQL language, so you should not need to choose Dbms_Sql for performance reasons when other approaches are viable.
   

• Static SQL constructs in PL/SQL, using both explicit and implicit cursors, give you the benefits of the cost-saving paradigm without the effort of programming it.

  This is achieved because the PL/SQL runtime system does not actually close your cursor (in the sense of the actions impemented by Dbms_Sql.Close_Cursor, aka a hard-close) when the PL/SQL close statement is executed, or when an implicit cursor statement completes. Rather, it just soft-closes the cursor you think you've closed, ie it marks it as a candidate for later hard-close in the PL/SQL cursor cache, a LRU cache of potentially re-usable open cursors exposed via v$open_cursor.
   

• It is possible to subvert this by careless programming, but it's easy to diagnose and correct such errors by monitoring v$open_cursor.
   
• REF CURSORs used with static SQL do not currently (up to Oracle9i) give you the benefit of the cost-saving paradigm. The compiler does have information, and this may be improved in a later release.
   
• Native dynamic SQL, which often implies the use of a REF CURSOR opened with a dynamic string, does not implement the cost-saving paradigm since it's current exposure in PL/SQL (up to Oracle9i) does not give language constructs to distinguish between opening and parsing on the one hand and binding, executing and fetching on the other. However (as sated above) this penalty is offset by the greater efficiency due to it's tighter integration.
   
• There is no reason ever to omit the close statement to balance the open for any of the above flavors of cursor construct. If you do omit it, you've programmed a potential memory leak: this is simply bad code! Nevertheless, the PL/SQL runtime system generally rescues you from such mistakes. However, you should not rely on this.

Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 4-Jun-2003