Re: bind variables and child cursors

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Thu, 26 Jan 2012 11:48:56 -0600
Message-Id: <6AA0CF52-AEE9-4A93-A135-12C0797E0F0E_at_enkitec.com>

Agreed - you may be able to get the data from the other_xml field in v$sql_plan (this would contain the values as parse time). If on 11g, v$sql_monitor may also contain the data if the statement is long running. A couple of scripts below.

• peeked_binds.sql col bind_name for a20 col bind_type for a20 col value for a40 select bind_name, decode(bind_type,1,'VARCHAR2',2,'NUMBER',bind_type) bind_type, decode(bind_type,1,display_raw(bind_data,'VARCHAR2'),2,display_raw(bind_data,'NUMBER'),bind_data) value from ( select extractvalue(value(d), '/bind/_at_nam') as bind_name, extractvalue(value(d), '/bind/_at_dty') as bind_type, extractvalue(value(d), '/bind') as bind_data from xmltable('/*/*/bind' passing ( select xmltype(other_xml) as xmlval from v$sql_plan where sql_id like nvl('&sql_id',sql_id) and child_number = '&child_no' and other_xml is not null ) ) d ) ;
• current_binds.sql break on sql_id on plan_hash_value col sql_exec_start for a20 select sid, session_serial# serial#, sql_id, sql_plan_hash_value plan_hash_value, sql_exec_id, status, to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start, elapsed_time/1000000 etime, buffer_gets, disk_reads , xmltype(binds_xml) from v$sql_monitor where sid like nvl('&sid',sid) and sql_id like nvl('&sql_id',sql_id) and sql_exec_id like nvl('&sql_exec_id',sql_exec_id) and status like nvl('&status','EXECUTING')
• order by sql_id, sql_exec_id order by sql_exec_start /

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Jan 26, 2012, at 11:33 AM, Denis wrote:

>

>>> I'm not aware of any method to peek at a bind value for a running query other than using extended trace. Once parsed, you can see it through ...

>
> Dump errorstack as I mentioned previously. There is a good discussion here: http://dioncho.wordpress.com/2009/05/07/tracking-the-bind-value/
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 26 2012 - 11:48:56 CST