SQL Server Profiler 跟踪指定实例

转载

网络安全守护神 2024-07-22 20:10:27

文章标签 数据库 sql SQL sed 文章分类 SQL Server 数据库

2011年1月14日 /2011-12-15增加修改

sql跟踪方法介绍

一、sql_trace的介绍

--打开trace文件设置，把sql trace设置为true，就会在udump目录中增加一个trc文件。
alter session set sql_trace=true;
show parameter sql_trace;（select * from v$parameter where name='sql_trace';）
修改后不生效呢？show parameter sql_trace;其参数始终是FALSE
    show parameter sql_trace是从v$parameter中取相应参数，而v$parameter是体现了全部的init.ora的内容。而show user应该是取的当前会话的参数。
sql_trace 是可以在系统或会话级上启用，并且产生很多输出，系统级应该是直接改init了，会话级就用alter session set sql_trace = true来生效。ISSES_MODIFIABLE=false应该是说明在会话一级上，不能启用系统一级的跟踪
    如果修改系统的参数，用如下语句：
alter system set sql_trace=true;
    此时在查看show parameter sql_trace;可看到sql_trace的value值为TRUE

--对跟踪文件做标记
alter session set tracefile_identifier='look_for_me'; --

在D:\oracle\product\10.2.0\admin\fgisdb\udump路径下可以找到标记为look_for_me的跟踪文件

--停止该会话的跟踪

alter session set sql_trace=false;

二、10046跟踪代码
跟踪级别介绍：
Level 0   停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1   标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4   在level 1的基础上增加绑定变量的信息
Level 8   在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息

打开跟踪：
GRANT ALTER SESSION TO lttfm; --必须具有alter session权限
alter session set events '10046 trace name context forever,level 12';--当前用户设置
或者在init.ora文件中插入下面的行：event = 10046 trace name context forever,level 12;--为全局设置

关闭跟踪：
alter session set events '10046 trace name context off';--关闭用户跟踪

注：sql_trace和10046设置代码跟踪只能针对本会话或者系统级进行会话跟踪，具体设置某个非本会话的跟踪需要采用oradebug或者
dbms_system.set_ev或者dbms_monitor.session_trace_enable。

三、使用oradebug
--用如下语句找出要跟踪的spid
select * from v$session a where audsid = userenv('sessionid'); --查询当前的sessionid

select s.USERNAME, 
  
       s.OSUSER, 
  
       s.SID, 
  
       s.PADDR, 
  
       s.PROCESS, 
  
       p.spid     os_process_id, 
  
       p.pid      oracle_process_id 
  
  from v$session s, v$process p 
  
 where s.paddr = p.addr 
  
   and s.username = upper('LTWEBGIS') 
  
   and s.SID = 145;

--设置跟踪进程id。
SQL> connect / as sysdba
SQL> oradebug setospid 5672;该语句为跟踪其他会话（5672为v$process的spid），若跟踪本会话，执行 oradebug setmypid。
或
SQL> connect / as sysdba
SQL> oradebug setorapid 15 --输入的15为v$process的pid

--设置跟踪文件大小无限制
SQL> oradebug unlimit;
--设置跟踪，级别8
SQL> oradebug event 10046 trace name context forever,level 8;
已处理的语句

--关闭跟踪
SQL> oradebug event 10046 trace name context off;

执行该语句（SQL> oradebug setospid 5672;）时，提示“ORA-01031: 权限不足”，
原因：oradebug是sysdba的命令（一般用户执行提示权限不足），而且是sqlplus特有的命令,不能在plsql工具中执行（否则提示无效sql）。
可以用oradebug help命令查看oradebug工具说明。

四、dbms_system（必须用sys用户执行）
exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>8 ,nm => '');--会话id等参数必须设置正确，否则trace无法生成
生成后可用sql语句查看trace文件位置。

--停止跟踪
exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>0 ,nm => '');

五、dbms_monitor
exec dbms_monitor.session_trace_enable(session_id => 136,serial_num => 4,waits => true,binds => true);
exec dbms_monitor.session_trace_disable(session_id => 136,serial_num => 4);
如果不设置session_id或者设置为null，则跟踪当前会话

六、dbms_support
exec dbms_support.start_trace_in_session(sid => 1234,serial# => 56789,waits => true,binds => true);
exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);
dbms_support默认情况下，系统不安装这个包。如果需要使用的话，需进行单独设置。在$ORACLE_HOME/rdbms/admin/目录下应该存在
dbmssupp.sql，prvtsupp.plb这两个文件,执行这两个文件后才可使用，如果别的用户要使用，需要进行相应的授权，并创建同义词。

注：3,4,5,6这几种跟踪方法都是在sys的用户下才能执行，可对任意会话进行跟踪。

获取跟踪文件：
1）oradebug获取跟踪文件
--使用oradebug获取本会话跟踪文件位置

SQL> oradebug setmypid 
   
SQL> oradebug tracefile_name

