Database Reference
In-Depth Information
Summing Up
Utilizing prepared statements with bind variables is crucial in order to avoid unnecessary hard parses. However, when
using them, you can expect a small overhead in the client-side CPU utilization and network traffic. You could argue
that this overhead will lead to performance problems and, consequently, that prepared statements and bind variables
should be used only when really necessary. Since the overhead is almost always negligible, the best practice is to use
prepared statements and bind variables whenever possible, as long as they don't lead to inefficient execution plans
(refer to Chapter 2 for detailed information about this topic). Whenever a prepared statement is frequently used, it's
a good idea to reuse it. By doing so, not only do you avoid soft parses, but you reduce the client-side CPU utilization
and the network traffic as well. The only problem related to keeping a prepared statement open has to do with
memory utilization, on both the client side and the server side. This means that keeping thousands of cursors open
per session must be done carefully and only when the necessary memory is available. Also note that the
open_cursors
initialization parameter limits the number of cursors that can be concurrently kept open by a single session. In case
many prepared statements have to be cached, it's probably better to use client-side statement caching with a carefully
sized cache instead of manually keeping them open. In this way, the memory pressure may be mitigated by allowing a
limited number of prepared statements to be cached.
Long Parses
In case of long parses that are executed only a few times (or as in the previous example, only once), it's usually not
possible to avoid the parse phase. In fact, the SQL statement must be parsed at least once. In addition, if the SQL
statement is rarely executed, a hard parse is probably inevitable because the cursor will be aged out of the library
cache between executions. This is especially true if no bind variables are used. Therefore, the only possible solution is
to reduce the parsing time itself.
What causes long parse times? Commonly, they are caused by the query optimizer evaluating too many different
execution plans. In addition, it can happen because of recursive queries executed on behalf of dynamic sampling.
Solving the latter should be obvious: you either reduce the level of dynamic sampling or completely avoid using it.
However, solving the former is a bit more tricky. In fact, to shorten the parse times, you must reduce the number of
evaluated execution plans. This is generally possible only by forcing a specific execution plan through hints or stored
outlines. For example, after creating a stored outline for the SQL statement used as an example in the “Identifying
Parsing Problems” section, the parse time is reduced by a factor of six (see Figure
12-10
). A similar effect may be reached
by directly specifying hints in the SQL statement, although this is possible only if you are able to modify the code.
Figure 12-10.
Comparison of the parse time with and without a stored outline
Working Around Parsing Problems
The previous sections describe three test cases related to quick parses. The first is simply a case of poor code writing.
The second is much better than the first. The third is the best in most situations. The dilemma is that code similar to
test case 1 has to be modified in order to be enhanced, and that, unfortunately, isn't always possible. This is because
