In the world of Oracle performance tuning, Bind Variable Peeking is one of those features that is both a hero and a villain. It’s a brilliant optimization that can occasionally cause a performance nightmare known as "Plan Instability."

Here is the breakdown of how it works, why it exists, and why it might be the reason your query is fast one day and slow the next.

1. The Context: Literals vs. Binds

To understand peeking, we first have to look at how we write SQL:

• Literals: SELECT * FROM sales WHERE region = 'NORTH';

  Oracle sees this as a unique string. If you search for 'SOUTH', it’s a brand-new query that needs a brand-new Hard Parse.

• Bind Variables: SELECT * FROM sales WHERE region = :reg;

  Oracle sees this as a template. It parses the query once and reuses it for any region. This saves massive amounts of CPU.

The Problem: The Optimizer doesn't know what :reg is during the parse. Is it a region with 5 rows or 5 million rows? Without knowing the value, it can't choose the best index.

2. What is "Peeking"?

Bind Variable Peeking (introduced in 9i) is Oracle’s solution to this blindness.

When a query is Hard Parsed (the very first time it’s seen), the Optimizer "peeks" at the value currently sitting in the bind variable.

1. It sees :reg = 'NORTH'.

2. It looks at the statistics for 'NORTH'.

3. It finds that 'NORTH' only has 10 rows, so it chooses an Index Scan.

4. It freezes that plan into the library cache.

3. The Catch: "The First One Wins"

Here is where the trouble starts. Once that plan is frozen, every subsequent execution uses the same plan, regardless of what the new bind value is.

• User A searches for 'NORTH' (10 rows) $\rightarrow$ Optimizer peeks $\rightarrow$ Index Scan plan is created.

• User B searches for 'SOUTH' (5 million rows) $\rightarrow$ Oracle reuses User A's plan $\rightarrow$ Index Scan is used for 5 million rows.

Result: User B’s query hangs because an Index Scan is the worst possible way to read 5 million rows. This is often called "Bind Variable Graduation" or "Parameter Sniffing" issues.

4. Why is it a "Silent Killer"?

The reason this drives DBAs crazy is that the performance is non-deterministic.

• If the database restarts and the first person to run the report searches for a "small" value, the plan is optimized for small values.

• If the first person searches for a "large" value, the plan is optimized for large values.

• The "winner" of the hard parse dictates the performance for everyone else until the plan ages out of memory.

5. How did Oracle fix it? (ACS)

In Oracle 11g and 12c, a feature called Adaptive Cursor Sharing (ACS) was introduced to mitigate this.

Instead of being stuck with one plan, Oracle now monitors the execution. If it notices that a query using a "small value" plan is suddenly processing "large value" row counts, it marks the cursor as "Bind Sensitive." Eventually, it will create multiple versions of the plan (one for small values, one for large) and pick the right one based on the bind variable provided.

6. Summary: Peeking at a Glance

How to check if you are a victim of peeking?

You can query V$SQL_BIND_CAPTURE to see what values Oracle "peeked" at when it generated the execution plan:

SELECT name, value_string 
FROM v$sql_bind_capture 
WHERE sql_id = 'your_sql_id';

Peer Tip: If you have a query that is wildly inconsistent, sometimes the best fix is to use a Literal for that specific "skewed" column (like STATUS) while keeping Binds for unique values (like USER_ID). This forces the Optimizer to rethink the plan for each status.