sql跟踪

1、一直到9i，可以使用2种方法跟踪

跟踪当前session：

SQL>alter session set events '10046 trace name context forever,level 12';  --激活

SQL>alter session set events '10046 trace name context off';  --关闭

 

   级别分为：

   0  禁止调试事件

   1  调试激活，有SQL语句、响应事件、服务时间、处理行数、逻辑读数量、物理读数量、执行计划等等

   4  同1，加上绑定变量的额外信息

   8  同1，加上等待事件

   12  同4和8

 

跟踪其他session

开始

dbms_system.set_ev(si => 127,  --session id

                                   se => 29,   --serial number

                                   ev => 10046,   --event number

                                   le => 12,     --level

                                   nm => NULL)

 

关闭

dbms_system.set_ev(si => 127,  --session id

                                   se => 29,   --serial number

                                   ev => 10046,   --event number

                                   le => 0,     --level

                                   nm => NULL)

 

2、10G以后

10g以后提供了dbms_monitor包开启或关闭SQL跟踪，可以多个方面进行跟踪

会话级

开启

dbms_monitor.session_trace_enable(session_id => 127, 

                                                           serial_num => 29, 

                                                           waits => true, 

                                                           binds => false )

 

关闭

dbms_monitor.session_trace_disable(session_id => 127, 

                                                           serial_num => 29）

 

查询状态

select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=127

 

客户端级

首先要在客户端设置client_id

dbms_session.set_identifier(client_id => 'leo_id')

 

开启

dbms_monitor.client_id_trace_enable(client_id => 'leo_id',

                                                             waits => true, 

                                                             binds => false )

 

 

关闭

dbms_monitor.client_id_trace_disable(client_id => 'leo_id')

 

数据库级

开启

dbms_monitor.database_trace_enable(waits => true,

                                                                  bind => true,

                                                                  instance_name => null)

 

关闭

dbms_monitor.database_trace_disable(instance_name => null)

 

查询客户端级和数据库级的开启情况

select primary_id,waits,binds from dba_enabled_traces where trace_type='CLIEND_ID' or trace_type='DATABASE'

 

技巧：通过一个登陆触发器实现跟踪SQL

create role sql_trace;

create or replace trigger enable_sql_trace after logon on database

begin

  if(dbms_session.is_role_enable('SQL_TRACE'))

     then

        execute immediate 'alter session set timed_statistics=true';  --设定计时信息，跟statistics_level设置有关

        execute immediate 'alter session set max_dump_file_size=unlimited';  --限制跟踪文件大小

        dbms_monitor.session_trace_enable;

   end if;

end;

/

 

 

查找跟踪文件

11g的初始化参数 user_dump_dest和background_dump_dest实效，支持diagnostic_dest，不过新的参数只设定目录，可以查询v$diag_info

select value from v$parameter where name='diagnostic_dest';   --基本目录

select value from v$diag_info where name='Diag Trace';  --具体目录

select value from v$diag_info where name='Default Trace File'; --查询当前会话的trace文件

 

如果是11g以前，可以使用下面sql查询

SELECT s.sid,
       s.server,
       CASE 
         WHEN s.server IN ('DEDICATED','SHARED') THEN
           decode(substr(version,1,2),'11',i.instance_name,lower(i.instance_name)) || '_' ||
           nvl(lower(pp.server_name), nvl(lower(ss.name), 'ora')) || '_' ||
           p.spid
         ELSE
           NULL
       END
       ||
       CASE
         WHEN p.traceid IS NOT NULL THEN
           '_' || p.traceid
         ELSE
           ''
       END ||
       '.trc' AS trace_file_name
FROM v$instance i,
     v$session s,
     v$process p,
     v$px_process pp,
     v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = 'USER'
ORDER BY s.sid;

 

技巧

默认tracefile权限是600的，如果要提供给其他用户访问权限，可以设置一个参数：trace_files_public为true，默认创建时会生成644权限

 

TKPROF工具使用

[ora10@localhost ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.  --指定生成执行计划的表
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.   --执行计划
  print=integer    List only the first 'integer' SQL statements.     --- 一般都跟SORT使用，显示多少前多少个sql
  aggregate=yes|no    --是否单独处理同样内容的sql，在一个具有多个子游标的情况下，可能会有多个执行计划，就需要no
  insert=filename  List SQL statements and data inside INSERT statements.  生成sql插入DB
  sys=no           TKPROF does not list SQL statements run as user SYS.   是否显示sys的sql
  record=filename  Record non-recursive statements found in the trace file.  --包含所有非递归sql
  waits=yes|no     Record summary for any wait events found in the trace file.   列出等待事件
  sort=option      Set of zero or more of the following sort options:
    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
    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

 例如：

tkprof /u01/app/ora10/admin/ora10/udump/ora10_ora_9687.trc 11.txt explain=leo/leo sys=no sort=prsela,exeela,fchela