oracle -- bind peeking

The Problem with Bind Variables
Well, if bind variables are so great, why not use them all the time? Don’t we have a magic bullet—cursor_
sharing—which transforms all the bad code to sharable statements? (Those already familiar with the reasons,
especially the concept of bind-peeking, can skip to the section titled “Adaptive Cursors”.)
Consider the case where there is an index on the column STATE_CODE. The values in the column are shown
below:
select state_code, count(1)
from customers
group by state_code;
ST COUNT(1)
-- ----------
NY 994901
CT 5099
As you can see, the data is highly skewed; about 5% of the rows have ‘CT’ in them while the rest have ‘NY’. It’s
not surprising considering the population of the states. Now, let’s see what type of execution plan is generated for
the query shown earlier:
SQL> set autot traceonly explain
SQL> select * from customers where state_code = ‘NY’ and times_purchased > 3
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895K| 26M| 1532 (9)| 00:00:19 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 895K| 26M| 1532 (9)| 00:00:19 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(“TIMES_PURCHASED”>3 AND “STATE_CODE”=’NY’)
The query used a full table scan—the appropriate action since 95% of the rows are returned with the query and an
index scan would have been very expensive. Now issue the same query with ‘CT’:
SQL> c/NY/CT
1* select * from customers where state_code = ‘CT’ and times_purchased > 3
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4876992
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4589 | 138K| 56 (2)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4589 | 138K| 56 (2)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_CUST_STATE | 5099 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(“TIMES_PURCHASED”>3)
2 - access(“STATE_CODE”=’CT’)
It used the index. Again, that was appropriate; CT accounts for only 5% of the rows and an index scan will
be beneficial.
Let’s see the behavior. when using a bind variable. Here is the demonstrated behavior. in Oracle Database 10g.
SQL> var state_code varchar2(2)
SQL> exec :state_code := ‘CT’
PL/SQL procedure successfully completed.

SQL> select max(times_purchased) from customers where state_code = :state_code
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1511 (8)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 500K| 2929K| 1511 (8)| 00:00:19 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(“STATE_CODE”=:STATE_CODE)
The optimizer chose Full Table Scan on CUSTOMERS table. Shouldn’t the index be used because we are searching
for CT only, which accounts for a mere 5% of the total records? What made the optimizer choose full table scan
over index scan?
The answer is a phenomenon called bind peeking. Earlier, when you ran that query with the bind variable value set
to ‘NY’, the optimizer had to do a hard parse for the first time and while doing so it peeked at the bind variable to
see what value had been assigned to it. The value was ‘NY’. Since ‘NY’ accounts for about 95% of the rows, the
optimizer chose full table scan (as expected). In addition, it also froze the plan for the query. Next, when we issued
the same query, but for ‘CT’, the plan was not re-calculated and the optimizer used the same plan used earlier,
even though it was not the best one for the purpose. Had you used a value such as ‘CT’ instead of the
bind variable in the query, the optimizer would have picked the correct plan.
Thus as you can see, bind variables, even though they were good in most cases, actually failed in cases where the
selectivity of the values radically affected the plans, as in this example where the selectivity of the values ‘CT’ and
‘NY’ were 5% and 95% respectively. In cases where the distribution of data is such that the selectivity is almost
the same for all values, the execution plan would remain the same. Therefore smart SQL coders will choose when
to break the cardinal rule of using bind variables, employing literals instead

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-613208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-613208/