Bind Peeking, Adaptive Cursor Sharing, Histograms, and Skewed Predicates

Learning outcomes

ServiceHub has one bound query on status_code. Ninety-nine percent of rows are CLOSED, while a tiny fraction are ESCALATED. One execution may benefit from scanning broadly; another may benefit from a selective index probe. The team fears that binding “locks the first plan forever.” Oracle's actual mechanisms are more nuanced: bind peeking can influence the first hard parse, a cursor can become bind-sensitive, and Adaptive Cursor Sharing (ACS) can create/select different children for materially different selectivities.

1. Bind peeking happens when Oracle hard parses an eligible bound statement

During a hard parse, the optimizer can peek at user-defined bind values and estimate predicate selectivity as if the value were known. If statistics indicate skew—often through a histogram—the value can materially affect the plan estimate. The child cursor can be marked bind-sensitive when Oracle recognizes that different bind values may cause different selectivity.

Bind peeking is not “substituting the literal into SQL text.” The parent statement remains bound and shareable. Peeking influences optimization metadata for the child.

2. Adaptive Cursor Sharing monitors whether one plan fits different bind selectivities

ACS monitors selected bound statements. If runtime behavior shows that different bind-selectivity ranges need different treatment, Oracle can mark the statement bind-aware and use extended cursor sharing to choose among children. If a new bind range produces the same plan as an existing child, Oracle can merge selectivity ranges instead of keeping needless duplicates.

3. Histograms help model skew; they do not force ACS or multiple plans

A histogram gives the optimizer value-frequency/distribution evidence. It can make the rare ESCALATED predicate estimate very different from common CLOSED. Whether that produces two physical plans depends on table/index costs, object size, caching, statistics, optimizer version/environment, and ACS heuristics.

4. Build skew deliberately

DROP TABLE servicehub_acs_case IF EXISTS PURGE;CREATE TABLE servicehub_acs_case (  case_id      NUMBER PRIMARY KEY,  status_code  VARCHAR2(12) NOT NULL,  payload      VARCHAR2(200) NOT NULL);INSERT INTO servicehub_acs_caseSELECT  LEVEL,  CASE    WHEN LEVEL <= 99000 THEN 'CLOSED'    WHEN LEVEL <= 99800 THEN 'OPEN'    ELSE 'ESCALATED'  END,  RPAD('x',120,'x')FROM dualCONNECT BY LEVEL <= 100000;CREATE INDEX sh10_l3_status_ixON servicehub_acs_case(status_code);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    USER,'SERVICEHUB_ACS_CASE',    cascade=>TRUE,    method_opt=>'FOR COLUMNS status_code SIZE AUTO'  );END;/SELECT column_name, num_distinct, histogram, num_bucketsFROM user_tab_col_statisticsWHERE table_name='SERVICEHUB_ACS_CASE'  AND column_name='STATUS_CODE';

The table is intentionally larger than earlier labs so a common value and a rare value have plausibly different access costs. On constrained Free hardware, creation may take longer; reduce to 20,000 rows if needed while preserving strong skew.

5. Execute the same parent statement with rare and common binds

VARIABLE b_status VARCHAR2(12)EXEC :b_status := 'ESCALATED';SELECT /* sh10_l3_acs */ COUNT(*)FROM servicehub_acs_caseWHERE status_code = :b_status;EXEC :b_status := 'CLOSED';SELECT /* sh10_l3_acs */ COUNT(*)FROM servicehub_acs_caseWHERE status_code = :b_status;EXEC :b_status := 'ESCALATED';SELECT /* sh10_l3_acs */ COUNT(*)FROM servicehub_acs_caseWHERE status_code = :b_status;EXEC :b_status := 'CLOSED';SELECT /* sh10_l3_acs */ COUNT(*)FROM servicehub_acs_caseWHERE status_code = :b_status;

Repeat the rare/common sequence several times if you want to give ACS more observations. Do not flush the shared pool between values: that defeats the adaptive reuse mechanism you are trying to observe.

6. Observe child state and ACS metadata

SELECT  sql_id,  child_number,  plan_hash_value,  executions,  rows_processed,  buffer_gets,  is_bind_sensitive,  is_bind_aware,  is_shareableFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l3_acs */ COUNT%'ORDER BY child_number;

SELECT  sql_id,  child_number,  predicate,  range_id,  low,  highFROM v$sql_cs_selectivityWHERE sql_id = :sql_idORDER BY child_number, range_id;SELECT  sql_id,  child_number,  bind_set_hash_value,  peeked,  executions,  rows_processed,  buffer_gets,  cpu_timeFROM v$sql_cs_statisticsWHERE sql_id = :sql_idORDER BY child_number, bind_set_hash_value;SELECT  sql_id,  child_number,  bucket_id,  countFROM v$sql_cs_histogramWHERE sql_id = :sql_idORDER BY child_number, bucket_id;

Not every ACS view must contain rows for every execution. The absence of selectivity rows does not prove binds were not used; it means that particular ACS metadata was not recorded/exposed for that cursor state.

7. Deliberately wrong: disable binds to “solve bind peeking”

Replacing binds with one literal SQL text per value may produce value-specific plans, but it can also create a hard-parse storm, increase shared-pool memory, reduce scalability, and reopen injection risk if literals are constructed unsafely. The correct response is to measure whether the workload is truly parameter-sensitive, keep histograms/statistics appropriate, inspect ACS behavior, and only then consider query redesign or governed plan controls from Chapter 09.

8. Privilege and cleanup path

GRANT SELECT ON V_$SQL TO servicehub_owner;GRANT SELECT ON V_$SQL_CS_SELECTIVITY TO servicehub_owner;GRANT SELECT ON V_$SQL_CS_STATISTICS TO servicehub_owner;GRANT SELECT ON V_$SQL_CS_HISTOGRAM TO servicehub_owner;

DROP TABLE servicehub_acs_case PURGE;-- Optional DBA cleanup of observer grants:REVOKE SELECT ON V_$SQL FROM servicehub_owner;REVOKE SELECT ON V_$SQL_CS_SELECTIVITY FROM servicehub_owner;REVOKE SELECT ON V_$SQL_CS_STATISTICS FROM servicehub_owner;REVOKE SELECT ON V_$SQL_CS_HISTOGRAM FROM servicehub_owner;

9. Production judgment

Keep binds for high-throughput application SQL. When values are genuinely skew-sensitive, inspect histogram quality, actual row counts/buffer work, bind-sensitive/bind-aware flags, and child plan hashes. ACS can be beneficial because it preserves parent SQL reuse while allowing different selectivity ranges to use appropriate children. Excess children still require diagnosis; “bind-aware” is not a free pass for uncontrolled version counts.

No management pack or COMPATIBLE change is required. Lesson 4 moves from optimizer-dependent child selection to session cursor caching and cursor-handle limits, which solve different problems and must not be tuned interchangeably.

Authoritative references

• Improving Real-World Performance Through Cursor Sharing — bind peeking, ACS lifecycle and tuning guidance
• V$SQL — IS_BIND_SENSITIVE and IS_BIND_AWARE
• V$SQL_CS_SELECTIVITY — ACS selectivity ranges
• V$SQL_CS_STATISTICS — ACS execution statistics
• DBMS_STATS — histogram gathering