原 如何得到已执行的目标SQL中的绑定变量的值？
👉 本文共约568个字，系统预计阅读时间或需3分钟。
简介
当Oracle解析和执行含有绑定变量的目标SQL时，如果满足如下两个条件之一，那么该SQL中的绑定变量的具体输入值就会被Oracle捕获：
- 当含有绑定变量的目标SQL以硬解析的方式被执行时。
- 当含有绑定变量的目标SQL以软解析或软软解析的方式重复执行时，Oracle在默认情况下至少得间隔15分钟才会捕获一次。这个15分钟受隐含参数“_CURSOR_BIND_CAPTURE_INTERVAL”控制，默认值为900秒，即15分钟。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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语句如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | 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并捕捉跟踪文件中的绑定变量。 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
