Database Reference
In-Depth Information
Bind Variables Trap
The most common mistake I come across in using the
EXPLAIN PLAN
statement is to specify a SQL statement that is
different from the one to be analyzed. Naturally, that could lead to the wrong execution plan. Because the formatting
itself has no impact on the execution plan, the difference is usually caused by replacing bind variables. Let's examine
the execution plan used by the query in the following PL/SQL procedure:
CREATE OR REPLACE PROCEDURE p (p_value IN NUMBER) IS
BEGIN
FOR i IN (SELECT * FROM emp WHERE empno = p_value)
LOOP
NULL; -- do something
END LOOP;
END;
A commonly used technique is to copy/paste the query by replacing the PL/SQL variable with a literal. You
execute a SQL statement like this:
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = 7788
The problem is that by replacing the bind variable with a literal, you submit a different SQL statement to the
query optimizer. This change—because, for example, of the presence of SQL profiles, stored outlines, SQL plan
baselines (more about these topics in Chapter 11), or the method used by the query optimizer to estimate the
selectivity of the predicate used in the
WHERE
clause (literals and bind variables aren't handled in the same way)—
might have an impact on the decisions taken by the query optimizer.
The correct approach is to use the same SQL statement. This is possible because bind variables can be used with
the
EXPLAIN PLAN
statement. You should, as an example, execute a SQL statement like the following one (notice that
the
p_value
PL/SQL variable was replaced by the
:B1
bind variable because this is what the PL/SQL engine would do):
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = :B1
Nonetheless, using bind variables with the
EXPLAIN PLAN
statement has two problems. The first is that, by
default, bind variables are declared as
VARCHAR2
. As a result, the database engine might automatically add implicit
conversions, and that could change the execution plan. You can check this with the information about predicates
shown at the end of the output generated by the
display
function in the
dbms_xplan
package. In the following output
example, the
to_number
function is used for that purpose:
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 4024650034
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
