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

The Lead Developer and I have been working very, very hard on tuning the main process that produces much of the revenue for our company. The tuning exercise has been very successful but we’ve run into some challenging on the pre-processing that occurs for each week to build the mart that the newly tuned process runs on afterwards. Due to this, I’ve been granted some time to monitor the build process a little closer than previously and although I have a lot of tuning to do, some of the largest challenges comes in the form of queries that the analysts have to execute as part of the final checks before we toggle over to the newly created mart.

These statements all have the same where clause, two different tbl_keys, (12 or 48), pulling the entire table, (each table between 90-380GB) and then ordering the data by the i_id. They all have the same parallel hints, just different tables substituted into the statements. The reason for the sort is that the data is then pulled into an application to finish the final step. The application is incapable of dealing with this type of large sorts, so they decided to do it internal to the database. The final data is used to verify that the correct data is present in the mart of the members that were fed into the primary database that the mart is based off of for the timeline.

select /*+ full(t) parallel (t 8) */ t.*
from tbl_summary t WHERE (tbl_key=12 and usr_id=0)
 
order by i_id ;

The above statement is going to result in 16 parallel slaves, pulling the entire data set for user class=0 vs. a specific id, for the last 12 months of data, then sort it by the usr_id. Note the first hint- FULL. Realize, as this data is across multiple partitions, we will not see a “partition list single” in the explain plan, but “px iterator” for the database to correctly identify the individuals that meet the criteria for each partition. Once it completes this step, then the challenge comes in sorting all that data and the problem with the second hint- PARALLEL.

I love parallel. I am thrilled with the performance gains I can attain when hashing large tables when accessing multiple partitions. I do not love parallel when someone decides to put a group by or order by into the statement. If any sorting has to occur on the statement, I’ve been seeing some odd behavior in about 80% of the executions. It appears that when sorting, it’s not Oracle sorting the data all at once, but sorting one parallel slave’s data set at a time. This creates an escalation in temp space usage that can become insanely difficult for Oracle to process efficiently and performance degrades immensely.

This is what occurred with the statements for the analysts.
With Parallel:
select /*+ full(t) parallel (t 8) */ t.*
 
from tbl_summary t WHERE (tbl_key=12 and usr_id=0)
order by i_id ;

Elapsed Time Breakdown
SQL Time (sec) 47,951.17
PL/SQL Time (sec) 0.00
Java Time (sec) 0.00

Activity By Time
Elapsed Time (sec) 47,951.17
CPU Time (sec) 9,734.81
Wait Time (sec) 38,216.35

Activity By Waits
Remaining Waits(20.9%)
User I/O Waits(58.8%)
CPU(20.3%)

I decided to query the temp usage, as I knew the above query would need to sort the ib_id and would impact the performance of the parallel execution.  The first and last sql_id in the table are the queries in question with parallel, which the one owned by kpedersen is my run at it without parallel:

Now after seeing the waits for temp, the long operations, (441 minutes left on the sort in temp…) I decide to say enough it enough and attempt the execution without the hints- just plain ol’ query of this huge table, nothing more, (see above in the table, showing my temp usage for the single process, as “Mine”.) Fifteen minutes, yes, that’s right, fifteen minutes and I had the first rows back on my screen:

SQL Text-
select t.* from tbl_summary t
WHERE (tbl_key=12 and usr_id=0) order by i_id;

DETAILS on Without Parallel:
Elapsed Time Breakdown
SQL Time (sec) 945.25
PL/SQL Time (sec) 0.00
Java Time (sec) 0.00

Activity By Time
Elapsed Time (sec) 945.25
CPU Time (sec) 370.67
Wait Time (sec) 574.58

Activity By Waits
Remaining Waits(1.1%)
User I/O Waits(59.7%)
CPU(39.2%)

Moral:
Carefully test any parallel processing when sorting is involved. You may not receive performance benefits as expected.