在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？

DB宝 2019-06-26

797

♣题目部分在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？

     
♣答案部分

当Oracle解析和执行含有绑定变量的目标SQL时，如果满足如下两个条件之一，那么该SQL中的绑定变量的具体输入值就会被Oracle捕获：
l 当含有绑定变量的目标SQL以硬解析的方式被执行时。
l 当含有绑定变量的目标SQL以软解析或软软解析的方式重复执行时，Oracle在默认情况下至少得间隔15分钟才会捕获一次。这个15分钟受隐含参数“_CURSOR_BIND_CAPTURE_INTERVAL”控制，默认值为900秒，即15分钟。
 1SYS@orclasm > SET PAGESIZE 9999
 2SYS@orclasm > SET LINE 9999
 3SYS@orclasm > COL NAME FORMAT A40
 4SYS@orclasm > COL KSPPDESC FORMAT A60
 5SYS@orclasm > COL KSPPSTVL FORMAT A20
 6SYS@orclasm > SELECT A.INDX,
 7  2         A.KSPPINM NAME,
 8  3         A.KSPPDESC,
 9  4         B.KSPPSTVL 
10  5  FROM   X$KSPPI  A,
11  6         X$KSPPCV B
12  7  WHERE  A.INDX = B.INDX
13  8  AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%');
14Enter value for parameter: _CURSOR_BIND_CAPTURE_INTERVAL
15old   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%')
16new   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%_CURSOR_BIND_CAPTURE_INTERVAL%')
17
18      INDX NAME                                     KSPPDESC                                                     KSPPSTVL
19---------- ---------------------------------------- ------------------------------------------------------------ --------------------
20      2140 _cursor_bind_capture_interval            interval (in seconds) between two bind capture for a cursor  900


需要注意的是，Oracle只会捕获那些位于目标SQL的WHERE条件中的绑定变量的具体输入值，而对于那些使用了绑定变量的INSERT语句，不管该INSERT语句是否是以硬解析的方式执行，Oracle始终不会捕获INSERT语句的VALUES子句中对应绑定变量的具体输入值。
查询视图V$SQL_BIND_CAPTURE或V$SQL可以得到已执行目标SQL中绑定变量的具体输入值。如果V$SQL_BIND_CAPTURE中查不到，那么有可能对应的Shared Cursor已经从Shared Pool中被清除了，这时候可以尝试从AWR相关的数据字典表DBA_HIST_SQLSTAT或DBA_HIST_SQLBIND中查询。另外，也可以通过DBMS_XPLAN.DISPLAY_CURSOR和10046来获取绑定变量的值。
查询SQL语句如下所示：
 1COL SQL_ID FOR A14;
 2COL SQL_TEXT FOR A32;
 3COL HASH_VALUE FOR 99999999999;
 4COL BIND_DATA FOR A32;
 5SELECT SQL_ID          
 6      ,SQL_TEXT
 7      ,LITERAL_HASH_VALUE
 8      ,HASH_VALUE
 9      ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA
10FROM V$SQL
11WHERE SQL_TEXT LIKE ''SELECT * FROM TEST%';
12
13
14COL SQL_ID FOR A14;
15COL SQL_TEXT FOR A32;
16COL HASH_VALUE FOR 99999999999;
17COL BIND_DATA FOR A32;
18SELECT SQL_ID          
19      ,SQL_TEXT
20      ,LITERAL_HASH_VALUE
21      ,HASH_VALUE
22      ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA
23FROM V$SQL
24WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';
25
26SELECT D.SQL_ID,
27       D.CHILD_NUMBER,
28       D.CHILD_ADDRESS,
29       D.NAME,
30       D.POSITION,
31       D.DATATYPE,
32       D.DATATYPE_STRING,
33       D.MAX_LENGTH,
34       D.WAS_CAPTURED,
35       D.LAST_CAPTURED,
36       D.VALUE_STRING
37  FROM V$SQL_BIND_CAPTURE D
38 WHERE D.SQL_ID = '01g03pruhphqc'
39 ORDER BY D.CHILD_NUMBER, D.POSITION;
40
41SELECT D.SQL_ID,
42        D.NAME,
43        D.POSITION,
44        D.DATATYPE,
45        D.DATATYPE_STRING,
46        D.MAX_LENGTH,
47        D.WAS_CAPTURED,
48        D.LAST_CAPTURED,
49        D.VALUE_STRING
50  FROM DBA_HIST_SQLBIND D;
51
52SELECT D.SNAP_ID,
53       DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA, 1).VALUE_STRING BIND1,
54       DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA, 1).VALUE_STRING BIND2
55  FROM DBA_HIST_SQLSTAT D
56 WHERE D.SQL_ID = '01g03pruhphqc';
57
58SELECT D.SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,D.NAME,D.POSITION,D.DATATYPE, D.DATATYPE_STRING,D.MAX_LENGTH,D.WAS_CAPTURED,D.LAST_CAPTURED,D.VALUE_STRING FROM V$SQL_BIND_CAPTURE D WHERE D.SQL_ID = 'aug0d49nzbgtq' ORDER BY D.CHILD_NUMBER,D.POSITION;
59
60SELECT D.SNAP_ID,DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA,1).VALUE_STRING BIND1,DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA,1).VALUE_STRING BIND2 FROM DBA_HIST_SQLSTAT D WHERE D.SQL_ID = 'aug0d49nzbgtq';
61
62SELECT * FROM DBA_HIST_SQLBIND  D WHERE D.SQL_ID = 'aug0d49nzbgtq';
63
64SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1t2r2p48w4p0g', 0, 'ADVANCED'));
65
66ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; --LEVEL=4 表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量。
67

