Oracle SQL跟踪与分析-优快云博客

本文链接：https://blog.youkuaiyun.com/wind509/article/details/6437890

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