The order-history screen is fast for every customer except one. Or it's fast all morning and crawls all afternoon. Or it was fine for months and became slow the night after a restart, with nobody deploying anything. You pull the statement and run it yourself with the problem value, and it comes back instantly. The SQL is identical. The data hasn't changed. The plan in the execution history is the same plan it always uses.
That last sentence is the clue. The statement uses a bind variable, which is the right thing to do, and the cursor for it holds one plan. That plan was chosen for whichever value happened to arrive first. On a column where values are wildly unevenly distributed, that's a plan built for one kind of caller and handed to every other. This post is how that happens, why Oracle's safety net for it, adaptive cursor sharing, doesn't always catch it, how to see it in the cursor cache, and how to fix it, with a lab that reproduces it on demand.
Bind peeking: the first caller picks the plan
When a statement with a bind variable is hard-parsed, the optimizer peeks at the actual bind value and costs the plan as if that value were a literal. That's usually good: a plan built for a real value beats a plan built for an average. The catch is what happens next. The finished cursor goes into the shared pool and every later execution reuses it, whatever value it binds. The plan is decided once, by the first caller.
On a column with evenly spread values, that doesn't matter: one value costs about the same as another. It matters on a skewed column, where a few values are rare and one is everywhere: a status code where 99.9% of rows are CLOSED, a country column dominated by one country, a flag that's almost always N. If the column has a histogram, the optimizer knows about the skew and builds very different plans for the rare value and the common one. Peeking means it builds one of them, and everyone gets that one.
The classic shape is a join. For a rare value, a few hundred driving rows, the best plan is nested loops: for each order, look up its customer by primary key. For the common value, hundreds of thousands of rows, the best plan is a hash join: read both tables once and match them in memory. Nested loops for the common value means one index probe per row, hundreds of thousands of times.
A bind-variable statement is optimized once, for the first value it sees, and the cursor is reused for every value after. On a skewed column with a histogram, a rare first value produces a nested-loops plan that is right for a few rows; the common value then inherits it and pays for one index probe per row. Adaptive cursor sharing can split the cursor into one plan per kind of value, but only once it has seen executions that differ enough; until then every caller gets the first caller's plan.
Adaptive cursor sharing: the safety net, with holes
Oracle has known about this since 11g, and adaptive cursor sharing (ACS) is its answer. A cursor that peeked a bind on a column with a histogram is marked bind-sensitive. Oracle then watches its executions, sorting each one into a small number of buckets by how much work it did. If executions with different bind values land in clearly different buckets, the cursor becomes bind-aware: from then on, each new value is checked against the existing child cursors, and a value that needs a different plan gets its own child cursor with its own plan.
That works well when the difference is visible. The hole is in "clearly different". If the rare value's first execution and the common value's executions land in the same bucket, ACS sees nothing to react to, and the cursor stays exactly as it was. And whether you're protected can depend on the order in which values arrive after each restart, shared pool flush, or statistics refresh, which nobody controls.
Prove it
The lab builds ORDERS with 200,000 rows and a skewed STATUS, 200 OPEN and 199,800 CLOSED, with a frequency histogram on the column, and joins it to a 1,000,000-row CUSTOMERS table. It flushes the shared pool, runs the statement once with 'OPEN', then five times with 'CLOSED', each in a fresh session, measuring session logical reads from V$MYSTAT and the child cursor each execution ran:
>> Fresh shared pool. The SAME statement, bound: ... where o.status = :s
OPEN rows=200 logical reads=2624 child cursor=0 plan=NESTED LOOPS
CLOSED rows=199800 logical reads=603391 child cursor=0 plan=NESTED LOOPS
CLOSED rows=199800 logical reads=603354 child cursor=0 plan=NESTED LOOPS
CLOSED rows=199800 logical reads=603354 child cursor=0 plan=NESTED LOOPS
CLOSED rows=199800 logical reads=603354 child cursor=0 plan=NESTED LOOPS
CLOSED rows=199800 logical reads=603354 child cursor=0 plan=NESTED LOOPS
The optimizer peeked 'OPEN', estimated 199 rows from the histogram, and chose nested loops, which is right for 200 rows. Every 'CLOSED' run then reused child cursor 0 and its nested loops: 603,354 logical reads, five times in a row. Adaptive cursor sharing never marked the cursor bind-aware. The first 'OPEN' execution, including its hard parse, and every 'CLOSED' execution fell into the same middle bucket, so the safety net never saw a difference. (The plan was even an adaptive plan, with a statistics collector that can switch join methods, but an adaptive plan settles on its first execution and the settled plan is what gets reused.)
Give 'CLOSED' its own plan and the cost collapses:
>> The fix for a skewed, low-cardinality column: the predicate as a literal -> one cursor + plan per value
CLOSED rows=199800 logical reads=34716 child cursor=0 plan=HASH JOIN
OPEN rows=200 logical reads=806 child cursor=0 plan=NESTED LOOPS
34,716 logical reads instead of 603,354: the same rows, 17 times less work, because the plan was built for the value it runs. And 'OPEN' keeps its cheap nested loops at 806.
Don't take my word for it — run it. The bind-peeking lab builds the skewed tables on Oracle Database Free, peeks the rare value, and asserts that every common-value run inherits nested loops on the same cursor at 10 times or more the work of a hash join, without adaptive cursor sharing splitting it. Then it gives each value its own plan and asserts the common value drops to 10% or less of that work. If the trap stops reproducing or the fix stops working, the run fails. Proven on every CI push.
See it in your own system
Start from the statement, not the plan. A query whose cost swings by value shows up as high variance in buffer gets per execution for one SQL_ID. Then look at its cursors:
-- one row per child cursor: which ones are bind-sensitive / bind-aware, and what each costs
SELECT child_number, plan_hash_value, is_bind_sensitive, is_bind_aware,
executions, ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY child_number;
-
Bind-sensitive
Y, bind-awareN, one child, huge gets per execution: the lab's situation. The cursor knows the bind matters, but it never split. -
Several children with different plan hash values, bind-aware
Y: ACS is doing its job. -
V$SQL_CS_HISTOGRAMshows how its executions were bucketed; all executions in one bucket means ACS has nothing to react to.
To see which value the plan was built for, ask for the peeked binds:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'TYPICAL +PEEKED_BINDS'));
In the lab that prints :S ... 'OPEN' above a nested-loops plan, the whole diagnosis in one screen. In AWR, the same statement appearing with more than one plan hash value over time, with very different elapsed times, is the historical fingerprint: the plan changes whenever the cursor is rebuilt with a different first value.
The fix
Pick by how the column behaves. In order of preference:
- For a skewed, low-cardinality column, use a literal for that predicate. A status code with five values makes five cursors, each with the right plan. That's no hard-parse storm: the storm comes from literal values with high cardinality, like IDs. Keep binds for everything else in the statement. This is the exception to "bind everything" that the bind variables post ends on, and the lab's fix.
- Split the query in the application when it already knows which kind of value it has: a separate statement for the "open orders" screen and the "history" screen. Two statements, two plans, no guessing.
- A SQL plan baseline with more than one accepted plan. Baselines cooperate with adaptive cursor sharing: accept the nested-loops plan and the hash-join plan, and each value can use the one that fits. See SQL plan management.
- Remove the histogram, if one plan is genuinely acceptable for every value. Without it the optimizer stops distinguishing values and picks one middle-of-the-road plan. It's a legitimate choice when no value is catastrophic, but a bad one when, as here, the wrong plan costs 17 times the work.
What not to do: don't "fix" it by flushing the shared pool or purging the cursor when it goes bad. That just rolls the dice again on which value arrives first, and the problem returns on its own schedule.
What teams get wrong
- Blaming the data or the database for "random" slowness. It isn't random: it's the plan built for whichever value arrived first, and it changes when the cursor is rebuilt.
- Testing by hand. Paste the statement into a SQL tool with the slow value typed in and it's different SQL text, so it gets its own hard parse and the right plan, and it's fast. The problem only shows on the shared, bind-variable cursor the application uses.
- Trusting adaptive cursor sharing to handle it. It helps when executions differ enough to land in different buckets, and quietly does nothing when they don't, as the lab shows five times in a row.
- Flushing the shared pool as the fix. It resets the lottery; it doesn't change the odds.
- Disabling bind peeking database-wide. It's a hidden parameter, and switching it off trades one wrong plan for a plan built for no real value at all, for every statement in the database.
- Turning a whole application to literals to escape it. That's the hard-parse storm all over again. Use literals only for the skewed, low-cardinality predicate.
Frequently asked questions
What is bind peeking in Oracle?
Bind peeking is the optimizer's behavior of looking at the actual value of a bind variable when it hard-parses a statement, and optimizing the plan as if that value were a literal. It produces a plan suited to a real value rather than to an average. The resulting cursor is then stored in the shared pool and reused by every later execution of the same statement, regardless of the value those executions bind. So the plan is effectively chosen by whichever value happened to arrive first when the cursor was built. On columns with evenly distributed values this rarely matters; on skewed columns with histograms it can make the same SQL fast for one value and very slow for another.
Why is the same Oracle query fast for one value and slow for another?
Usually because the statement uses a bind variable on a skewed column and the cursor's single plan was built for a different kind of value. If the first execution peeked a rare value, the optimizer may have chosen nested loops or an index-driven plan that is ideal for a few rows; every later execution reuses that plan, and a common value matching hundreds of thousands of rows then pays for one index probe per row. In the companion lab the same join costs 2,624 logical reads for a rare value and 603,354 for the common value on the inherited plan, versus 34,716 with its own hash-join plan. Running the query by hand with the slow value often looks fast, because a manual run with different text gets its own hard parse and the right plan.
What is adaptive cursor sharing?
Adaptive cursor sharing, introduced in Oracle 11g, lets a statement with bind variables have more than one plan. A cursor that peeked a bind on a column with a histogram is marked bind-sensitive, and Oracle monitors how much work its executions do, sorting them into buckets. If executions with different bind values land in clearly different buckets, the cursor is marked bind-aware, and new bind values are then checked against the existing child cursors; a value that needs a different plan gets a new child cursor with its own plan. You can see it in V$SQL (IS_BIND_SENSITIVE, IS_BIND_AWARE and multiple child cursors) and in V$SQL_CS_HISTOGRAM.
Why didn't adaptive cursor sharing fix my plan?
Because adaptive cursor sharing only reacts when it sees executions that differ enough to fall into different workload buckets. If the rare value's first execution and the common value's executions land in the same bucket, the cursor stays bind-sensitive but never becomes bind-aware, and every execution keeps using the original plan. In the companion lab the first execution with the rare value, including its hard parse, and all five executions with the common value fell into the same bucket, so the cursor never split even though the common value did 17 times the necessary work each time. Whether a given workload is protected can also depend on the order in which values arrive after a restart, a shared pool flush, or a statistics refresh.
How do I find which bind value a plan was built for?
Use DBMS_XPLAN.DISPLAY_CURSOR with the PEEKED_BINDS format option, for example SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number, 'TYPICAL +PEEKED_BINDS')). The output includes a Peeked Binds section showing the value the optimizer saw when it built that child cursor's plan. Combine it with V$SQL for the same SQL_ID: one row per child cursor, its plan hash value, whether it is bind-sensitive and bind-aware, and buffer gets per execution. A single child with huge gets per execution and a peeked value from the rare end of a skewed column is the bind-peeking signature.
Should I use literals instead of bind variables to avoid bind peeking problems?
Only for the specific predicate on a skewed, low-cardinality column, such as a status code or a flag with a handful of values. Each distinct literal gets its own cursor and its own plan, and with only a few distinct values that means only a few cursors, which is no burden on the shared pool. Keep bind variables for high-cardinality values like IDs, names, and dates: using literals there causes a hard-parse storm, with thousands of nearly identical cursors, shared pool pressure, and latch contention. The rule is bind by default, literal by exception, and the exception is low-cardinality skew.
Does flushing the shared pool fix a bad bind-peeked plan?
Only by accident, and only temporarily. Flushing the shared pool, or purging the one cursor with DBMS_SHARED_POOL.PURGE, forces a new hard parse, and the new plan will be built for whichever bind value arrives first afterwards. If that is the common value, things look fixed until the next time the cursor is rebuilt with the rare value first; if it is the rare value, nothing changes. It resets the lottery without changing the odds. Fix the cause instead: give the skewed predicate a literal, split the statement in the application, or use a SQL plan baseline with more than one accepted plan.
Should I disable bind peeking?
No. Disabling bind peeking requires a hidden parameter, which should only be changed under Oracle Support's guidance, and it applies to every statement in the database. Without peeking, the optimizer builds plans using average selectivity rather than any real value, so instead of a plan that is right for some values you get a plan that is right for none of the skewed ones. Address the specific statements that suffer: literals for low-cardinality skewed predicates, separate statements per case, SQL plan baselines, or removing a histogram that does more harm than good.
Bind peeking sits right at the meeting point of two other posts in this series. Bind variables are why the cursor is shared in the first place, and that's the right default; indexes are why the same predicate deserves an index for one value and a full scan for another. The plan itself is something you can read in the execution plan, the histogram that sets the trap comes from optimizer statistics, and SQL plan management is one way to pin more than one good plan. When one statement is fast for some values and slow for others, look at the peeked bind before anything else, and prove it the way that ends the argument — with the bind-peeking lab, where the same query costs 2,624 reads for one value and 603,354 for another until each value gets its own plan.
Originally published at uptimearchitect.com.
