Oracle：建立原始绑定名称与捕获绑定变量的映射及相关问题

1. 关联绑定变量与原始参数名 #

v$sql_bind_capture中的绑定变量（如:B1）无法直接通过position/usage_id关联all_arguments/all_identifiers，因为PL/SQL编译时会对绑定变量重命名或优化。可通过以下两种方法建立映射：

方法一：解析包体获取绑定映射 #

执行脚本解析test_pkg包体，提取存储过程内游标与参数的对应关系：

DECLARE
    l_cursor_id INTEGER;
    l_bind_cnt  INTEGER;
    l_bind_name VARCHAR2(100);
    l_arg_name  VARCHAR2(100);
BEGIN
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    -- 定位get_bills存储过程的代码段
    DBMS_SQL.PARSE(l_cursor_id, q'[
        SELECT text FROM all_source 
        WHERE name = 'TEST_PKG' AND type = 'PACKAGE BODY' 
        AND line BETWEEN 
            (SELECT line FROM all_source WHERE name = 'TEST_PKG' AND type = 'PACKAGE BODY' AND text LIKE '%GET_BILLS%')
            AND (SELECT line FROM all_source WHERE name = 'TEST_PKG' AND type = 'PACKAGE BODY' AND text LIKE '%END GET_BILLS%')
    ]', DBMS_SQL.NATIVE);
    l_bind_cnt := DBMS_SQL.NUM_BVARS(l_cursor_id);
    
    FOR i IN 1..l_bind_cnt LOOP
        DBMS_SQL.DESCRIBE_BIND_VARIABLE(l_cursor_id, i, l_bind_name);
        -- 关联all_arguments获取原始参数名
        SELECT argument_name INTO l_arg_name
        FROM all_arguments
        WHERE owner = USER
          AND object_name = 'TEST_PKG'
          AND procedure_name = 'GET_BILLS'
          AND position = i;
          
        DBMS_OUTPUT.PUT_LINE('绑定变量 ' || l_bind_name || ' 对应参数: ' || l_arg_name);
    END LOOP;
    
    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(l_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
        END IF;
        RAISE;
END;
/

方法二：通过V$SQL_PLAN关联PL/SQL对象 #

针对存储过程内的查询游标，可通过执行计划关联PL/SQL对象与参数：

SELECT
    b.name AS bind_variable,
    a.argument_name AS original_param_name
FROM
    v$sql_bind_capture b
JOIN
    v$sql_plan p ON b.sql_id = p.sql_id AND b.child_number = p.child_number
JOIN
    all_arguments a ON p.object_name = a.object_name
                   AND p.object_owner = a.owner
                   AND p.procedure_name = a.procedure_name
                   AND a.position = b.position
WHERE
    b.sql_id = '&target_sql_id'
    AND a.object_name = 'TEST_PKG'
    AND a.procedure_name = 'GET_BILLS';

2. 解决SELECT子句参数捕获为NULL的问题 #

Oracle默认仅捕获影响执行计划的绑定变量（如WHERE/HAVING子句中的参数），SELECT子句中的p_sprach不会被自动捕获。可通过以下方式强制捕获：

• 启用会话级SQL追踪：

-- 轻量捕获所有绑定变量
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

执行存储过程后，生成的trace文件会包含所有绑定变量值，包括p_sprach。

• 修改绑定捕获目标参数：

ALTER SESSION SET cursor_bind_capture_destination = 'MEMORY_AND_DISK';

该参数会让Oracle捕获更多非过滤条件的绑定变量。

3. 无需修改代码捕获Peeked Binds #

Peeked Binds是硬解析时Oracle用于生成执行计划的绑定值，可通过以下方法持久化：

方法一：从AWR历史数据获取 #

AWR会自动保存历史SQL绑定信息，执行查询提取peeked值：

SELECT
    sql_id,
    child_number,
    name,
    value_string,
    is_peeked
FROM
    dba_hist_sqlbind
WHERE
    sql_id = '&target_sql_id'
    AND is_peeked = 'YES';

方法二：创建触发器监控V$SQL_BIND_CAPTURE #

通过触发器将peeked绑定值插入自定义表（需SYSDBA权限）：

-- 创建存储表
CREATE TABLE captured_peeked_binds (
    sql_id VARCHAR2(13),
    child_number NUMBER,
    bind_name VARCHAR2(30),
    bind_value VARCHAR2(4000),
    is_peeked VARCHAR2(3),
    capture_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 创建触发器
CREATE OR REPLACE TRIGGER capture_peeked_binds_trg
AFTER INSERT ON v$sql_bind_capture
FOR EACH ROW
WHEN (new.is_peeked = 'YES')
BEGIN
    INSERT INTO captured_peeked_binds (
        sql_id, child_number, bind_name, bind_value, is_peeked
    ) VALUES (
        :new.sql_id, :new.child_number, :new.name, :new.value_string, :new.is_peeked
    );
END;
/

4. 多游标场景下对比原始SELECT与V$SQL语句 #

方法一：获取原始SQL文本对比 #

使用DBMS_SQLDIAG获取原始SQL，与v$sql中的文本对比：

SELECT
    sql_id,
    child_number,
    DBMS_SQLDIAG.GET_SQL_TEXT(sql_id) AS original_sql,
    sql_text AS v$sql_sql_text
FROM
    v$sql
WHERE
    sql_id IN ('&sql_id_1', '&sql_id_2');

方法二：通过FORCE_MATCHING_SIGNATURE匹配语义相同的SQL #

Oracle会为语义一致的SQL生成相同的force_matching_signature，可通过该字段判断是否为同一原始语句的不同游标：

SELECT
    sql_id,
    child_number,
    sql_text,
    force_matching_signature,
    sql_hash_value
FROM
    v$sql
WHERE
    force_matching_signature = (SELECT force_matching_signature FROM v$sql WHERE sql_id = '&base_sql_id');

方法三：通过SQL Profile跟踪原始语句 #

若启用了SQL Profile，可通过dba_sql_profiles获取原始SQL并对比：

SELECT
    p.name AS profile_name,
    p.sql_text AS original_sql,
    s.sql_text AS v$sql_sql_text
FROM
    dba_sql_profiles p
JOIN
    v$sql s ON p.signature = s.signature
WHERE
    p.name = '&target_profile_name';

内容的提问来源于stack exchange，提问作者Malath Enaim