Skip to Main Content
  • Questions
  • Can you see the values of bind variables without enabling trace?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 21, 2017 - 11:51 am UTC

Last updated: November 02, 2018 - 2:56 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Sometimes I want to capture the SQL a process is running, and often the quickest method is to use the session browser tool in Toad, SqlDeveloper, or just the v$session and v$sql tables directly. However what I obviously see is this:

Select something
  From my_table t
 Where t.value = :1


Now I know I could enable tracing and look at the binds, however is there any way to get them from the v$ tables? v$sql_bind_capture appears to be those captured on the first run (peeked binds?)

Select /*JK123*/ * From dual where dummy = :1;

Select * From  V$SQL WHERE sql_text like '%JK123%';

Select * From V$SQL_BIND_CAPTURE where sql_id='auac7nd4vqhx6'; 


Is there any v$ view (or otherwise) that says this session used these binds when running this sql_id at this time?

Thanks

and Connor said...

We capture *some* bind information, but not all of it - it's sampled rather than every execution (too expensive an overhead to do so)

v$sql_bind_capture
v$sql_plan.other_xml

There is also

v$sql_bind_data
v$sql_bind_metadata

but they are session specific

If you are licensed for it (Diag + Tuning), you can also use V$SQL_MONITOR.BIND_XML for queries under the real time sql monitoring umbrella

Rating

  (2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Thanks

John Keymer, February 23, 2017 - 9:32 am UTC

Thanks for that, I'll take a look. I appreciate it would be quite expensive to store a history of all binds. Maybe a halfway house would be a v$ view of currently executing SQL binds. Not to worry, I'll see how I get on with the above. :)

Bind Value Peeking

Martin Haltmayer, October 31, 2018 - 5:15 pm UTC

Helpful as always.

However, back to the original question: is there in the newer versions of Oracle any way of learning the current bind value?

A desperately desired functionality would be "read the bind value of the right side operand of a like expression and supply an Oracle Text clause by a VPD policy with contains and that very bind variable for pre-selecting".
Connor McDonald
November 02, 2018 - 2:56 am UTC

Not I know of sorry.

You could raise it as an enhancement request here

https://community.oracle.com/community/groundbreakers/database/database-ideas

and then garner support from the user community. More votes = more likely to occur