oracle+用什么追踪,Oracle中如何跟踪SQL或数据库操作 [final]

一般生成的trace文件格式为 imb_ora_10552.trc, 即 数据库名+ora+SPID .

其中spid是OS process ID .

1.    用SQL_TRACE

在数据库级别上设置TIMED_STATISTICS为True。SQL TRACE相当于10046 事件

的Level 1:  启用标准的sql_trace功能.

A.  在全局中使用:  设置在spfile中的参数sql_trace=true ;  会导致所有进程的活动

被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，

所以在生产环境中要谨慎使用  (除非特殊情况,  一般较少使用) .

B. 在session级使用:

启用当前session跟踪:    SQL> alter session set sql_trace=true;

追踪一段时间.......

结束跟踪:    SQL> alter session set sql_trace=false;

这里的启动和结束跟踪之间要隔一段时间。

找到本session对应生成的trace文件 。

SELECT    d.VALUE

|| '/'

|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

FROM (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 AND p.addr = s.paddr) p,

(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,

(SELECT VALUE

FROM SYS.v$parameter

WHERE NAME = 'user_dump_dest') d  ；

2.    用DBMS_SUPPORT包或DBMS_SYSTEM包跟踪其他用户session :

在数据库级别上设置TIMED_STATISTICS为True。

查询v$session视图,获取进程信息

SQL> select sid,  serial#,username from v$session;

或者通过spid查询sid, serial#  :

SQL> select  b.*, a.*   from v$process a, v$session b

where a.addr = b.paddr and a.spid in (1914, 19140)

启用相关session进程sql_trace

SQL> exec dbms_system.set_sql_trace_in_session(7,284,true)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(11,214,true)

PL/SQL procedure successfully completed.

等候一段时间,关闭sql_trace

SQL> exec dbms_system.set_sql_trace_in_session(7,284,false)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(11,214,false)

PL/SQL procedure successfully completed.

检查trace文件或使用 tkprof  xxx.trc   xxx.txt  sys=no ...格式化trace文件.

3.    使用10046 event 跟踪 .

在数据库级别上设置TIMED_STATISTICS为True。

使用10046 event的方法大致如下(当前session)：

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

your sql statement...

alter session set events '10046 trace name context off';

其中的level有1，4，8，12几个选项，其中1相当于设置SQL_TRACE=TRUE之后

的结果，4包括1的结果和绑定变量的实际值，8包括1的结果和等待事件的情况，

12则同时包含1的结果，绑定变量的实际值和等待事件情况，所以可以说level 12

是最为详细的trace了。

备注:

10046事件是oracle提供的内部事件，是对sql_trace的增强，可以设置以下4个级别：

Level 1:启用标准的sql_trace功能，等价于sql_trace

Level 4:等价于Level 1+绑定值

Level 8: 等价于Level 4+等待事件跟踪

Level 12: 等价于Level 1+level 4 + level 8

使用10046 event 跟踪其他用户session :

对其他用户session设置  dbms_system.set_ev

说明：5个参数   sid/serial#/ev/level/username

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

SID    SERIAL#

---------- ---------- -----

113         227

292        314

189        2280

或者通过spid查询sid, serial#  :

SQL> select  b.*, a.*   from v$process a, v$session b

where a.addr = b.paddr and a.spid in (1914, 19140) ;

执行跟踪：

exec dbms_system.set_ev(113,227,10046,8,'');

结束跟踪：

exec dbms_system.set_ev(113,227,10046,0,'');

4. 使用 tkprof 命令示例：

“tkprof tracefile outputfile explain=userid/password"

在操作系统oracle用户下，键入“tkprof”，会有详细的命令帮助。分析后的输出

文件 outputfile中，有每一条PL/SQL语句的“执行计划”、CPU占用、物理读次数、

逻辑读次数、执行时长等重要信息。 根据输出文件的信息，我们可以很快发现应

用中哪条PL/SQL语句是问题的症结所在。

常用的使用方式:

$ tkprof    xxx.trc    xxx.txt     sys=no   explain=userid/password

5.  tkprof 得出的output文件分析：

对Tkprof命令输出的解释,  首先解释输出文件中列的含义：

CALL：每次SQL语句的处理都分成三个部分

Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用

到的表、列以及其他引用到的对象是否存在。

Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步

会修改数据，对于select操作，这步就只是确定选择的记录。

Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。

COUNT:这个语句被parse、execute、fetch的次数。

CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。

ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。

DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正

在从缓存中读取的数据而不是从磁盘上读取的数据。

QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。

一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓

存实际上在头部存储了状态。

CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、

update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓

存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。

ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对

于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则

是在execute这步。

-----------------

问题及解决措施

-----------------

解析数字太大

应该增大SHARED_POOL_SIZE或使用绑定变量

DISK磁盘读(物理读)取量太高

没有使用索引或根本就不存在索引

query和/或current列值(内存读取, 逻辑读)太高

索引位于低基数的列上(由一个值组成了表中大部分记录的列；比如y/n字段)。删除/限制

索引，或使用直方图或位图索引或许可以提高性能。表连接顺序或连接索引的顺序不好

也会发生这个情况.......

分析所需要的时间太多

可能是开放游标的数量有问题

EXPLAIN PLAN里某一行语句要处理的行数相对于其他行语句而言太多

这可能表明有一个索引对唯一键(一个列上的唯一值)进行了较差的分布。

在分析期间库缓存里Misses值大于1

这表明需要重载这条语句。可能需增大init.ora文件中的SHARED_POOL_SIZE值，或者执行

一次较好的共享SQL任务

6.  原始trace文件内容分析 得出的output文件分析：

参考