ORACLE PARAMETERS TO IMPROVE CURSOR PROCESSING PERFORMANCE

For applications where literals are being used instead of bind variables, leading to unnecessary hard parsing, the parameter cursor_sharing can be used.  The performance of such applications is primarily influenced by the hard parse necessary.  By default, Oracle will behave doing a hard parse for each new SQL statement.  If, however, these SQL statements could be shared had the literal(s) been replaced by a bind variable, setting the parameter to one of the non-default values will change this behavior.  Doing this will bring
some of the benefit of coding applications with bind variables instead of literals.

The cursor_sharing parameter can be set to one of three values:

• exact  SQL statements are only shared if they are exactly identical. This is the default

• force  If SQL statements only differ in literal values, they will be shared as if the literals had been bind variables.  This will be done unconditionally.

• similar  Causes cursor sharing to take place when this is known not to have any impact on optimization.

The parameter can be set at the system level using the alter system command or at the session level using the alter session command.

It is highly recommended not to write applications dependent on this parameter, and to use the parameter only when needed for existing applications incorrectly using literals. 

Applications identified by repeated (soft) parse of identical SQL statements.  On the Oracle server side, this has the implication that the same values repeatedly are assigned to the server side information about cursors.  This behavior can be modified by allowing the server to keep information available for frequently parsed SQL statement, at the expense of the need to lookup such SQL statements. 
The parameter session_cached_cursors can be used to do exactly that:  If set to an integer value, the Oracle server engine will attempt to keep that many cursors in each session parsed and ready for execution.  Appropriate values for the session_cached_cursors parameter depend on the Oracle release. 
In Oracle9i Release 2 and later, values as high as several hundreds can be used, in earlier releases, values above 10 to 20 are not likely to be useful as more CPU usage has a tendency to negate the effect of increased scalability using the parameter.