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.
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.