Dealing with Bind Variable Peeking

Tanel Poder had a great post about bind variable peeking, http://blog.tanelpoder.com/2010/02/02/bind-variable-peeking-execution-plan-inefficiency/ 
I run into this exact issue more often than I would like to and have found a few tricks to refrain it from happening in code that is “vulnerable” to the situation.

Truthfully, there isn’t much you can do when Oracle has gone in and performed a bind peek and chosen a poor example to peek on.  As a DBA, you simply know there is an issue when the explain plan states that the CBO has determined the cost is exceptionally low to perform a nested loop on a few rows, although a quick check for the actual value or of the actual rows being processed will show otherwise.

When you are lucky enough that you have internal code that is the victim of this malicious and unprovoked “bind variable peek attack” 🙂 there are some techniques you can utilize to stop it from becoming a bad habit for the database.

First, check for the following:
1.  Tables in hash joins that are missing or have stale statistics. 

• If they are stale, ensure that statistics are kept up to date.
• Create histograms on the columns that use bind variables.

2.  If a table is updated with dynamic sampling, (search in the buffer for ‘%OPT_DYN%’ and the table name   (s).  View the ACTUAL statement in the buffer for what columns in the hash have been collected, (this might include the column with the bind variable from the other table, quite a surprise the first time I came across this!)

• Ensure, if this is a datawarehouse environment, you are not using the default setting of 2, (I prefer 4-6 for datamarts and warehouse environments.)
• If at all possible-  COLLECT STATISTICS!  Dynamic sampling should be an enhancement to, not a replacement for statistics!
• If in a warehouse or mart environment, once the dimension and fact tables are built-  LOCK THE STATS.  This will allow the dynamic sampling to affect the smallest percentage of tables, (See Post, The Good, the Bad and The Ugly.)

3.  If tables are partitioned, inspect the partitioning key and partition sizes.

• Are there any “place holder” or empty/truncated partitions that can be removed?  These can cause serious havok with explain plans in a warehouse.
• Are the partitions of similar size or are the partitioning keys leaving the data askew across the partitions?  This will leave you more vulnerable to this situation.
• If the above is in play-  consider placing the USE_HASH  and FULL hint in vulnerable code to hinder the nested loops issue often seen with bind variable peeking.  **Also note that the FULL hint does not mean full table in a paritioned table, but will result in full partition or just the partition iterator scan across the necessary partitions to fulfill the criteria.

A good example is the following query from our main production database.  The Customer Accounts table has

select

ca.rowid as ca_row_id,
case when (nvl(t.do_not_call_flag,ca.do_not_call_flag) <> ca.do_not_call_flag) then t.do_not_call_flag else ca.do_not_call_flag end as upd_do_not_call_flag,
…
nvl (t.flex_5, ca.flex_5) as upd_flex_5,
nvl (t.flex_6, ca.flex_6) as upd_flex_6,
nvl (t.flex_7, ca.flex_7) as upd_flex_7,
nvl (t.flex_8, ca.flex_8) as upd_flex_8,
nvl (t.flex_9, ca.flex_9) as upd_flex_9
from OWNR.DI_MTCH_389431_620 t,  <–CTAS Performed just before this step
OWNR.MBR_ACCT ca
where t.i_id = ca.i_id
and t.idnbr = ca.idnbr
and ca.id = :b1 and t.id > 0;  <– ID in questiion has 3,212,685 rows in ca

What does the Explain Plan Look like?
Execution Steps:

Step # Step Name
6 SELECT STATEMENT
5 NESTED LOOPS
1 OWNR.DI_MTCH_389431_620 TABLE ACCESS [FULL]
4 PARTITION LIST [SINGLE]
3 OWNR.MBR_ACCT TABLE ACCESS [BY LOCAL INDEX ROWID]
2 OWNR.MBR_PK INDEX [UNIQUE SCAN]

Nice explain plan, right?  Unfortunately, the nested loops and the choice to utilize the PK on MBR_ACCT was a poor choice.  The ID has over 3 million rows in MBR_ACCT, yet in this explain plan, the bind variable peek chose one with an incredibly small number, so the index looks very attractive to the CBO.

The resolution for this one was to place a USE_HASH(ca,t) FULL(ca) into the code, which bypasses the global index and proceeds to the partition directly, (ask me another time why I’m not a real fan of global indexes… :))