Using Bind Variables

The previous performance tip centered around how the Oracle database returns data to the user. This next
tip has to do with the exact way that the Oracle database processes SQL statements. Unlike the previous
tip, which did not affect your application code, this area requires that you implement your code in a par-
ticular way to reduce the use of resources and improve the performance of your Oracle database.
Earlier in this chapter, we discussed how Oracle stores SQL statements in the shared pool area of memory.
When the Oracle database receives an SQL statement, it checks the shared pool to see if an optimized execution plan already exists for the statement. If the plan exists in the pool, the plan is simply retrieved from memory, which is much more efficient than reoptimizing the statement. Use of the shared pool helps Oracle to scale for large numbers of users and perform well with any load.
If you have to reoptimize every statement, each statement adds to the overall workload of the target Oracle
database. This means that the overhead on your system increases with the number of statements and that,
eventually, you will run into resource limitations that will decrease performance. If every statement has to
go through the complete cycle of execution, your scalability will be limited.
Fortunately, a real-world application is not a series of unique SQL requests. In fact, most applications use
the same SQL statements over and over again. Theoretically, this would mean that the repeated SQL will
be picked up from the shared pool, which is much less expensive from a resource standpoint. Since Oracle
needs exclusive access to some resources when optimizing an SQL statement, optimizing SQL statements
cannot be done in parallel, so the more optimizations Oracle has to do, the greater the elapsed time for a
query to be processed.
The way to reduce this potential bottleneck is to help your Oracle database to avoid performing hard
parses as much as possible. You can help this to occur by using bind variables to help re-use SQL
statements.
Remember that the method used to identify a statement is a comparison of the hash algorithm created
from the statement. The value of this hash is derived from the characters in the statement.

Consider the following two statements:
SELECT ENAME FROM EMP WHERE EMP_ID = 7
SELECT ENAME FROM EMP WHERE EMP_ID = 5
You can quickly tell that these two statements should use identical optimizer plans. If the execution plan
for the first statement is still in the shared pool, Oracle should use it, right? Unfortunately, Oracle may not
be able to find the plan, since the hash value created by the second statement will very likely be different
from the hash value created by the first statement, based on the different characters.
At this point, bind variables come to the rescue. A bind variable is a placeholder in an SQL statement.
Oracle can process a statement containing a bind variable but can only execute the statement when it
receives the value of the variable.
You identify a bind variable by preceding it with a colon in the SQL statement. The previous SQL statements
could both be represented by the single SQL statement following, which uses a bind variable.
SELECT ENAME FROM EMP WHERE EMP_ID = :n_EmpID
If you use this syntax twice, instead of using the two different SQL statements shown previously, Oracle
will be able to retrieve the execution plan from the shared pool, saving lots of resources.
The statements used to illustrate the use of bind variables previously are not that complex, so you may
doubt how much the use of bind variables could help. But remember that your own SQL statements are
considerably more complex, where the creation of an execution plan could likely be the biggest resource
hog in the entire sequence of SQL processing. Also, remember that not only will your application likely
repeat the same SQL statement over and over again but that this effect is multiplied by the use of your
application by many different users.
Those of you who think of yourself as primarily as programmers and don’t give no never mind about
databases may be rising up in protest at this point. There is some common wisdom that says the code
required to use bind variables, a PreparedStatement call in Java, executes slower than the code to execute
a hard-coded statement, a Statement call in Java. We certainly can’t argue this simple fact (although oth-
ers have), but remember that there is more to performance than simply the time required to execute a call.
The use of bind variables can have an enormous impact on the time Oracle needs to return data to the
user. Since performance is an aggregate, it doesn’t make sense to save time in the execution of a single
call if those savings are going to be outweighed by slower performance from the database.

The bottom line is that you should train yourself to use bind variables whenever you have SQL statements
that only differ in the value of a particular item in the statement and that are repeatedly executed in your
application.