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.<\/p>\n
In 10g, some of this has been fixed. <\/p>\n
First, we show an example for a statement that was executed by PL\/SQL…<\/p>\n
\r\nSQL> declare\r\n 2 l_num number := 150;\r\n 3 l_name varchar2(30);\r\n 4 begin\r\n 5 select username into l_name from dba_users where user_id = l_num;\r\n 6 end;\r\n 7 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select sql_id,sql_text from v$sql where sql_text like '%SELECT USERNAME%WHERE USER_ID%';\r\n\r\nSQL_ID\r\n-------------\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\ndgqy15vwrj25q\r\nSELECT USERNAME FROM DBA_USERS WHERE USER_ID = :B1\r\n\r\n7qzmv07r5yzas\r\nselect sql_id,sql_text from v$sql where sql_text like '%SELECT USERNAME%WHERE US\r\nER_ID%'\r\n\r\n\r\nSQL> select sql_id||' -- ' ||value_string from v$sql_bind_capture where sql_id = 'dgqy15vwrj25q';\r\n\r\nSQL_ID||'--'||VALUE_STRING\r\n--------------------------------------------------------------------------------\r\ndgqy15vwrj25q -- 150\r\nSQL><\/pre>\n…and then another example for one with a host variable…<\/p>\n
\r\nSQL> variable l_num number\r\nSQL> exec :l_num := 150\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select \/* fake comment *\/ username from dba_users where user_id = :l_num;\r\n\r\nUSERNAME\r\n------------------------------\r\nCORCACCESS\r\n\r\nSQL> select sql_id,sql_text from v$sql where sql_text like '%fake_comment%';\r\n\r\nSQL_ID\r\n-------------\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\n\r\nf65f9f6xvzj4w\r\nselect \/* fake comment *\/ username from dba_users where user_id = :l_num\r\n\r\n\r\nSQL> select sql_id||' -- ' ||value_string from v$sql_bind_capture where sql_id = 'f65f9f6xvzj4w';\r\n\r\nSQL_ID||'--'||VALUE_STRING\r\n--------------------------------------------------------------------------------\r\nf65f9f6xvzj4w -- 150\r\n\r\nSQL>\r\n<\/pre>\nBinds 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…<\/p>\n
\r\nalter system set \"_cursor_bind_capture_interval\" = 10; -- capture every ten seconds\r\n<\/pre>\nMy understanding is this can have a large impact on system performance, but I haven’t tested it.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n