Using v$sql_bind_capture

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.