Thursday, January 23, 2014

how to read the values of bind variables for currently executing statements real time monitoring kicks in

As usual Tanel Poder has done an excellent job of writing up this approach.

Caveats I have tested this in 11.2.0.2.x and 11.2.0.3.x might have some issues earlier not quite sure but hey Tanel probably has this documented also.

This came in really handy recently looking at some SQL chewing up large amounts of LIO.

Here is Tanels writeup: bind variable sql monitor leading over to here ...


7 comments:

  1. An easy and expedite way - if you know the sql_id of the statement in question - is to use this:

    select position,name,value_string
    from v$sql_bind_capture
    where sql_id = '&sqlid'

    Works with 10g onwards, dunno about 12c

    ReplyDelete
  2. Problems noted by many in using the sql_bind_capture ( multiple sources ) but while executing the query against the sql monitor ( if licensed ) appears accurate as far as I know.

    ReplyDelete
  3. Can you get me references to those "problems"? I'd like to know what they are and when/how..

    ReplyDelete
  4. Nuno Tanel has this noted in the first link: And a related comment – V$SQL_BIND_CAPTURE is not a reliable way for identifying the current bind variable values in use. Oracle’s bind capture mechanism does not capture every single bind variable into SGA (it would slow down apps which run lots of short statements with bind variables). The bind capture only selectively samples bind values, during the first execution of a new cursor and then every 15 minutes from there (controlled by _cursor_bind_capture_interval parameter), assuming that new executions of that same cursor are still being started (the capture happens only when execution starts, not later during the execution).

    ReplyDelete
    Replies
    1. Ah OK. Thanks a lot. SQLMonitor would also miss most "rapid-fire" SQL anyways. Ah well, it still picks the heavy ones, which is important.

      Delete
    2. Yes and if one wanted to do some custom monitoring one could sample periodically the contents of the sql monitor view and stash that stuff away ( for 10 days ? ) elsewhere including the binds because the results in sql monitor don't hang around long do they? It's usually just a couple of really bad boys that you care about most right?

      Delete
    3. Hehehe! Hopefully!
      (but I've seen a few more...)

      Delete