检查绑定变量窥探和自适应游标共享状态

命令

-- 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>';

作用

优化器在首次硬解析时窥探绑定值来选择计划。自适应游标共享可在有直方图的列上按绑定值创建不同的子游标/计划；这些查询显示游标是否为 bind-sensitive/aware、窥探了哪个值，以及创建子游标的原因。

适用场景

用于"同一条 SQL 时快时慢"的投诉、数据分布倾斜，以及子游标数量激增（high version count）时。

注意

无需许可；只读。V$SQL_BIND_CAPTURE 是采样数据（默认约每 15 分钟一次），且只保存 WHERE/HAVING 中的绑定变量；并非每次执行的值。ACS 只有在几次糟糕的执行之后才会使游标变为 bind-aware。对于关键 SQL，将多个计划加载到 SQL plan baseline 比依赖 ACS 更确定。