ORACLE获取SQL绑定变量值的方法总结

本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法，在SQL优化调优过程中，经常会用到这方面的知识点。在此梳理、总结一下，方面日后查找、翻阅。

方法1：查询V$SQL

V$SQL视图中的BIND_DATA字段用来存储绑定变量的值，但是从这个视图查询绑定变量的值，有很大的局限性：

    1： 它的记录频率受_cursor_bind_capture_interval隐含参数控制，默认值为900，表示每900秒记录一次绑定值，也就是说在900内，绑定变量值的改变不会反应在这个视图中。除非你调整隐含参数_cursor_bind_capture_interval

    2： 它记录的仅仅最后一次捕获的绑定变量值。

    3： BIND_DATA数据类型为RAW，需要进行转换。

可以使用下面两种方式来查看绑定变量的值。

COL SQL_ID FOR A14;

COL SQL_TEXT FOR A32;

COL HASH_VALUE FOR 99999999999;

COL BIND_DATA FOR A32;

SELECT SQL_ID          

      ,SQL_TEXT

      ,LITERAL_HASH_VALUE

      ,HASH_VALUE

      ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA

FROM V$SQL

WHERE SQL_TEXT LIKE ‘‘SELECT * FROM TEST%‘;

COL SQL_ID FOR A14;

COL SQL_TEXT FOR A32;

COL HASH_VALUE FOR 99999999999;

COL BIND_DATA FOR A32;

SELECT SQL_ID          

      ,SQL_TEXT

      ,LITERAL_HASH_VALUE

      ,HASH_VALUE

      ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA

FROM V$SQL

WHERE SQL_TEXT LIKE ‘SELECT * FROM TEST%‘;

如下实验所示，我们在一个会话中使用绑定变量的查询SQL语句，然后，我们来尝试获取绑定变量的值，如下所示：

SQL> SHOW USER;

USER is "TEST"

SQL> DESC TEST;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(10)

 NAME                                               VARCHAR2(32)

SQL> 

SQL> VARIABLE NAME NVARCHAR2(32);

SQL> EXEC :NAME :=‘KKKK‘;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST WHERE NAME=:NAME;

no rows selected

SQL> 

SQL>SHOW USER;

USER is "SYS"

SQL> COL SQL_ID FOR A14;

SQL> COL SQL_TEXT FOR A32;

SQL> COL HASH_VALUE FOR 99999999999;

SQL> COL BIND_DATA FOR A32;

SQL> SELECT SQL_ID          

  2        ,SQL_TEXT

  3        ,LITERAL_HASH_VALUE

  4        ,HASH_VALUE

  5        ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA

  6  FROM V$SQL

  7  WHERE SQL_TEXT LIKE ‘SELECT * FROM TEST%‘;

SQL_ID         SQL_TEXT                         LITERAL_HASH_VALUE   HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO

-------------- -------------------------------- ------------------ ------------ --------------------------------

0r7m5jyz9ng09  SELECT * FROM TEST WHERE NAME=:N                  0   3197778953 SQL_BIND_SET(SQL_BIND(NULL, 1, N

               AME                                                              ULL, 1, ‘NVARCHAR2(128)‘, 2000,

                                                                                NULL, NULL, 128, ‘04-SEP-17‘, ‘K

                                                                                KKK‘, ANYDATA()))

SQL> COL SQL_ID FOR A14;

SQL> COL SQL_TEXT FOR A32;

SQL> COL HASH_VALUE FOR 99999999999;

SQL> COL BIND_DATA FOR A32;

SQL> SELECT SQL_ID          

  2        ,SQL_TEXT

  3        ,LITERAL_HASH_VALUE

  4        ,HASH_VALUE

  5        ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA

  6  FROM V$SQL

  7  WHERE SQL_TEXT LIKE ‘SELECT * FROM TEST%‘;

SQL_ID         SQL_TEXT                         LITERAL_HASH_VALUE   HASH_VALUE BIND_DATA

-------------- -------------------------------- ------------------ ------------ --------------------------------

0r7m5jyz9ng09  SELECT * FROM TEST WHERE NAME=:N                  0   3197778953 KKKK

如果此时你给变量NAME赋值为kerry，然后你使用上面SQL语句查询，你会发现绑定变量的值依然为"KKKK",这个是因为绑定变量何时被捕获是有一定规律的：

1 含有绑定变量的sql语句被硬解析时

2 当含有绑定变量的sql语句以软解析或者软软解析方式重复执行时，该SQL语句中的绑定变量的具体输入值也可能被ORACLE捕获，只不过默认情况下这种捕获操作

受隐含参数_cursor_bind_capture_interval影响，默认需要间隔15（900秒）分钟才会做一次

SQL> exec :NAME :=‘kerry‘;

PL/SQL procedure successfully completed.

SQL> /

        ID NAME

---------- --------------------------------

      1000 kerry

SQL>

方法2：查询wrh$_sqlstat

V$SQL中有BIND_DATA字段，当SQL被解析时，就会放到BIND_DATA字段中，最终会被存入wrh$_sqlstat。关于wrh$_sqlstat的介绍如下所示：

wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.

wrh$_sqlstat中存储是v$sql的执行统计信息的快照的历史记录，那么从这里可以查询到一些历史绑定变量的值，但是也有可能v$sql的快照信息没有被捕获到（如满足什么条件才会被捕获呢？）。如下截图所示

SQL> select dbms_sqltune.extract_bind(bind_data, 1).value_string

  2  from wrh$_sqlstat

  3  where sql_id=‘0r7m5jyz9ng09‘;

no rows selected

如上测试所示，这个获取绑定变量值的方法有一定的缺陷性，有可能V$SQL快照信息没有被捕获到，导致wrh$_sqlstat

里面查不到对应的信息。

注意，如果有1个的绑定值，可以使用如下查询

select dbms_sqltune.extract_bind(bind_data, 1).value_string   

from wrh$_sqlstat

where sql_id = ‘1t2r2p48w4p0g‘

如果有2个绑定值，可以使用如下查询

select dbms_sqltune.extract_bind(bind_data, 1).value_string||

‘

‘--‘||dbms_sqltune.extract_bind(bind_data, 2).value_string

  from wrh$_sqlstat

 where sql_id = ‘1t2r2p48w4p0g‘

如果有多个绑定变量，使用类似下面SQL

select dbms_sqltune.extract_bind(bind_data, 1).value_string

     ||‘-‘|| dbms_sqltune.extract_bind(bind_data, 2).value_string 

     ||‘-‘|| dbms_sqltune.extract_bind(bind_data, 3).value_string 

     ||‘-‘|| dbms_sqltune.extract_bind(bind_data, 4).value_string 

     ||‘-‘|| dbms_sqltune.extract_bind(bind_data, 5).value_string 

     ||‘-‘|| dbms_sqltune.extract_bind(bind_data, 6).value_string

from wrh$_sqlstat

where sql_id = ‘1t2r2p48w4p0g‘

/

方法3：v$sql_bind_capture

• < 上一篇

  SQL 数据库高CPU占用语句排查

• 下一篇 >

  SQL之case when then用法(用于分类统计)