It is good practice to limit the number of times a cursor has to be parsed the optimal number of parses is one, of course. One option to achieve this ideal would be to preparse every possible cursor your application might ever execute. That way, every cursor would already be waiting in the shared pool when your application starts up. However, this approach would be extremely difficult to maintain in a large application or in one that allows ad hoc queries. Thus, it is better to take the hit the first time a cursor executes and then ensure that it is reused whenever possible later on.
Oracle uses a complex algorithm to decide whether a cursor that is about to be executed may reuse an already compiled version from the shared pool.
- One of the best ways to take advantage of automatically reformatting cursors to promote reuse is to put them into PL/SQL.The PL/SQL compiler reformats the mismatched cursors, thus producing a single cursor in the shared pool. On the surface, this may seem to be a very small improvement, but implemented across an application, it can produce big performance gains because the shared pool latches have fewer cursors to keep track of.
- PL/SQL can virtually eliminate all hard parses just by moving the cursors into a stored procedure.
- The PL/SQL compiler promotes reuse by being more forgiving regarding the structure of cursors.
- Once a cursor is compiled into a PL/SQL procedure, package, or function, it is automatically considered valid and parsed as long as the procedure, package, or function remains valid.
- PL/SQL compiler makes some extra effort to facilitate cursor reuse by checking for small differences like extra whitespace, uppercase versus lowercase, and line breaks.
- Another factor to consider when planning for cursor reuse is the use of literal values.
- A setting is available for the CURSOR_SHARING parameter : FORCE. And it does just that force cursors to be shared strictly on the basis of the text they contain after translating literals into bind variables. This method blindly performs literal substitution and forces the CBO to create an execution plan based on it. This is not always optimal, because the CBO might make a better decision if it knew the explicit value.
Note: The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1
- Oracle PL/SQL for DBA By Arup Nanda, Steven Feuerstein