Database Reference
In-Depth Information
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable, plan_hash_value
2 FROM v$sql
3 WHERE sql_id = 'f364ymn1bbr4q'
4 ORDER BY child_number;
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y Y Y 4270555908
1 Y Y Y 2966233522
In summary, to increase the likelihood that the query optimizer will generate efficient execution plans, you
shouldn't use bind variables. Bind variable peeking might help. Unfortunately, it's sometimes a matter of luck whether
an efficient execution plan is generated. The only exception is when, as of version 11.1, the new adaptive cursor
sharing automatically recognizes the problem.
Best Practices
Any feature should be used only if the advantages related to its utilization outweigh the disadvantages. In some
situations, it's easy to decide. For example, there's no reason for not using bind variables with SQL statements without
a
WHERE
clause (for example, plain
INSERT
statements). On the other hand, bind variables should be avoided at all
costs whenever there's a high risk of being stung by bind variable peeking. This is especially true when the following
three conditions are met:
•
When the query optimizer has to check whether a value is outside the range of available
values (that is, lower than the minimum value or higher than the maximum value stored
in the column)
•
When a predicate in the
WHERE
clause is based on a range condition (for example,
HIREDATE >'2009-12-31'
)
When the query optimizer makes use of histograms
•
As a result, for cursors that can be shared, you should
not
use bind variables if one of the preceding three conditions
is met. In all other cases, the situation is even less clear. Nevertheless, it's possible to consider two main cases:
SQL statements processing little data
: Whenever little data is processed, the hard parse time
might be close to or even higher than the execution time. In that kind of situation, using
bind variables and therefore avoiding a hard parse is usually a must. This is especially
true for SQL statements that are expected to be executed frequently. Typically, such SQL
statements are used in data entry systems (commonly referred to as OLTP systems).
