如何获取ODP.NET中通过OracleParameter传递的绑定变量的跟踪信息? [英] How to get tracing info for binding variables passed through OracleParameter in ODP.NET?

问题描述

经过大量的搜索并且没有找到我想要的东西之后，我决定问这个问题.

After Googling a lot and not finding what I'm looking for I decided to ask this question.

我正在使用绑定变量，如 2005 上的这篇很棒的文章所述，标题为

I'm using binding variables as demonstrated in this awesome article from 2005 titled The Values That Bind by Mark A. Williams, like this:

OracleParameter p_APP_NAME =
    new OracleParameter("p_APP_NAME", OracleDbType.NVarchar2, ParameterDirection.Input);
                         p_APP_NAME.Size = 50;
                         p_APP_NAME.Value = log.Application.Name;
                         cmd.Parameters.Add(p_APP_NAME);

我成功启用了 ODP.NET调试跟踪但是缺少的一个关键信息是记录的SQL statement没有显示出绑定变量绑定的值是什么.

I successfully enabled ODP.NET debug tracing but one key info that's missing is that the logged SQL statement doesn't show me what was the value bound to the binding variable.

正在记录OracleCommand.CommandText，但没有OracleCommand.Parameters值.正在向我显示此内容:

It's logging OracleCommand.CommandText but without OracleCommand.Parameters values. It's showing me this:

TIME:2013/09/20-22:59:21:890 TID:20fc  OpsSqlPrepare2(): SQL: UPDATE PS_LOG SET 
                                              APP_NAME = :p_APP_NAME, 
                                              WHERE LOG_ID = :p_LOG_ID

我真正想看到的是发送到ORACLE服务器的查询中使用的实际值，如下所示:

What I'd really like to see are the actual values that were used in the query sent to the ORACLE server like this:

TIME:2013/09/20-22:59:21:890 TID:20fc  OpsSqlPrepare2(): SQL: UPDATE PS_LOG SET 
                                              APP_NAME = 'App Name', 
                                              WHERE LOG_ID = 777

我是否缺少某些配置，或者使用ODP.NET跟踪功能时所需的此信息不可用?

Am I missing some configuration or this info I want is not available when using ODP.NET tracing capability?

如果这不是内置的，我想我必须实现自己的替换方法并亲自记录SQL Statement.

If this is not built-in I guess I'll have to implement my own substitution method and log the SQL Statement myself.

推荐答案

作为选项之一，您可以通过手动或自动为会话设置级别12或4的10046事件来打开具有绑定变量转储的sql跟踪(例如，在用户的登录触发器中):

As one of the options, you could turn on sql tracing with bind variables dumping by setting 10046 event of level 12 or 4 for a session, either manually or automatically(in a logon trigger for a user for instance):

alter session set events '10046 trace name context forever, level 12';

之后，将在user_dump_dest参数指定的目录中生成跟踪文件.

After that trace file will be generated in a directory specified by user_dump_dest parameter.

SQL> show parameter user_dump_dest;

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
user_dump_dest                       string      D:\...\diag\rdbms\cdb\cdb 
                                                 \trace   

这里是一个例子:

SQL> alter session set events '10046 trace name context forever, level 12';
  2 variable var number;
  3 exec :var := 1234567;     -- our bind variable
  4 select 1 from dual where 1234567 = :var ;

在新生成的跟踪文件中提供的其他信息中，我们可以找到有关查询，绑定变量及其值的信息:

Among other information presented in a newly generated trace file we could find information about the query, bind variable(s) and its/their values:

在光标中解析#375980232 len = 40 dep = 0 uid = 103
oct = 3 lid = 103 tim = 2640550035 hv = 1641534478
ad ='7ff5bd0baf0'sqlid ='ap9rzz5hxgp0f'
从对偶中选择1，其中1234567 =:var <-我们的查询
STMT结束
PARSE#375980232:c = 0，e = 375，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 0，og = 1，plh = 0，tim = 2640550034
BINDS#375980232:
绑定#0
oacdty = 02 mxl = 22(22)mxlc = 00 mal = 00 scl = 00 pre = 00
oacflg = 03 fl2 = 1000000 frm = 01 csi = 178 siz = 24 off = 0
kxsbbbfp = 16646e10 bln = 22 avl = 05 flg = 05
value = 1234567 <-绑定变量

PARSING IN CURSOR #375980232 len=40 dep=0 uid=103
oct=3 lid=103 tim=2640550035 hv=1641534478
ad='7ff5bd0baf0' sqlid='ap9rzz5hxgp0f'
select 1 from dual where 1234567 = :var <-- our query
END OF STMT
PARSE #375980232:c=0,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=2640550034
BINDS #375980232:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=24 off=0
kxsbbbfp=16646e10 bln=22 avl=05 flg=05
value=1234567 <-- value of the bind variable

从Oracle 10g及更高版本开始，您还可以查询v$sql_bind_capture动态性能视图以获取有关绑定变量及其值的信息:

You could also, starting from Oracle 10g and up, query v$sql_bind_capture dynamic performance view to get information about bind variable(s) and their values:

select t.parsing_user_id
     , t.sql_fulltext         -- text of a query
     , bc.name                -- name of a bind variable
     , bc.value_string        -- value of a bind variable
  from v$sqlarea t
  join v$sql_bind_capture bc
    on (bc.sql_id = t.sql_id)
  join v$session s
    on (s.user# = t.parsing_schema_id)
  where s.username = user
    and bc.name in (':VAR') -- name of a bind variable(s), 
                            -- value(s) of which we want to know

结果:

PARSING_USER_ID   SQL_FULLTEXT                      NAME    VALUE_STRING   
 ------------------------------------------------------------------------ 
           103   select 1 from dual where 1 =:var  :VAR     1234567

这篇关于如何获取ODP.NET中通过OracleParameter传递的绑定变量的跟踪信息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！