Check bind peeking and adaptive cursor sharing status

Command

-- 1) Child cursors: bind-sensitive / bind-aware / shareable?
SELECT sql_id, child_number, executions, plan_hash_value,
       is_bind_sensitive, is_bind_aware, is_shareable,
       ROUND(buffer_gets/NULLIF(executions,0)) AS gets_per_exec
FROM   v$sql
WHERE  sql_id = '<sql_id>'
ORDER  BY child_number;

-- 2) ACS histogram and selectivity ranges:
SELECT child_number, bucket_id, count
FROM   v$sql_cs_histogram
WHERE  sql_id = '<sql_id>'
ORDER  BY child_number, bucket_id;

SELECT child_number, predicate, range_id, low, high
FROM   v$sql_cs_selectivity
WHERE  sql_id = '<sql_id>';

-- 3) Peeked / captured bind values:
SELECT child_number, name, position, datatype_string, value_string,
       TO_CHAR(last_captured,'YYYY-MM-DD HH24:MI:SS') AS captured
FROM   v$sql_bind_capture
WHERE  sql_id = '<sql_id>'
ORDER  BY child_number, position;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', NULL, 'TYPICAL +PEEKED_BINDS'));

-- 4) Why was a new child created?
SELECT child_number, bind_mismatch, bind_equiv_failure,
       optimizer_mismatch, load_optimizer_stats
FROM   v$sql_shared_cursor
WHERE  sql_id = '<sql_id>';

What it does

The optimizer picks a plan by peeking at the bind value on the first hard parse. Adaptive cursor sharing can create different child cursors/plans per bind value on columns with histograms; these queries show whether the cursor is bind-sensitive/aware, which value was peeked, and why children were created.

When to use it

For "the same SQL is sometimes fast, sometimes slow" complaints, with skewed data distribution, and when the child cursor count explodes (high version count).

Caution

No license required; read-only. V$SQL_BIND_CAPTURE is sampled (about every 15 minutes by default) and only holds WHERE/HAVING binds; it is not the value of every execution. ACS makes a cursor bind-aware only after a few bad executions. For critical SQL, loading several plans into a SQL plan baseline is more deterministic than relying on ACS.