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

October 18, 2010 by 14 Comments

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

Filed Under: Oracle Tagged With: , ,

Comments

  1. Tanel Poder says:
    October 18, 2010 at 9:56 am

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

    Reply
  2. DanyC says:
    October 18, 2010 at 1:46 pm

    One word: WOOOW !!

    Thanks,
    Dani

    Reply
  3. Amardeep Sidhu says:
    October 19, 2010 at 10:28 pm

    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” ;)

    Reply
  4. Rich says:
    October 22, 2010 at 10:07 pm

    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

    Reply
  5. Tanel Poder says:
    October 23, 2010 at 2:18 am

    @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…

    Reply
  6. Tanel Poder says:
    October 23, 2010 at 2:19 am

    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)

    Reply
  7. Rich says:
    October 23, 2010 at 12:38 pm

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

    Reply
  8. Rich says:
    October 25, 2010 at 11:10 pm

    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’));

    Reply
  9. puthran says:
    August 24, 2012 at 3:20 pm

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

    Reply
  10. Matthias Rogel says:
    March 22, 2013 at 6:23 am

    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

    Reply
  11. Bertrand says:
    April 29, 2013 at 4:49 am

    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

    Reply

Trackbacks

  1. October 2010 Blogroll Report | AskDba.org Weblog says:
    December 28, 2011 at 1:22 am

    [...] query instead of dumping using errorstack. Tanel Poder gives a example describing with example http://blog.tanelpoder.com/2010/10/18/read-currently-running-sql-statements-bind-variable-values/ Dominic talks about NLS (I know many of you would be scared but believe me you can’t avoid [...]

    Reply
  2. Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2 | bdt's oracle blog says:
    April 29, 2013 at 4:37 am

    [...]  For those ones, Tanel Poder helped us to retrieve the passed values from v$sql_monitor into this blog post (This is reliable compare to [...]

    Reply

Speak Your Mind

*