【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程序員面試筆試寶典》，作者：李華榮。
