【DB笔试面试584】在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？

小麦苗DBA宝典

发布于 2019-09-29 15:53:45

3K0

发布于 2019-09-29 15:53:45

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在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