The only way you can use a parsed statement repeatedly for multiple executions is if a SQL statement is identical to the parsed statement. Two SQL statements are identical if they use exactly the same code, including case and spaces. The reason for this is that when Oracle
compares a new statement to existing statements in the library cache, it uses simple string comparisons. In addition, any bind variables used must be similar in data type and size. Here are a couple of examples that show you how picky Oracle is when it comes to considering whether two SQL statements are identical.
In the following example, the statements aren’t considered identical because of an extra space in the second statement:
SELECT * FROM employees;
SELECT * FROM employees;
In the next example, the statements aren’t considered identical because of the different case
used for the table Employees in the second statement. The two versions of employees are termed literals because they’re literally different from each other.
SELECT * FROM employees;
SELECT * FROM Employees;
Let’s say users in the database issue the following three SQL statements:
SELECT * FROM persons WHERE person_id = 10
SELECT * FROM persons WHERE person_id = 999
SELECT * FROM persons WHERE person_id = 6666
Oracle uses a different execution plan for the preceding three statements, even though they
seem to be identical in every respect, except for the value of person_id. Each of these statements has to be parsed and executed separately, as if they were entirely different. Because all three are essentially the same, this is inherently inefficient. As you can imagine, if hundreds of thousands of such statements are issued during the day, you’re wasting database resources and the query performance will be slow. Bind variables allow you to reuse SQL statements by making them “identical,” and thus eligible to share the same execution plan.
In our example, you can use a bind variable, which I’ll call :var, to help Oracle view the three
statements as identical, thus requiring a single execution instead of multiple ones. The person_id values 10, 99, and 6666 are “bound” to the bind variable, :var.
Your replacement SQL statement using a bind variable, then, would be this:
SELECT * FROM persons WHERE person_id = :var
Using bind variables can dramatically increase query performance.
Comments