Databases Reference
In-Depth Information
With this example, we have introduced the correct way to use SQL statements in our
applications:
1.
Prepare the statement.
2.
Execute the statement many times (using bind variables).
3.
Close the statement.
In this way, the costly hard parse operation is performed only once, that is in step 1.
There's more...
Parsing is a CPU-bound operation (it requires many CPU cycles) that involves latches, hence
serialization, and hence waiting. We want to avoid unnecessary parsing.
The parsing process always performs a syntax and semantic check of the statement; after
this phase, the database engine first searches for the statement in the shared SQL area.
To do so, it calculates a hash of the literal statement, and compares it with the hash of the
shared SQL statements. In this way, similar statements, which differ only in whitespaces,
in case or in the name of bind variables are treated as different.
To avoid hard parsing, write SQL statements in reusable code sections. Use bind variables
and not constants, trying to minimize the number of different queries executed against
the database.
I have recommended the use of bind variables and not constants—this
is the general rule—but don't overstate it by writing something like
select
substr(field,
:start,
:end)
as
shortfield,
...
until it's not a requirement (return a variable part of the field).
In this situation, the correct size of
shortfield
is not known to
the parser; in situations where bind variables are used in a predicate
like
select
field1
from
table1
where
field2
=
:value
and
field2
is always "Y" in
table1
, except in some records where
it is "N". Different execution plans can be chosen by the optimizer
depending on bind variable values. If we query for "Y" records, a full
table scan will be more advantageous than an index scan, which
is perfectly suited if we ask for "N" records. If a constant is really
constant, don't use a bind variable because "it's always better", but
think about the drawbacks.
When the same statement is found in a shared SQL area, a soft parse occurs, otherwise
a hard parse operation is needed. This requires two extra steps, optimizing and generating
the execution plan for the query.
In developing our application, we will try to minimize the number of hard parses and
maximize the soft-to-hard parse ratio.
