编程学习资讯网

ORACLE获取SQL绑定变量值的方法总结--编程学习网

发布时间:2017-09-28 09:46:43   来源:本站编辑   浏览次数:

方法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

 

clip_image001

 

 

 

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

 

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

 

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

受隐含参数_cursor_bind_capture_interval影响,默认需要间隔15900秒)分钟才会做一次

 

 

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

 

clip_image002

 

 

 

 

如上测试所示,这个获取绑定变量值的方法有一定的缺陷性,有可能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'
/

 

 

 

 

 

方法3v$sql_bind_capture

 

 

使用 V$SQL_BIND_CAPTURE获取绑定变量的值,也有一些限制:

 

1、如果STATISTICS_LEVEL设置成BASIC,那绑定变量的捕捉就会关闭(Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

2、默认是900秒捕捉一次绑定变量值,由_cursor_bind_capture_interval参数控制。

3V$SQL_BIND_CAPTURE视图中记录的绑定变量只对WHERE条件后面的绑定进行捕获,这点需要使用的时候注意。

    对于DML操作,V$SQL_BIND_CAPTURE无法获取绑定变量的值。

 

 

SQL> COL NAME FOR A12;
SQL> COL DATATYPE_STRING FOR A24;
SQL> COL VALUE_STRING FOR A32;
SQL> SELECT NAME, 
  2         DATATYPE_STRING, 
  3         VALUE_STRING, 
  4         MAX_LENGTH, 
  5         LAST_CAPTURED 
  6  FROM   V$SQL_BIND_CAPTURE 
  7  WHERE  SQL_ID = '1t2r2p48W4P0g'; 
 
NAME         DATATYPE_STRING          VALUE_STRING                     MAX_LENGTH LAST_CAPT
------------ ------------------------ -------------------------------- ---------- ---------
:NAME        NVARCHAR2(128)           KD                                    128 04-SEP-17
 
SQL> 

 

 

v$sql_bind_capture视图,可以查看绑定变量,但是这个视图不太给力,只能捕获最后一次记录的绑定变量值。而且两次捕获的间隔也是受隐含参数_cursor_bind_capture_interval控制。默认是900秒后才会重新开始捕获。在900内,绑定变量值的改变不会反应在这个视图中。这个跟v$sql获取变量值是一样的。

 

 

SQL> EXEC :NAME :='kerry';

 

PL/SQL procedure successfully completed.

 

SQL>/

 

        ID NAME

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

      1000 kerry

 

SQL>

 

 

如上所示,我给变量赋予新的值,然后重新执行一次,你执行上面SQL,发现绑定变量的值为kerry了,之前的值KD就无法找到了。所以这个也是这个视图的一个局限性。(注意,实验结果结果也有可能是KD,这个取决于实验的时间间隔与隐含参数_cursor_bind_capture_interval的值有关系)

 

 

clip_image003

 

 

This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).

 

 

 

--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
 
SELECT
  sql_id,
  t.sql_text sql_text,  
  b.name bind_name,
  b.value_string bind_value
FROM
  v$sql t
JOIN
  v$sql_bind_capture b  using (sql_id)
WHERE
  b.value_string is not null
AND
  sql_id='&sqlid'
/
 
 
SELECT
  b.sql_id,
  t.sql_text sql_text,  
  b.name bind_name,
  b.value_string bind_value
FROM
  v$sql t
JOIN
  v$sql_bind_capture b  on t.hash_value = b.hash_value and t.child_address = b.child_address
WHERE
  b.value_string is not null
AND
  b.sql_id='&sqlid'
/

 

 

 

方法4:查询视图DBA_HIST_SQLBIND.

 

 

DBA_HIST_SQLBIND是视图V$SQL_BIND_CAPTURE历史快照所以从视图DBA_HIST_SQLBIND能查到多个绑定变量的值。但是这里依然会遇到一个问题,就是有可能历史快照没有被捕获到DBA_HIST_SQLBIND下。如下测试所示:

 

 

 

