ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中如何跟踪SQL或数据库操作 [final]

Oracle中如何跟踪SQL或数据库操作 [final]

原创 Linux操作系统 作者:tolywang 时间:2011-11-16 13:35:53 0 删除 编辑

 

一般生成的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文件分析:

参考  http://www.eygle.com/archives/2005/10/aeearaw_traceia.html 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-713434/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13167614