Databases Reference
In-Depth Information
In the following screenshot, we can see an excerpt from the
TKPROF
output related to the
query in step 17 (ID = 1):
We can see that, regardless of the auto-trace results obtained earlier,
TKPROF
shows us the
correct result. The optimizer has also chosen a different execution plan, when using bind
variables, to obtain better performance using the index, when querying for an infrequent
value. When the predicate discards only a few records of the table, a full table scan is used.
There's more...
Pre Oracle Database 10
g
, bind variables and histograms could not be used together. To
use the histograms, the optimizer was required to know the actual value of the predicate
and choose the appropriate execution plan for that particular value of the filter. To extend
this feature in situations where both bind variables and histograms are used, the optimizer
"peeks" the value used for bind variables the first time a statement is parsed—as if the user
had used literals instead of bind variables—and the execution plan obtained is then used for
subsequent executions of the same query.
However, this feature, called Bind Variable Peeking, can be disruptive in situations like the
one in our example. When the statement was parsed, the actual value of the bind variable
was 2, and for that particular case, the best choice was an index scan. Unfortunately, for the
most part of the table, this isn't the best choice.