SELECT SNAP_ID, 
       NAME, 
       POSITION, 
       VALUE_STRING, 
       LAST_CAPTURED, 
       WAS_CAPTURED 
FROM   DBA_HIST_SQLBIND 
WHERE  SQL_ID = '&SQL_ID' 
       AND SNAP_ID = &SNAP_ID; 
 
 
 
 
SELECT SNAP_ID, 
       NAME, 
       POSITION, 
       VALUE_STRING, 
       LAST_CAPTURED, 
       WAS_CAPTURED 
FROM   DBA_HIST_SQLBIND 
WHERE  SQL_ID = '&SQL_ID';

 

 

 

SQL> SELECT SNAP_ID, 
  2         NAME, 
  3         POSITION, 
  4         VALUE_STRING, 
  5         LAST_CAPTURED, 
  6         WAS_CAPTURED 
  7  FROM   DBA_HIST_SQLBIND 
  8  WHERE  SQL_ID = '&SQL_ID';
Enter value for sql_id: 1t2r2p48w4p0g
old   8: WHERE  SQL_ID = '&SQL_ID'
new   8: WHERE  SQL_ID = '1t2r2p48w4p0g'
 
no rows selected
 
SQL> exec dbms_workload_repository.create_snapshot();
 
PL/SQL procedure successfully completed.
 
SQL> SELECT SNAP_ID, 
  2         NAME, 
  3         POSITION, 
  4         VALUE_STRING, 
  5         LAST_CAPTURED, 
  6         WAS_CAPTURED 
FROM   DBA_HIST_SQLBIND 
  7    8  WHERE  SQL_ID = '&SQL_ID';
Enter value for sql_id: 1t2r2p48w4p0g
old   8: WHERE  SQL_ID = '&SQL_ID'
new   8: WHERE  SQL_ID = '1t2r2p48w4p0g'
 
no rows selected
 
SQL> 

 

 

clip_image004

 

 

 

 

方法5dbms_xplan.display_cursor

 

 

 sql_id       指定位于库缓存执行计划中SQL语句的父游标。默认值为null。当使用默认值时当前会话的最后一条

                SQL语句的执行计划将被返回。 可以通过查询V$SQL V$SQLAREASQL_ID列来获得SQL语句的SQL_ID

 

child_number  指定父游标下子游标的序号。即指定被返回执行计划的SQL语句的子游标。默认值为0。如果为null

               则sql_id所指父游标下所有子游标的执行计划都将被返回。

      

  format       控制SQL语句执行计划的输出部分,即哪些可以显示哪些不显示。

 

 

select * from table(dbms_xplan.display_cursor('1t2r2p48w4p0g', 0, 'ADVANCED'));
 
 
-------------------------------------
SELECT * FROM TEST WHERE NAME=:NAME
 
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    31 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / TEST@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :NAME (VARCHAR2(30), CSID=873): 'KKK'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("NAME"=:NAME)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "TEST"."ID"[NUMBER,22], "NAME"[VARCHAR2,32]

 

 

方法610046事件捕获绑定变量

 

 

alter session set events '10046 trace name context forever, level 4'; --level=4 表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量。

 

实验在此略过,其实ORACLE中seq$表更新频繁的分析案例中已经展示如何使用10046事件捕获绑定变量的值。另外v$sql,v$sql_bind_capturedba_hist_sqlbind只能捕获查询SQL(确切的说,只对WHERE条件后面的绑定变量进行捕获)的绑定变量,但10046也能捕获DMLSQL的值

 

 

 

 

 

最后如果需要可以通过alter system set "_cursor_bind_capture_interval"=10; 修改绑定变量捕获的时间间隔。

编程学习网 http://www.javalearns.cn

关注微信号:javalearns   随时随地学Java

或扫一扫

随时随地学Java