ORACLE中获取绑定变量值的各种方法

 有时候做SQL优化分析，需要获取绑定变量的值，有多种方式可以获取绑定变量值。
1）从v$sql获取
v$sql里的bind_data受_cursor_bind_capture_interval隐含参数控制，默认900s捕获一次，而且只记录{BANNED}{BANNED}最佳佳近一次捕获的值。bind_data是raw类型，需要按照下列脚本解析。

NAME                                     VALUE
---------------------------------------- ----------------------------------------
_cursor_bind_capture_interval            900

解析脚本：
col value_string for a50
col datatype_string a20
set line 200 pagesize 999
select position, value_string,datatype_string
from table(select dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='&sql_id');

dingjun123@ORADB> select position, value_string,datatype_string
  2  from table(select dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='2yqgwatmsyr1p');


  POSITION VALUE_STRING                                       DATATYPE_STRING
---------- -------------------------------------------------- ---------------
         1 123                                                VARCHAR2(4000)
         2 TEST                                               VARCHAR2(4000)
         3 2023-3-15         


2）从v$sql_bind_capture中获取
select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from v$sql_bind_capture t
  where sql_id = '&sql_id';
  
SQL_ID        NAME                                                         DATATYPE_STRING
------------- ------------------------------------------------------------ ------------------------------
BIND_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_CAPTURED
-------------------
2yqgwatmsyr1p :V1                                                          VARCHAR2(4000)
123
2023-05-06 14:54:31


2yqgwatmsyr1p :V2                                                          VARCHAR2(4000)
TEST
2023-05-06 14:54:31


2yqgwatmsyr1p :V3                                                          VARCHAR2(4000)
2023-3-15
2023-05-06 14:54:31

3）从dba_hist_sqlbind、dba_hist_sql_bind_metadata中获取，和v$sql_bind_capture一样

4）通过display_cursor,format设为advanced或peeked_binds获取
dingjun123@ORADB> select * from table(dbms_xplan.display_cursor('2yqgwatmsyr1p',null,' +peeked_binds'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2yqgwatmsyr1p, child number 0
-------------------------------------
select * from t where object_id = :v1 or object_name = :v2 or created >
:v3


Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   308 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   950 | 93100 |   308   (1)| 00:00:04 |
--------------------------------------------------------------------------


Peeked Binds (identified by position):
--------------------------------------


   1 - :V1 (VARCHAR2(30), CSID=852): '123'
   2 - :V2 (VARCHAR2(30), CSID=852): 'TEST'
   3 - :V3 (VARCHAR2(30), CSID=852): '2023-3-15'

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("CREATED">:V3 OR "OBJECT_NAME"=:V2 OR
              "OBJECT_ID"=TO_NUMBER(:V1)))

5）10046事件获取绑定变量值，level4
alter session set events '10046 trace name context forever, level 4';