Cursor Sharing in Scheduled Processes: 1. Introduction

This is the first in a series of 4 posts about the selective use of cursor sharing in scheduled processes in PeopleSoft.

1. Introduction
2. What happens during SQL Parse?  What is a 'hard' parse?  What is a 'soft' parse?  The additional overhead of a hard parse.
3. How to set CURSOR_SHARING for specific scheduled processes.
4. How to identify candidate processes for cursor sharing.

PeopleSoft applications make extensive use of configuration data that is then interpreted by dynamic code. This leads to lots of similar statements with different literal values; statements that differ only by literal values are still different. In an Oracle database, each statement that cannot be exactly matched with a statement already in the Shared SQL Area (also known as the Cursor Cache) must be parsed.  Thus, each distinct statement must be parsed at least once.  

Hence, PeopleSoft systems have many SQL statements that are only ever executed once,each of which has to be fully parsed.  This is often called a 'hard' parse.  With PeopleSoft, this parse overhead can have a significant impact on performance.

In an Oracle database, CURSOR_SHARING can be set to FORCE (the default is EXACT). Then, it will substitute the literals with system-generated bind variables. The statement is still exactly matched against the contents of the Shared SQL Area, but now statements that previously only differed by their literal values will match the same bind variables.  Oracle can omit some of the parse processing for matched statements, leaving what is often called a 'soft' parse. This can significantly reduce the CPU overhead of SQL parse, and thus improve the performance of some processes.

Do not set CURSOR_SHARING to FORCE at database level.  Over the years, I have tried this several times with different PeopleSoft systems, and on various different versions of Oracle.  The net result was always negative. Some things improved, but many more degraded and often to a greater extent.

However, I have found that it can be effective to set it at session-level for specific processes, and sometimes just specific run controls for specific processes.  

What is Oracle's advice?

Oracle has always been clear that it is always better to write sharable SQL (i.e. that uses bind variables), and that setting CURSOR_SHARING = FORCE should not be used as a permanent fix.

As a general guideline, the Oracle Real-World Performance group recommends against setting CURSOR_SHARING to FORCE except … when all of the following conditions are met:

1. Statements in the shared pool differ only in the values of literals 
2. Response time is suboptimal because of a very high number of library cache misses.
3. Your existing code has a serious security and scalability bug—the absence of bind variables—and you need a temporary band-aid until the source code can be fixed.
4. You set this initialization parameter at the session level and not at the instance level.

See SQL Tuning Guide: Improving Real-World Performance Through Cursor Sharing -> Real-World Performance Guidelines for Cursor Sharing -> Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix

I submit that my approach in PeopleSoft meets at least 3 these of conditions.  As for the third criterion, we are talking about SQL statements that are either delivered as part of PeopleSoft code or that are dynamically generated from configuration data, also by delivered code.  The reality is that these are as 'fixed' as they are going to be within PeopleSoft.  Most of this code is at least very difficult, if not impossible, for the customer to alter, and the cost and risk of owning and maintaining any such customisation is almost certainly prohibitive.  Cursor sharing may be a band-aid, but it will be there for the long term.

The next article looks at how the Oracle database parses SQL statements.