--获取任意会话跟踪文件位置

SQL> oradebug setospid 5392

已处理的语句

SQL> oradebug tracefile_name 
   
d:\oracle\product\10.2.0\admin\fgisdb\udump\fgisdb_ora_5600.trc

2）sql获取跟踪文件
--sql查看当前session跟踪文件位置

select d.value || '\' || lower(rtrim(i.instance, chr(0))) || '_ora_' || 
   
       p.spid || '.trc' trace_file_name 
   
  from (select p.spid 
   
          from v$mystat m, v$session s, v$process p 
   
         where m.statistic# = 1 
   
           and s.sid = m.sid 
   
           and p.addr = s.paddr) p, 
   
       (select t.instance 
   
          from v$thread t, v$parameter v 
   
         where v.name = 'thread' 
   
           and (v.value = 0 or t.thread# = to_number(v.value))) i, 
   
       (select value from v$parameter where name = 'user_dump_dest') d;

--以下sql为查询输入的spid的会话跟踪文件

select d.value || '\' || lower(rtrim(i.instance, chr(0))) || '_ora_' || 
   
       &spid || '.trc' trace_file_name 
   
  from (select t.instance 
   
          from v$thread t, v$parameter v 
   
         where v.name = 'thread' 
   
           and (v.value = 0 or t.thread# = to_number(v.value))) i, 
   
       (select value from v$parameter where name = 'user_dump_dest') d;

查看跟踪级别
1）查看当前session的跟踪级别（必须在sys用户下执行）

declare 
   
  event_level number; 
   
begin 
   
  dbms_system.read_ev(10046,event_level); 
   
  dbms_output.put_line(to_char(event_level)); 
   
end;

2）执行如下语句查看跟踪事件的跟踪级别

SQL> oradebug setospid spid --先指定要查看跟踪级别的spid 
   
SQL> oradebug eventdump session 
   
10046 trace name CONTEXT level 8, forever

注：如果开启了跟踪会话，但之后数据库关闭，那么跟踪自动关闭。

TKPROF的介绍

Tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低，
一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出，从而找出有问题的SQL语句。

Tkprof命令后面可以带各种类型的排序选项，具体如下：
Usage: tkprof tracefile outputfile [explain= ] [table= ][print= ] [insert= ] [sys= ] [sort= ]

参数说明：

tracefile：要分析的trace文件 
  
outputfile：格式化后的文件 
  
explain=user/password@connectstring 
  
table=schema.tablename

注1：这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中
注2：该table必须是数据库中不存在的，如果存在会报错

print=n：只列出最初N个sql执行语句 
  
insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中 
  
sys=no：过滤掉由sys执行的语句 
  
record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去 
  
waits=yes|no：是否统计任何等待事件 
  
aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes 
  
sort= option：设置排序选项，选项如下： 
  
    prscnt：number of times parse was called 
  
    prscpu：cpu time parsing 
  
    prsela：elapsed time parsing 
  
    prsdsk：number of disk reads during parse 
  
    prsqry：number of buffers for consistent read during parse 
  
    prscu：number of buffers for current read during parse 
  
    prsmis：number of misses in library cache during parse 
  
    execnt：number of execute was called 
  
    execpu：cpu time spent executing 
  
    exeela：elapsed time executing 
  
    exedsk：number of disk reads during execute 
  
    exeqry：number of buffers for consistent read during execute 
  
    execu：number of buffers for current read during execute 
  
    exerow：number of rows processed during execute 
  
    exemis：number of library cache misses during execute 
  
    fchcnt：number of times fetch was called 
  
    fchcpu：cpu time spent fetching 
  
    fchela：elapsed time fetching --要设置初始化参数time_statistics=true 
  
    fchdsk：number of disk reads during fetch 
  
    fchqry：number of buffers for consistent read during fetch 
  
    fchcu：number of buffers for current read during fetch 
  
    fchrow：number of rows fetched 
  
    userid：userid of user that parsed the cursor

可根据自己的需要设置排序

举例：
/*将生成的fgisdb_ora_1204_test.trc的跟踪文件转化为cc文件，条件：只列出前三个sql语句;生成insert.sql，运行该文件将数据保存至数据库;
提取sql语句，sqlstr.sql默认在执行该命令的路径；并且输出执行计划*/
C:\Documents and Settings\Administrator>tkprof D:\oracle\product\10.2.0\admin\fgisdb\udump\fgisdb_ora_1204_test.trc
c:\cc.txt print=3 insert=c:\insert.sql record=sqlstr.sql explain=gwm/gwm@fgisdb table=gwm.trace_test
sort=(prsela, exeela, fchela)
--sort选项可同时用多个，做法是用括号括起来，中间用逗号分割：
注意：最后排序是按照各个选项的数字之和进行排序，类似于order by (sort1+sort2+sort3)，而不是order by sort1,sort2,sort3

分析tkprof文件：
CALL ：每次SQL语句的处理都分成以下三个部分