sql_trace的使用

 

sql_trace 是Oracle 提供用于进行sql语句追踪的工具。

 

 启用方法

1 在全局中使用

          在参数文件(pfile/spfile)中指定：

           sql_trace = true

          在全局启用sql_trace会令到所有进程都会被追踪，包括后台进程以及所有用户进程。会导致比较明显的性能下降。所以在production环境一般不使用。

2  在当前session启用

           大多数时候我们使用sql_trace 追踪当前进程。前提是当前账号具有alter session的系统权限。

           启用命令为alter session set sql_trace=true;           

3  跟踪其他用户的进程

          很多时候我们需要跟踪的是其他用户的进程，而不是当前用户(进程），这可以通过Oracle的SYS用户下的包DBMS_SYSTEM中的存储过程SET_SQL_TRACE_IN_SESSION来完成

         SET_SQL_TRACE_IN_SESSION 要求用户提供3个参数：SID, SERIAL#, SQL_TRACE(开关)；（如果没有dbms_system包权限看文末解决方法）

 

下面讲解如何跟踪其他用户的进程

名词解释

Sid:  Session identifier    # session 的标识符

SERIAL#

Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.

#Session 的序列号。作为唯一标示符用于标识session的对象。（当1个session结束而另1个新的session使用了旧session相同的sid时）保证在session层面上的命令能应用在正确的session对象身上。

 

我们可以通过查看v$session来获得sid,serial#等信息

 

select sid,serial#,username from v$session where username is not null;

 可以找到想跟踪的用户的sid、serial#，如果同一个用户多点登录，可以在该session下使用命令查询当前session的sid

 

 

select userenv('sid') from dual;

 

 

最后执行

exec dbms_system.set_sql_trace_in_session(sid,serial#,true)

然后就是查看trace记录的问题了

 

查看追踪的记录数据

         一般来讲，被追踪的数据会存放在这个目录下 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/。文件命名是有规则的，实列名_ora_对应进程名(pid).trc

         具体位置可以由下面sql语句查询出来：

 

select d.value || '/' || 
        lower(rtrim(i.instance, chr(0))) || '_ora_' || p.spid || '.trc' trace_file_name
from
    -- spid
   ( select p.spid
     from sys.v$mystat m,
             sys.v$session s,
             sys.v$process p
     where m.statistic# = 1 
         and s.sid = m.sid      ——由于连接了sys.v$mystat这里只能搜索当前session的sid
         and p.addr = s.paddr) p,
    -- instance name     
   ( select t.instance 
      from sys.v$thread  t,
              sys.v$parameter  v
     where v.name = 'thread' 
     and (v.value = 0 or 
             t.thread# = to_number(v.value))) i,
    --path
   ( select value 
    from sys.v$parameter 
    where name = 'user_dump_dest') d
 / 
上面这个语句只能用于查看当前用户当前进程的记录文件，
如果要用于其他session， 就必须指定sid， 如下：
select d.value || '/' 
     || lower(rtrim(i.instance, chr(0))) || '_ora_' || p.spid || '.trc' trace_file_name
from
   ( select p.spid
     from 
             sys.v$session s,
             sys.v$process p
     where
         s.sid = 15        --这里指定sid 在sys.v$session就可以找出其他session的sid 
                              参考2.3  跟踪其他用户的进程
         and p.addr = s.paddr) p,
   ( select t.instance 
      from sys.v$thread  t,
              sys.v$parameter  v
     where v.name = 'thread' 
     --下面只不过保证字符转换数字不会出错
     and translate(v.value,'a1234567890.-','a') is null  --必须由数字和"."和"-"组成
   and translate(v.value,'1.-','1') is not null        --去掉"."和"-"后至少1个字符，也就是至少有1个数字啦
   and length(v.value) - length(replace(v.value,'.','')) < 2 --"."的个数必须少于2
   and (instr(v.value,'-',0)= 0 or (                         --没有"-"
                                     --或者只有1个"-"字符，而且用"-"开头
                                    (length(v.value) - length(replace(v.value,'-','')) = 1)
                                     and  substr(v.value,1,1) ='-'))
     and (v.value = '0' or 
             t.thread# = to_number(v.value))) i,
    --path
   ( select value 
    from sys.v$parameter 
    where name = 'user_dump_dest') d
 / 

 

获取dbms_system包权限

1、首先确定dbms_system的包是否在，以sys或具有sysdba权限的用户连入
sqlplus 'sys/pass as sysdba'
SQL>desc dbms_system 
看是否报错。
2、如果报错报不存在，按 lcmlsj 做法把包建上

在sqlplus下执行
{ORACLE_HOME}/rdbms/admin/prvtutil.plb
3、如果你用的是很它用户来执行dbms_system,则可以这样：
sqlplus 'sys/pass as sysdba'
SQL>grant execute on dbms_system to <your_username>;
SQL>connect your_username/pass
SQL>CREATE SYNONYM DBMS_SYSTEM FOR SYS.DBMS_SYSTEM;