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

最新推荐文章于 2021-04-16 07:32:35 发布

cuixie2370

最新推荐文章于 2021-04-16 07:32:35 发布

阅读量172

点赞数

文章标签：数据库操作系统

一般生成的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任务