This is a very useful view in 10g. When a user came to me with a performance problem, I used to find myself asking, “What value did you use for the search?”. Not very professional, although it could work. I would also set system 10046 trace and then grep for the problematic SQL (if I knew it) in all trace files created, so I could find a “bad” value.
In 10g, some of this has been fixed.
First, we show an example for a statement that was executed by PL/SQL…
SQL> declare
2 l_num number := 150;
3 l_name varchar2(30);
4 begin
5 select username into l_name from dba_users where user_id = l_num;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select sql_id,sql_text from v$sql where sql_text like '%SELECT USERNAME%WHERE USER_ID%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dgqy15vwrj25q
SELECT USERNAME FROM DBA_USERS WHERE USER_ID = :B1
7qzmv07r5yzas
select sql_id,sql_text from v$sql where sql_text like '%SELECT USERNAME%WHERE US
ER_ID%'
SQL> select sql_id||' -- ' ||value_string from v$sql_bind_capture where sql_id = 'dgqy15vwrj25q';
SQL_ID||'--'||VALUE_STRING
--------------------------------------------------------------------------------
dgqy15vwrj25q -- 150
SQL>
…and then another example for one with a host variable…
SQL> variable l_num number
SQL> exec :l_num := 150
PL/SQL procedure successfully completed.
SQL> select /* fake comment */ username from dba_users where user_id = :l_num;
USERNAME
------------------------------
CORCACCESS
SQL> select sql_id,sql_text from v$sql where sql_text like '%fake_comment%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
f65f9f6xvzj4w
select /* fake comment */ username from dba_users where user_id = :l_num
SQL> select sql_id||' -- ' ||value_string from v$sql_bind_capture where sql_id = 'f65f9f6xvzj4w';
SQL_ID||'--'||VALUE_STRING
--------------------------------------------------------------------------------
f65f9f6xvzj4w -- 150
SQL>
Binds are captured by default every 15 minutes. If you need to capture them more frequently, you can set the (undocumented) parameter “_cursor_bind_capture_interval”, such as…
alter system set "_cursor_bind_capture_interval" = 10; -- capture every ten seconds
My understanding is this can have a large impact on system performance, but I haven’t tested it.