Parsing - Troubleshooting Oracle Performance - page 426

The following sections explain how to work around such problems in order to achieve results similar to those
reached by carrying out the right implementation. Even if the performance of such workarounds isn't as good as that
possible with a correct implementation, in some situations the workaround is much better than doing nothing at all.

■ the following sections describe the impact of parsing by showing the results of different performance tests.
the performance figures are intended only to help compare different kinds of processing and to give you a feel for their
impact. remember, every system and every application has its own characteristics. therefore, the relevance of using
each technique might be very different depending on where it's applied.

Note

Cursor Sharing
This feature is designed to work around performance problems caused by applications that improperly use literals
instead of bind variables, which in turn leads to too many hard parses. Earlier in this chapter, I pointed this problem
out in test case 1.
The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if
cursor sharing is enabled, the database engine automatically replaces the literals with bind variables. Thanks to these
replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.
Basically, the goal is to have an application behaving like test case 2, even if it's written like test case 1.

■ Cursor sharing doesn't replace literal values contained in static sQL statements executed through pL/sQL. For
dynamic sQL statements, the replacement takes place only when literals aren't mixed with bind variables. this isn't a
bug; it's a design decision. You can use the cursor_sharing_mix.sql script to reproduce this behavior.

Note

Cursor sharing is controlled through the cursor_sharing initialization parameter. If it's set to exact , the feature
is disabled. In other words, SQL statements share the same parent cursor only if their text is identical. If cursor_
sharing is set to force or similar , the feature is enabled. The default value is exact . You can change it at the system
and session levels. It's also possible to explicitly disable cursor sharing at the SQL statement level by specifying the
cursor_sharing_exact hint.
Oracle Support note 1169017.1 ( Deprecating the cursor_sharing = 'SIMILAR' setting ) declares that, from
version 11.1 onward, setting the cursor_sharing initialization parameter to similar is deprecated. In addition, as of
version 11.2.0.3, when the parameter is set to similar , the database engine behaves as if it were set to force ! There
are two main reasons for deprecating the value similar . First, as you will read shortly, there are problems in its
implementation. Second, the introduction of adaptive cursor sharing (refer to Chapter 2 for information about this
feature) makes similar no longer necessary. In fact, adaptive cursor sharing can work with cursor sharing set to force .

■ Cursor sharing has a reputation for not being very stable. this is because, over the years, plenty of bugs
related to it have been found and fixed. therefore, if you are considering using it, my advice is to carefully review Oracle
support note 94036.1 ( Init.ora Parameter “CURSOR_SHARING” Reference Note ), specifically the list of known bugs.

Caution

Since cursor sharing can be enabled with two values, force and similar , let's discuss the differences
between them. For that purpose, test case 1 was executed against a 10.2.0.5 database once for each value of the