Re: bind variables and child cursors
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: denis.sun@xxxxxxxxx
- Date: Thu, 26 Jan 2012 11:48:56 -0600
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/@nam') as bind_name,
extractvalue(value(d), '/bind/@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/
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
