OracleでSQLで指定されたバインド変数の値を確認する
バインド変数が用いられたSQLでは、v$sql_bind_captureで指定したバインド変数の値を確認することができます。
実際にSQLを実行し、バインド変数の値を確認してみます。
まずは、以下のようにバインド変数を使用してSQLを実行します。
※TBL31は事前に作成したものです。
variable v_id number
exec :v_id := 1
select count(*) from tbl31 where id = :v_id;
実行したSQLのSQL IDをv$sqlから確認し、そのSQL IDとv$sql_bind_captureからバインド変数の値を確認します。
-- SQL IDを確認する。
select sql_id,sql_text from v$sql where sql_text like 'select count(*) from tbl31 where id%';
column name format a20
column value_string format a20
set linesize 150
select name, child_number, to_char(last_captured, 'YYYY-MM-DD HH:MI:SS'), value_string from v$sql_bind_capture where sql_id = '5axk7r4hskv8w';
NAME CHILD_NUMBER TO_CHAR(LAST_CAPTUR VALUE_STRING
-------------------- ------------ ------------------- --------------------
:V_ID 0 2021-01-14 06:16:38 1
NAMEにバインド変数名（V_ID）、VALUE_STRINGにバインド変数の値（"1"）が出力されています。
なお、v$sqlからSQL IDを確認していますが、18cからはsqlplus上で以下のコマンドでSQLを実行するとSQL IDが出力されます。
SQL> set feedback on sql_id
SQL> select count(*) from tbl31 where id = :v_id;
COUNT(*)
----------
1
1 row selected.
SQL_ID: 5axk7r4hskv8w
次にバインド変数の値を"2"に変更し、再度実行してみます。
exec :v_id := 2
select count(*) from tbl31 where id = :v_id;
select name, child_number, to_char(last_captured, 'YYYY-MM-DD HH:MI:SS'), value_string from v$sql_bind_capture where sql_id = '5axk7r4hskv8w';
NAME CHILD_NUMBER TO_CHAR(LAST_CAPTUR VALUE_STRING
-------------------- ------------ ------------------- --------------------
:V_ID 0 2021-01-14 06:16:38 1
バインド変数を"2"に変更しましたが、VALUE_STRINGは"1"のままです。
理由を調べるためにV$SQL_BIND_CAPTUREを確認すると、以下のように記載されていました。
バインド値が取得された日付。バインド値は、SQL文の実行時に取得される。オーバーヘッドを制限するには、指定したカーソルに対して最大15分間隔でバインドを取得する。
そのため、時間をおいて実行したところ、VALUE_STRINGの値が反映されるようになりました。
SQL> select count(*) from tbl31 where id = :v_id;
COUNT(*)
----------
2
1 row selected.
SQL_ID: 5axk7r4hskv8w
SQL> select name, child_number, to_char(last_captured, 'YYYY-MM-DD HH:MI:SS'), value_string from v$sql_bind_capture where sql_id = '5axk7r4hskv8w';
NAME CHILD_NUMBER TO_CHAR(LAST_CAPTUR VALUE_STRING
-------------------- ------------ ------------------- --------------------
:V_ID 0 2021-01-14 06:33:17 2
なお、バインド変数の値はdba_hist_snapshotに履歴が保存されます。
select to_char( sn.end_interval_time, 'YYYY-MM-DD HH:MI:SS') as interval_time, sb.sql_id, sb.name, to_char(sb.last_captured, 'YYYY-MM-DD HH:MI:SS') as last_captured, sb.value_string from dba_hist_sqlbind sb, dba_hist_snapshot sn where sb.sql_id = '5axk7r4hskv8w'
and sn.snap_id = sb.snap_id order by 1,2;
INTERVAL_TIME SQL_ID NAME LAST_CAPTURED VALUE_STRING
------------------- ------------- -------------------- ------------------- --------------------
2021-01-14 08:00:09 5axk7r4hskv8w :V_ID 2021-01-14 07:55:07 3
2021-01-14 09:00:22 5axk7r4hskv8w :V_ID 2021-01-14 07:55:07 3
2021-01-14 10:00:34 5axk7r4hskv8w :V_ID 2021-01-14 09:35:36 5
スナップショットの時間を見ると、1時間に1回スナップショットが取らているようです。
本当はVALUE_STRINGが"1", "2"も出力されると思っていたのですが、なぜか出力されていません。その後に値を変更したら出力されていました（原因不明）。
おまけメモ
timstamp型の場合、バインド変数の値がうまく出力されませんでした（原因不明）。
sqlplusからだとchar型で扱っていることが原因かも。（暇なときに調べる）
variable v_created char(64)
exec :v_created := "2021-01-05";
select /* T1 */count(*) from tbl31 where created = to_timestamp(:v_created, 'YYYY-MM-DD');
column name format a20
column datatype_string format a20
column value_string format a20
column value_anydata format a20
set linesize 150
select name, child_number, datatype_string, to_char(last_captured, 'YYYY-MM-DD HH:MI:SS') as last_captured, value_string, value_anydata from v$sql_bind_capture where sql_id = '38ubn0fhwxh27';
NAME CHILD_NUMBER DATATYPE_STRING LAST_CAPTURED VALUE_STRING VALUE_ANYDATA()
-------------------- ------------ -------------------- ------------------- -------------------- --------------------
:V_CREATED 0 CHAR(2000) 2021-01-17 05:24:42 NULL
VALUE_STRINGが"NULL"になりました。