本文總結一下ORACLE資料庫中如何獲取SQL繫結變數值的方法，在SQL最佳化調優過程中，經常會用到這方面的知識點。在此梳理、總結一下，方面日後查詢、翻閱。
方法1：查詢V$SQL
V$SQL檢視中的BIND_DATA欄位用來儲存繫結變數的值，但是從這個檢視查詢繫結變數的值，有很大的侷限性：
1： 它的記錄頻率受_cursor_bind_capture_interval隱含引數控制，預設值為900，表示每900秒記錄一次繫結值，也就是說在900內，繫結變數值的改變不會反應在這個檢視中。除非你調整隱含引數_cursor_bind_capture_interval
2： 它記錄的僅僅最後一次捕獲的繫結變數值。
3： BIND_DATA資料型別為RAW，需要進行轉換。
可以使用下面兩種方式來檢視繫結變數的值。
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%';
如下實驗所示，我們在一個會話中使用繫結變數的查詢SQL語句，然後，我們來嘗試獲取繫結變數的值，如下所示：
SQL> SHOW USER;
USER is "TEST"
SQL> DESC TEST;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
NAME VARCHAR2(32)
SQL>
SQL> VARIABLE NAME NVARCHAR2(32);
SQL> EXEC :NAME :='KKKK';
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST WHERE NAME=:NAME;
no rows selected
SQL>
SQL>SHOW USER;
USER is "SYS"
SQL> COL SQL_ID FOR A14;
SQL> COL SQL_TEXT FOR A32;
SQL> COL HASH_VALUE FOR 99999999999;
SQL> COL BIND_DATA FOR A32;
SQL> SELECT SQL_ID
2 ,SQL_TEXT
3 ,LITERAL_HASH_VALUE
4 ,HASH_VALUE
5 ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA
6 FROM V$SQL
7 WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO
-------------- -------------------------------- ------------------ ------------ --------------------------------
0r7m5jyz9ng09 SELECT * FROM TEST WHERE NAME=:N 0 3197778953 SQL_BIND_SET(SQL_BIND(NULL, 1, N
AME ULL, 1, 'NVARCHAR2(128)', 2000,
NULL, NULL, 128, '04-SEP-17', 'K
KKK', ANYDATA()))
SQL> COL SQL_ID FOR A14;
SQL> COL SQL_TEXT FOR A32;
SQL> COL HASH_VALUE FOR 99999999999;
SQL> COL BIND_DATA FOR A32;
SQL> SELECT SQL_ID
2 ,SQL_TEXT
3 ,LITERAL_HASH_VALUE
4 ,HASH_VALUE
5 ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA
6 FROM V$SQL
7 WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA
-------------- -------------------------------- ------------------ ------------ --------------------------------
0r7m5jyz9ng09 SELECT * FROM TEST WHERE NAME=:N 0 3197778953 KKKK
如果此時你給變數NAME賦值為kerry，然後你使用上面SQL語句查詢，你會發現繫結變數的值依然為"KKKK",這個是因為繫結變數何時被捕獲是有一定規律的：
1 含有繫結變數的sql語句被硬解析時
2 當含有繫結變數的sql語句以軟解析或者軟軟解析方式重複執行時，該SQL語句中的繫結變數的具體輸入值也可能被ORACLE捕獲，只不過預設情況下這種捕獲操作
受隱含引數_cursor_bind_capture_interval影響，預設需要間隔15（900秒）分鐘才會做一次
SQL> exec :NAME :='kerry';
PL/SQL procedure successfully completed.
SQL> /
ID NAME
---------- --------------------------------
1000 kerry
SQL>
方法2：查詢wrh$_sqlstat
V$SQL中有BIND_DATA欄位，當SQL被解析時，就會放到BIND_DATA欄位中，最終會被存入wrh$_sqlstat。關於wrh$_sqlstat的介紹如下所示：
wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.
wrh$_sqlstat中儲存是v$sql的執行統計資訊的快照的歷史記錄，那麼從這裡可以查詢到一些歷史繫結變數的值，但是也有可能v$sql的快照資訊沒有被捕獲到（如滿足什麼條件才會被捕獲呢？）。如下截圖所示
SQL> select dbms_sqltune.extract_bind(bind_data, 1).value_string
2 from wrh$_sqlstat
3 where sql_id='0r7m5jyz9ng09';
no rows selected
如上測試所示，這個獲取繫結變數值的方法有一定的缺陷性，有可能V$SQL快照資訊沒有被捕獲到，導致wrh$_sqlstat
裡面查不到對應的資訊。
注意，如果有1個的繫結值，可以使用如下查詢
select dbms_sqltune.extract_bind(bind_data, 1).value_string
from wrh$_sqlstat
where sql_id = '1t2r2p48w4p0g'
如果有2個繫結值，可以使用如下查詢
select dbms_sqltune.extract_bind(bind_data, 1).value_string||
'
'--'||dbms_sqltune.extract_bind(bind_data, 2).value_string
from wrh$_sqlstat
where sql_id = '1t2r2p48w4p0g'
如果有多個繫結變數，使用類似下面SQL
select dbms_sqltune.extract_bind(bind_data, 1).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 2).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 3).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 4).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 5).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 6).value_string
from wrh$_sqlstat
where sql_id = '1t2r2p48w4p0g'
/
方法3：v$sql_bind_capture
使用 V$SQL_BIND_CAPTURE獲取繫結變數的值，也有一些限制：
1、如果STATISTICS_LEVEL設定成BASIC，那繫結變數的捕捉就會關閉（Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.）
2、預設是900秒捕捉一次繫結變數值，由_cursor_bind_capture_interval引數控制。
3、V$SQL_BIND_CAPTURE檢視中記錄的繫結變數只對WHERE條件後面的繫結進行捕獲，這點需要使用的時候注意。
對於DML操作，V$SQL_BIND_CAPTURE無法獲取繫結變數的值。
SQL> COL NAME FOR A12;
SQL> COL DATATYPE_STRING FOR A24;
SQL> COL VALUE_STRING FOR A32;
SQL> SELECT NAME,
2 DATATYPE_STRING,
3 VALUE_STRING,
4 MAX_LENGTH,
5 LAST_CAPTURED
6 FROM V$SQL_BIND_CAPTURE
7 WHERE SQL_ID = '1t2r2p48W4P0g';
NAME DATATYPE_STRING VALUE_STRING MAX_LENGTH LAST_CAPT
------------ ------------------------ -------------------------------- ---------- ---------
:NAME NVARCHAR2(128) KD 128 04-SEP-17
SQL>
v$sql_bind_capture檢視，可以檢視繫結變數，但是這個檢視不太給力，只能捕獲最後一次記錄的繫結變數值。而且兩次捕獲的間隔也是受隱含引數_cursor_bind_capture_interval控制。預設是900秒後才會重新開始捕獲。在900內，繫結變數值的改變不會反應在這個檢視中。這個跟v$sql獲取變數值是一樣的。
SQL> EXEC :NAME :='kerry';
PL/SQL procedure successfully completed.
SQL>/
ID NAME
---------- --------------------------------
1000 kerry
SQL>
如上所示，我給變數賦予新的值，然後重新執行一次，你執行上面SQL，發現繫結變數的值為kerry了，之前的值KD就無法找到了。所以這個也是這個檢視的一個侷限性。(注意，實驗結果結果也有可能是KD，這個取決於實驗的時間間隔與隱含引數_cursor_bind_capture_interval的值有關係)
This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT
sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id='&sqlid'
/
SELECT
b.sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b on t.hash_value = b.hash_value and t.child_address = b.child_address
WHERE
b.value_string is not null
AND
b.sql_id='&sqlid'
/
方法4：查詢檢視DBA_HIST_SQLBIND.
DBA_HIST_SQLBIND是檢視V$SQL_BIND_CAPTURE歷史快照. 所以從檢視DBA_HIST_SQLBIND能查到多個繫結變數的值。但是這裡依然會遇到一個問題，就是有可能歷史快照沒有被捕獲到DBA_HIST_SQLBIND下。如下測試所示：
SELECT SNAP_ID,
NAME,
POSITION,
VALUE_STRING,
LAST_CAPTURED,
WAS_CAPTURED
FROM DBA_HIST_SQLBIND
WHERE SQL_ID = '&SQL_ID'
AND SNAP_ID = &SNAP_ID;
SELECT SNAP_ID,
NAME,
POSITION,
VALUE_STRING,
LAST_CAPTURED,
WAS_CAPTURED
FROM DBA_HIST_SQLBIND
WHERE SQL_ID = '&SQL_ID';
SQL> SELECT SNAP_ID,
2 NAME,
3 POSITION,
4 VALUE_STRING,
5 LAST_CAPTURED,
6 WAS_CAPTURED
7 FROM DBA_HIST_SQLBIND
8 WHERE SQL_ID = '&SQL_ID';
Enter value for sql_id: 1t2r2p48w4p0g
old 8: WHERE SQL_ID = '&SQL_ID'
new 8: WHERE SQL_ID = '1t2r2p48w4p0g'
no rows selected
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SQL> SELECT SNAP_ID,
2 NAME,
3 POSITION,
4 VALUE_STRING,
5 LAST_CAPTURED,
6 WAS_CAPTURED
FROM DBA_HIST_SQLBIND
7 8 WHERE SQL_ID = '&SQL_ID';
Enter value for sql_id: 1t2r2p48w4p0g
old 8: WHERE SQL_ID = '&SQL_ID'
new 8: WHERE SQL_ID = '1t2r2p48w4p0g'
no rows selected
SQL>
方法5：dbms_xplan.display_cursor
sql_id： 指定位於庫快取執行計劃中SQL語句的父遊標。預設值為null。當使用預設值時當前會話的最後一條
SQL語句的執行計劃將被返回。 可以透過查詢V$SQL 或V$SQLAREA的SQL_ID列來獲得SQL語句的SQL_ID。
child_number 指定父遊標下子游標的序號。即指定被返回執行計劃的SQL語句的子游標。預設值為0。如果為null，
則sql_id所指父遊標下所有子游標的執行計劃都將被返回。
format 控制SQL語句執行計劃的輸出部分，即哪些可以顯示哪些不顯示。
select * from table(dbms_xplan.display_cursor('1t2r2p48w4p0g', 0, 'ADVANCED'));
-------------------------------------
SELECT * FROM TEST WHERE NAME=:NAME
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :NAME (VARCHAR2(30), CSID=873): 'KKK'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"=:NAME)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."ID"[NUMBER,22], "NAME"[VARCHAR2,32]
方法6：10046事件捕獲繫結變數
alter session set events '10046 trace name context forever, level 4'; --level=4 表示啟用SQL_TRACE並捕捉跟蹤檔案中的繫結變數。
實驗在此略過，其實ORACLE中seq$表更新頻繁的分析案例中已經展示如何使用10046事件捕獲繫結變數的值。另外v$sql,v$sql_bind_capture、dba_hist_sqlbind只能捕獲查詢SQL(確切的說，只對WHERE條件後面的繫結變數進行捕獲)的繫結變數，但10046也能捕獲DML的SQL的值
最後如果需要可以透過alter system set "_cursor_bind_capture_interval"=10; 修改繫結變數捕獲的時間間隔。
