Dealing with Bind Variable Peeking

Tanel Poder had a great post about bind variable peeking, 
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


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
where t.i_id = ca.i_id
and t.idnbr = ca.idnbr

and = :b1 and > 0;  <– ID in questiion has 3,212,685 rows in ca

What does the Explain Plan Look like?
Execution Steps:

Step # Step Name

Rows Returned
Est. KBytes Returned
This plan step retrieves all rows from
table DI_MTCH_389431_6200.
This plan step retrieves a single
ROWID from the B*-tree index MBR_PK.
This plan step retrieves rows from
table MBR_ACCT through ROWID(s) returned by a local partitioned
This plan step has no supplementary
description information.
This plan step joins two sets of rows
by iterating over the driving, or outer, row set (the first child of the
join) and, for each row, carrying out the steps of the inner row set (the
second child). Corresponding pairs of rows are tested against the join
condition specified in the query’s WHERE clause.
This plan step designates this
statement as a SELECT statement.

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… :))

5 thoughts on “Dealing with Bind Variable Peeking

  • February 4, 2010 at 10:10 pm


    We had this same issue. One solution if you aren't parsing the same query thousands and thousands of times is to not use bind variables. If you have histograms, it will peek. If it doesn't, then it doesn't matter as it will use the Number of Distinct Values to try and determine the cardinality.

    If you do use a lot of these, then hard coding the join is one solution. We had this issue and what my developers did in one app was they collected their own stats and if a customer was a "large" they passed in one query with a comment in the hint and if they were "small" then it passed in another. Large used hash joins and small used nested loops. The developers had to take the time to look this up in the app but it was well worth it to avoid a nested loop query on a large data set running forever. I guess you have to pick your poison 🙂

  • February 5, 2010 at 9:29 am

    Hi Tom,
    Thank you for the info and yes, we are doing very similar resolutions for some of ours as well, (determining dynamic hints on the size of the feed and collecting statistics in the code.) I didn't even think about it, but that may be why that code isn't showing this issue!

  • February 28, 2010 at 10:55 am


    For the first check second step you wrote

    "Create histograms on the columns that use bind variables."

    Are you sure about this ? I think it needs to be "Do Not Create" because from my experience, before "adaptive cursor sharing" in 11G, histograms and binds are mostly not soul mates and their usage is the primary cause of bind peek problems (from what I saw and read). If your suggestion is not a typo then can you please share what is the reason behind this suggestion ?

  • February 28, 2010 at 9:21 pm

    I think you're right that a "simple create histograms" on the columns is not the correct approach on here, let alone stating I'm in a 10g environment, but I should be elaborating on what DID cause me to create a histogram on ONE of the columns on a composite index. I think also the sincere approach to the problem would cause most of us to inspect the indexing choices on the table, as this was one of the problems, (which is also a consistent issue I find in my environment, too.) This was a three column index, where creating a histogram on the one column actually corrected the problem. I have two other similar situations, but again, is the bind variable peek issue the real problem here or is the issue more complex? I'd point towards the latter, so you are correct in pointing that out…:)

  • Pingback: ASH Analytics- Activity Focused on Session Identifiers, Part III - Oracle - Oracle - Toad World

Comments are closed.