Read currently running SQL statement’s bind variable values using V$SQL_MONITOR.BIND_XML in Oracle 11.2

The title pretty much says it. In Oracle 11.2, if you have the Diag+Tuning Pack licenses and the SQL monitoring kicks in for your SQL statement, then instead of the old fashioned ERRORSTACK dump reading you can just query the V$SQL_MONITOR.BIND_XML to find the values and metadata of your SQL statement’s bind variables.

I’ve written an example here:

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).

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

20 Responses to Read currently running SQL statement’s bind variable values using V$SQL_MONITOR.BIND_XML in Oracle 11.2

  1. Tanel Poder says:

    I just made an update to the tech.e2sn article and added a comment about a related parameter “_sqlmon_binds_xml_format”

  2. DanyC says:

    One word: WOOOW !!

    Thanks,
    Dani

  3. I think my first comment went into spam. Now it is not allowing me to post that again but i just wanted to say “awesome” ;)

  4. Rich says:

    Tanel,
    this is for session which is currently running now, what about the session which was completed few min/hour back and I want to capture all sql’s and used binds that session executed, is this possible in 11g R2 ?

    I am on 11.1 and facing really touch time for bind capturing as there is no column for sid, I have to trust sql_id.
    can you hack binds in 11.1 for sid’s ? (sessions completed / currently running)

    Rich

  5. Tanel Poder says:

    @Rich
    If the session / SQL is already finished, then this information is gone. If you know in advance that you want to capture a session, then SQL_TRACE with bind tracing enabled will capture everything you need…

  6. Tanel Poder says:

    I mentioned that V$SQL_BIND_CAPTURE is unreliable for capturing ALL binds used… but it can be enough if you want just occasional bind variable samples (taken 1st hard parse and every 15mins if new executions keep being done)

  7. Rich says:

    I trust you completely that sql_bind_capture is unreliable, its good but too late that Oracle thought about real time bind capture ..

    thanks for this info.

    Rich ..

  8. Rich says:

    I forgot about one thing,
    with the help of dbms_xplan we can get the info of peeked bind.

    select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable,PLAN_HASH_VALUE,hash_value,sql_profile,OBJECT_STATUS from v$sql where sql_id = ‘&sql_id’ ;

    CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITI IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE HASH_VALUE SQL_PROFILE OBJECT_STATUS
    ———— ———- ———– ————— ————— ————— ————— ———- ————————- ——————–
    0 711331 105370222 N N Y 1242873453 2773956540 VALID
    1 486416 71533540 N N Y 1242873453 2773956540 VALID
    2 404571 59230134 N N Y 1242873453 2773956540 VALID
    4 678763 100323603 N N Y 1242873453 2773956540 VALID

    Select * from table(dbms_xplan.display_cursor(‘&&sql_id’, &&child_num,’basic +PEEKED_BINDS’));

  9. puthran says:

    Yes that’s right V$SQL_BIND_CAPTURE is not reliable.

  10. Tanel, thanks, useful !

    However, I discovered that V$SQL_MONITOR doesn’t show all bind variables.
    For example ( I am on 11.2.0.2 ), it doesn’t show inputs to pipelined table functions made as bind variables ( Errorstack dump reading does this ).

    This reproduces for me:

    create table large_emp
    as
    select emp1.*
     from scott.emp emp1, scott.emp, scott.emp, scott.emp, scott.emp, scott.emp, scott.emp; 
     
     
    CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
    /* http://www.oracle-base.com/articles/misc/dynamic-in-lists.php */
    /
    
    CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
    /* http://www.oracle-base.com/articles/misc/dynamic-in-lists.php */
      RETURN t_in_list_tab
    AS
      l_tab   t_in_list_tab := t_in_list_tab();
      l_text  VARCHAR2(32767) := p_in_list || ',';
      l_idx   NUMBER;
    BEGIN
      LOOP
        l_idx := INSTR(l_text, ',');
        EXIT WHEN NVL(l_idx, 0) = 0;
        l_tab.extend;
        l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
        l_text := SUBSTR(l_text, l_idx + 1);
      END LOOP;
    
      RETURN l_tab;
    END;
    / 
    
    
    variable inl varchar2(100)
    exec :inl := 'SALESMAN, MANAGER'
    
    variable dummy varchar2(100)
    exec :dummy := 'dummy'
    
    SELECT count(*)
    FROM   large_emp
    WHERE  :dummy = :dummy and job IN (select * from TABLE(in_list(:inl)))
    /
    

    Then, in another session

    variable sid number
    exec :sid := ...
    select xmltype(binds_xml) from v$sql_monitor where sid = :sid and status = 'EXECUTING';
    XMLTYPE(BINDS_XML)
    --------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------
    <binds>
      <bind name=":DUMMY" pos="1" dty="1" dtystr="VARCHAR2(2000)" maxlen="2000" csid="873" len="5">dummy</bind>
    </binds>
    

    Note that bind variable named INL is not listed here.

    Errorstack however shows

    ...
    ----- Bind Info (kkscoacd) -----
     Bind#0
      oacdty=01 mxl=2000(300) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000000 frm=01 csi=873 siz=2000 off=0
      kxsbbbfp=2ad3b6dd4a98  bln=2000  avl=05  flg=05
      value="dummy"
     Bind#1
      No oacdef for this bind.
     Bind#2
      oacdty=01 mxl=2000(300) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000000 frm=01 csi=873 siz=2000 off=0
      kxsbbbfp=2ad3b682d848  bln=2000  avl=17  flg=05
      value="SALESMAN, MANAGER"
     Frames pfr 0x2ad3b6d74700 siz=14952 efr 0x2ad3b6d74668 siz=13688
     Cursor frame dump
      enxt: 6.0x00000010  enxt: 5.0x00000600  enxt: 4.0x00000fc0  enxt: 3.0x00000ff8
      enxt: 2.0x00000060  enxt: 1.0x00000f60
      pnxt: 1.0x000004f0
     kxscphp=0x2ad3b65204e8 siz=3280 inu=2216 nps=1744
     kxscdfhp=0x2ad3b66a64e8 siz=984 inu=88 nps=0
     kxscbhp=0x2ad3b6776068 siz=5064 inu=4264 nps=4080
     kxscwhp=0x2ad3b67c6730 siz=8991520 inu=8991072 nps=8974344
    Starting SQL statement dump
    SQL Information
    user_id=203 user_name=DP module=SQL*Plus action=
    sql_id=5nmz5yuyfutgd plan_hash_value=92535503 problem_type=4
    ----- Current SQL Statement for this session (sql_id=5nmz5yuyfutgd) -----
    SELECT count(*)
    FROM   large_emp
    WHERE  :dummy = :dummy and job IN (select * from TABLE(in_list(:inl)))
    sql_text_length=104
    sql=SELECT count(*)
    FROM   large_emp
    WHERE  :dummy = :dummy and job IN (select * from TABLE(in_list(:inl)))
    ...
    

    Are you aware of any workaround ?

    Thanks
    Matthias

  11. Bertrand says:

    Hello,

    I just want to let you know that (thanks to this post) i created an sql script to retrieve peeked and passed values per execution, see: http://bdrouvot.wordpress.com/2013/04/29/bind-variable-peeking-retrieve-peeked-and-passed-values-per-execution-in-oracle-11-2/

    One more time: Thanks Tanel ! ;-)

    Bertrand

  12. Raj says:

    Hi Tanel,

    Is there any way we can capture the queries which were using paralliesm from the past ? If yes , then how we can capture them with parallel degree requisition they made & how much parallelism was allocated to those queries?

    Regards:
    Raj.

    • Tanel Poder says:

      Which DB version? In V$SQL / V$SQLSTATS (and the corresponding DBA_HIST_SQLSTAT view) have EXECUTIONS and PX_SERVERS_EXECUTIONS columns, which give you a quick idea of the latest SQL statements executed in parallel and the avg number of slaves per execution that got used (you may want to use the GV$ views instead in RAC):

      SQL> SELECT sql_id, executions, px_servers_executions, px_servers_executions / NULLIF(executions,0) avg_slaves, last_active_time FROM v$sqlstats WHERE px_servers_executions > 0;

      SQL_ID EXECUTIONS PX_SERVERS_EXECUTIONS AVG_SLAVES LAST_ACTIVE_TIME
      ------------- ---------- --------------------- ---------- -----------------
      28uh41n6g49c3 1 8 8 20130725 19:57:09

    • Tanel Poder says:

      Note that there are other ways, like the V$SQL_MONITOR view and estimating the number of slaves from ASH (and looking into the PX_FLAGS column in ASH).

  13. Mayank Singh says:

    Hi Tanel,
    I have messing around for many days to get values out of Bind variable in INSERT/UPDATE stmt
    But i failed to get any solution yet

    Bind value are not getting catch in v$sql_monitor or v$sql_bind_capture
    I tried all types oracle traces including ORADEBUG, but still bind values are not getting displayed ,

    But user table is getting populated including v$sql is updating whenever process run

    Please enlighten me

    Thanks,Mayank

    • Tanel Poder says:

      So what about SQL tracing your test case and see if the values are reported in the Bind# section at all?

      • Mayank Singh says:

        I tried to reproduce scenario using below simple example

        SQL> create table test_insert (n1 number);
        SQL> var b1 number;
        SQL> exec :b1 := 10;
        SQL> alter session set events ‘10046 trace name context forever, level 12′;
        SQL> insert into test_insert values (:b1);
        SQL> alter session set events ‘10046 trace name context off';

        alter SESSION set events ‘10046 trace name context forever, level 12′; — trace file are generating with Insert stmt with bind values

        alter SYSTEM set events ‘10046 trace name context forever, level 12′; — trace files are NOT generating

        So this may be the reason why I cannot see Trace files generated by my process , as I am trying to generate trace using ALTER SYSTEM
        As my process is connecting to DB on fly

        Please suggest why ALTER SYSTEM is not able to generate trace files and how can I trace session created on fly by the process

        Thanks,
        Mayank

        • Tanel Poder says:

          ALTER SYSTEM SET EVENTS will not change the event settings for existing sessions. It changes something called “system event array” and only the new sessions will pick up these flags. Similarly, when you use ALTER SYSTEM to disable an event, it won’t disable it in existing sessions which got it enabled, only the new ones that log in.

          So, use DBMS_MONITOR instead, it’s the best way for enabling SQL trace in 10g+

          • Mayank Singh says:

            Hi Tanel,
            Your golden words help me to resolve problem which was open for long (also SR open for same to Oracle without any direction)
            Everyone including me & Oracle corp was looking in SET EVENTS trace files

            I take related SID from v$open_cursor using SQL_ID

            select * from v$open_cursor where sql_id =’g5ybmur8fcc98′

            Then run below using SID

            SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1546, serial_num=>605, waits=>FALSE, binds=>TRUE);
            PL/SQL procedure successfully completed.
            SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1558, serial_num=>10815, waits=>FALSE, binds=>TRUE);

            Trace file was generated with INSERT stmt with bind values

            I need one clarification please
            1) Is there any limit to values returned in Trace files , as my INSERT statement have 138 Bind variable declare (for columns) but in trace i can see value for only 79 bind values

            2) If bind variable in trace do not have tag ‘value=’ , it means NULL values is being passed to Bind variable for that column

            Thanks again, you are the best !!!

            Regards,
            Mayank

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>