How to generate an Execution Plan with Bind Variable Peeking?

Author's name: Jaromir D.B. Nemec

Author's Email: office AT db-nemec.com

Date written: 11.03.2006

Oracle version(s): 10.2.0.1.0

Bind variable peeking introduced in Oracle Release 9 can be
extremely helpful in some situations where the additional information leads
to a better execution plan. On the other side this feature makes it much more
difficult to see the 'real' execution plan using explain plan or autotrace
for statements with bind variables as those tools don't perform the peek. The
trivial answer to the question above is to substitute the bind variable with
a literal value but there are some subtle issues with variable data type that
could lead to a different plan.

Illustration Example

Let's illustrate the problem on a small example. We define a table T with a
column N. Column N contains numbers from 0 to 100 but is extremely skew as the
value 0 repeats 100 times. We define an index on N and collect statistics. Note
the usage of high pctfree to make the table and index brighter.

Well,
again we see index range scan is used to access the table. Let's examine the
v$sql_plan view to be absolutely sure which plan was used.

Execution
Plan from V$SQL_PLAN

As
you probably observed, we placed a special comment in the select above
containing a string unique_string. This string has two roles. First it
guaranties that the select will be hard parsed, as there certainly isn't any
identical select in the shared pool. Secondly we will use this string to find
our statement in the view v$sqltext. From v$sqltext we switch to v$sql_plan,
transfer the data to the plan_table and finally use the dbms_xplan to display
the execution plan. Note that due to incompatibility issues between v$sql_plan
and plan table some transformation is required, the presented syntax is
suitable for the 10g plan_table. You will need to comment out some of the columns
while using the release 9i.

We
see that the used execution plan performs full table scan. Both explain plan
and autotrace reported a misleading plan. The bind variable peeking effectively
changed the execution plan.

Explain
Plan with Bind Variable Peeking without Executing the Statement

The
disadvantage of the approach above is that it is necessary to execute the
statement before we see the used execution plan. This could be undesirable in
some cases for example if the tested query is long running. How can we populate
the view v$sql_plan without an execution the statement? A simple possibility is
to define the query as a cursor in a PL/SQL block. The trick is to only open
and close the cursor without fetching the rows from it. The whole operation is
illustrated in the example below. Check the reference section below for the
credit of this idea. Note that we use the unique string in a hint like comment
as the ordinary comment will be cut off while unifying the select statement
within the PL/SQL statement transformation. The access of the execution plan
from the v$sql_plan is identical with the preceding example.

Conclusion

The
proposed technique allows to generate the real execution plan using the bind
variable peeking without the necessity of executing the statement. This 'higher
precision' of execution plan is required in following situations where the
missing of bind variable peeking can lead to a different execution plan:

the column in the predicate
has a histogram (the most known case also used in our example)

due to bind variable
peeking the exact partition can be evaluated (the partition selection
switches from KEY - KEY to N - N)

In
general a care should be taken as the bind variable peeking (as a result from
either bugs or features) doesn't work for all clients. I tried initially to
trigger the hard parse with bind variable peeking using the dbms_sql.parse but
the 10053 trace showed no effect. Apparently (as in 10g R2) the dbms_sql
package doesn't support bind variable peeking.

The
original idea of the open / close of cursor to trigger the hard parse is from
Tom Kyte and can be found on AskTom,
an other threads on this side discuss bind
variable peeking and JDBC.

Further
work: This approach doesn't address the problem of explaining of DML statements
with bind variables - in this case is probably even more important to avoid the
execution of the statement while explaining it.