【DB笔试面试584】在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？
- 2019 年 10 月 10 日
- 笔记
♣
题目部分
在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？
♣
答案部分
当Oracle解析和执行含有绑定变量的目标SQL时，如果满足如下两个条件之一，那么该SQL中的绑定变量的具体输入值就会被Oracle捕获：
l 当含有绑定变量的目标SQL以硬解析的方式被执行时。
l 当含有绑定变量的目标SQL以软解析或软软解析的方式重复执行时，Oracle在默认情况下至少得间隔15分钟才会捕获一次。这个15分钟受隐含参数“_CURSOR_BIND_CAPTURE_INTERVAL”控制，默认值为900秒，即15分钟。
SYS@orclasm > SET PAGESIZE 9999 SYS@orclasm > SET LINE 9999 SYS@orclasm > COL NAME FORMAT A40 SYS@orclasm > COL KSPPDESC FORMAT A60 SYS@orclasm > COL KSPPSTVL FORMAT A20 SYS@orclasm > SELECT A.INDX, 2 A.KSPPINM NAME, 3 A.KSPPDESC, 4 B.KSPPSTVL 5 FROM X$KSPPI A, 6 X$KSPPCV B 7 WHERE A.INDX = B.INDX 8 AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%'); Enter value for parameter: _CURSOR_BIND_CAPTURE_INTERVAL old 8: AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%') new 8: AND LOWER(A.KSPPINM) LIKE LOWER('%_CURSOR_BIND_CAPTURE_INTERVAL%') INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- ------------------------------------------------------------ -------------------- 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语句如下所示：
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%'; 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 = '01g03pruhphqc' ORDER BY D.CHILD_NUMBER, D.POSITION; SELECT D.SQL_ID, D.NAME, D.POSITION, D.DATATYPE, D.DATATYPE_STRING, D.MAX_LENGTH, D.WAS_CAPTURED, D.LAST_CAPTURED, D.VALUE_STRING FROM DBA_HIST_SQLBIND D; SELECT 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 = '01g03pruhphqc'; 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; SELECT 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'; SELECT * FROM DBA_HIST_SQLBIND D WHERE D.SQL_ID = 'aug0d49nzbgtq'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1t2r2p48w4p0g', 0, 'ADVANCED')); ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; --LEVEL=4 表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量。
测试示例如下所示：
CREATE TABLE T_BG_20170610_LHR(N NUMBER(10),V VARCHAR2(3000)); --SQL_TEXT1: 硬解析 DECLARE N NUMBER(10) :=1; --分配22字节的内存空间 V VARCHAR2(32) :='XIAOMAIMIAO1'; --分配32字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; / --SQL_TEXT2: 硬解析 DECLARE N NUMBER(10) :=2; --分配22字节的内存空间 V VARCHAR2(33) :='XIAOMAIMIAO2'; --分配128字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; / --SQL_TEXT3: 硬解析 DECLARE N NUMBER(10) :=3; --分配22字节的内存空间 V VARCHAR2(129) :='XIAOMAIMIAO3'; --分配2000字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; / --SQL_TEXT4: 软解析 DECLARE N NUMBER(10) :=4; --分配22字节的内存空间 V VARCHAR2(2001) :='XIAOMAIMIAO4'; --分配2000字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; / --SQL_TEXT5: 软解析 DECLARE N NUMBER(10) :=5; --分配22字节的内存空间 V VARCHAR2(32767) :='XIAOMAIMIAO5'; --分配2000字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; / --SQL_TEXT6: 硬解析 DECLARE N NUMBER(10) :=6; --分配22字节的内存空间 V VARCHAR2(32767) :=RPAD('XIAOMAIMIAO6',2002,'8'); --字符串长度为2002，分配4000字节的内存空间 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V; COMMIT; END; /
查询绑定变量的输入值：
LHR@orclasm > COL NAME FORMAT A6 LHR@orclasm > COL VALUE_STRING FORMAT A15 LHR@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; SQL_ID CHILD_NUMBER CHILD_ADDRESS NAME POSITION DATATYPE DATATYPE_STRING MAX_LENGTH WAS LAST_CAPTURED VALUE_STRING ------------- ------------ ---------------- ------ ---------- ---------- ------------------------------ ---------- --- ------------------- --------------- aug0d49nzbgtq 0 0000000095C56BB0 :N 1 2 NUMBER 22 YES 2017-06-10 11:48:47 1 aug0d49nzbgtq 0 0000000095C56BB0 :V 2 1 VARCHAR2(32) 32 YES 2017-06-10 11:48:47 XIAOMAIMIAO1 aug0d49nzbgtq 1 0000000095C5ECF0 :N 1 2 NUMBER 22 YES 2017-06-10 11:48:47 2 aug0d49nzbgtq 1 0000000095C5ECF0 :V 2 1 VARCHAR2(128) 128 YES 2017-06-10 11:48:47 XIAOMAIMIAO2 aug0d49nzbgtq 2 0000000095C66750 :N 1 2 NUMBER 22 YES 2017-06-10 11:48:47 3 aug0d49nzbgtq 2 0000000095C66750 :V 2 1 VARCHAR2(2000) 2000 YES 2017-06-10 11:48:47 XIAOMAIMIAO3 aug0d49nzbgtq 3 0000000095C22880 :N 1 2 NUMBER 22 YES 2017-06-10 11:48:48 6 aug0d49nzbgtq 3 0000000095C22880 :V 2 1 VARCHAR2(4000) 4000 NO
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