测试示例如下所示：
 1CREATE TABLE T_BG_20170610_LHR(N NUMBER(10),V VARCHAR2(3000));
 2--SQL_TEXT1: 硬解析
 3DECLARE
 4  N NUMBER(10) :=1;   --分配22字节的内存空间
 5  V VARCHAR2(32) :='XIAOMAIMIAO1';   --分配32字节的内存空间
 6BEGIN
 7  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
 8  COMMIT;
 9END;
10/
11--SQL_TEXT2: 硬解析
12DECLARE
13  N NUMBER(10) :=2;  --分配22字节的内存空间
14  V VARCHAR2(33) :='XIAOMAIMIAO2'; --分配128字节的内存空间
15BEGIN
16  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
17  COMMIT;
18END;
19/
20
21--SQL_TEXT3:  硬解析
22DECLARE
23  N NUMBER(10) :=3;  --分配22字节的内存空间
24  V VARCHAR2(129) :='XIAOMAIMIAO3'; --分配2000字节的内存空间
25BEGIN
26  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
27  COMMIT;
28END;
29/
30
31--SQL_TEXT4: 软解析
32DECLARE
33  N NUMBER(10) :=4;  --分配22字节的内存空间
34  V VARCHAR2(2001) :='XIAOMAIMIAO4';  --分配2000字节的内存空间
35BEGIN
36  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
37  COMMIT;
38END;
39/
40--SQL_TEXT5: 软解析
41DECLARE
42  N NUMBER(10) :=5;  --分配22字节的内存空间
43  V VARCHAR2(32767) :='XIAOMAIMIAO5';  --分配2000字节的内存空间
44BEGIN
45  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
46  COMMIT;
47END;
48/
49
50--SQL_TEXT6: 硬解析
51DECLARE
52  N NUMBER(10) :=6;  --分配22字节的内存空间
53  V VARCHAR2(32767) :=RPAD('XIAOMAIMIAO6',2002,'8');  --字符串长度为2002，分配4000字节的内存空间
54BEGIN
55  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
56  COMMIT;
57END;
58/

查询绑定变量的输入值：
 1LHR@orclasm > COL NAME FORMAT A6
 2LHR@orclasm > COL VALUE_STRING FORMAT A15
 3LHR@orclasm > SELECT D.SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,D.NAME,D.POSITION,D.DATATYPE, D.DATATYPE_STRING,D.MAX_LENGTH,D.WAS_CAPTURED,D.LAST_CAPTURED,D.VALUE_STRING FROM V$SQL_BIND_CAPTURE D WHERE D.SQL_ID = 'aug0d49nzbgtq' ORDER BY D.CHILD_NUMBER,D.POSITION;
 4
 5SQL_ID        CHILD_NUMBER CHILD_ADDRESS    NAME     POSITION   DATATYPE DATATYPE_STRING                MAX_LENGTH WAS LAST_CAPTURED       VALUE_STRING
 6------------- ------------ ---------------- ------ ---------- ---------- ------------------------------ ---------- --- ------------------- ---------------
 7aug0d49nzbgtq            0 0000000095C56BB0 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 1
 8aug0d49nzbgtq            0 0000000095C56BB0 :V              2          1 VARCHAR2(32)                           32 YES 2017-06-10 11:48:47 XIAOMAIMIAO1
 9aug0d49nzbgtq            1 0000000095C5ECF0 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 2
10aug0d49nzbgtq            1 0000000095C5ECF0 :V              2          1 VARCHAR2(128)                         128 YES 2017-06-10 11:48:47 XIAOMAIMIAO2
11aug0d49nzbgtq            2 0000000095C66750 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 3
12aug0d49nzbgtq            2 0000000095C66750 :V              2          1 VARCHAR2(2000)                       2000 YES 2017-06-10 11:48:47 XIAOMAIMIAO3
13aug0d49nzbgtq            3 0000000095C22880 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:48 6
14aug0d49nzbgtq            3 0000000095C22880 :V              2          1 VARCHAR2(4000)                       4000 NO



本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。