The perils of Non-Use of
Bind Variables in Oracle
The biggest
problem in many applications is the non-use of bind variables.
Oracle bind variables are a super important way to make Oracle SQL
reentrant.
Why is the use of bind variables such an issue?
Oracle uses a
signature generation algorithm to assign a hash value to each SQL
statement based on the characters in the SQL statement. Any change
in a statement (generally speaking) will result in a new hash and
thus Oracle assumes it is a new statement. Each new statement must
be verified, parsed and have an execution plan generated and stored,
all high overhead procedures.
The high overhead procedures might be avoided by using bind variables. See
these notes on Oracle cursor_sharing
for details.
Ad-hoc query generators
(Crystal Reports, Discoverer, Business Objects) do not use bind variables, a major reason for Oracle developing
the cursor_sharing parameter to force SQL to use bind
variables (when cursor_sharing=force).
