1    Sql_trace

Sql_trace 是oracle 提供的会话级跟踪工具。启动会话跟踪后oracle会将指定会话执行的所有SQL语句记录在一个trace文件中，这个trace文件记录是SQL语句真实的执行计划和各项性能数据。

1.1  Top命令找到最消耗资源的oracle进程

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                               

22139 oracle    15   0  2336 1120  804 R  0.7  0.1   0:00.07 top                                                                                   

 3223 root      15   0 38156 3944 3224 S  0.3  0.4   0:04.68 vmtoolsd                                                                              

21826 oracle    18   0  573m  18m  17m S  0.3  1.8   0:06.97 oracle                                                                                

    1 root      15   0  2072  632  544 S  0.0  0.1   0:01.09 init                                                                                   

2 root      RT  -5     0    0    0 S  0.0  0.0   0:06.43 migration/0       

1.2  查看sql_trace状态并得到参数 sid 和serial#，这两个值是会话的唯一标识

SQL< select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826 ;

       SID    SERIAL# SQL_TRACE

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

       145        195 DISABLED

1.3  开启跟踪指定会话

SQL< exec dbms_system.set_sql_trace_in_session(145,195,true);

PL/SQL procedure successfully completed.

SQL< select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826 ;

       SID    SERIAL# SQL_TRACE

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

       145        195 ENABLED

1.4  查看生成trace文件路径

Trace文件一般放在user_dump_dest路径下

SQL< show parameter dump

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/enm

                                                 oedu/ENMOEDU/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/enm

                                                 oedu/ENMOEDU/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/enm

                                                 oedu/ENMOEDU/trace

1.5  查找生成的trace文件

使用oradebug 按pid 找到trace文件

SQL< oradebug setospid 21826;

Oracle pid: 35, Unix process pid: 21826, image: oracle@ENMOEDU (TNS V1-V3)

SQL< oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_21826.trc

为了方便查找也可以为生成的trace文件增加标识符

SQL< alter session set tracefile_identifier=MY;

Session altered.

[oracle@ENMOEDU trace]$ ls -trl *_MY.trc

-rw-r----- 1 oracle oinstall 31965 Jan 19 02:11 ENMOEDU_ora_21826_MY.trc

1.6  关闭会话跟踪

SQL< exec dbms_system.set_sql_trace_in_session(145,195,false);

PL/SQL procedure successfully completed.

SQL< select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826;

       SID    SERIAL# SQL_TRACE

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

       145        195 DISABLED

1.7  使用tkprof格式化trace文件

[oracle@ENMOEDU trace]$ tkprof ENMOEDU_ora_21826_MY.trc ENMOEDU_ora_21826_MY.trc.txt

TKPROF: Release 11.2.0.3.0 - Development on Sun Jan 19 02:18:03 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

附：启用或关闭跟踪当前会话

SQL< alter session set sql_trace=true|false;

SQL< exec dbms_session.set_sql_trace(true|false);

1046诊断事件是sql_trace功能的扩展，主要增加了绑定变量和等待事件的跟踪。

2.1  跟踪级别

1046事件可设置的四个级别:

Level 1－与sql_trace记录内容相同

Level 4－level 1 + 绑定变量信息

Level 8－level 1 + 等待事件信息

Level 12－level 1 + Level 4 + Level 8

2.2  开启会话跟踪

Exec dbms_monitor.session_trace_enable(sid,serial#,waits,binds);

Exec dbms_monitor.session_trace_disable(sid,serial#,waits,binds);

waits表示是否记录等待事件,值为 true 或 false;

binds表示是否记录绑定变量,值为 true 或 false;

Exec  sys.dbms_system.set_ev(sid,serial#,10046,12,””);

附：设置当前会话跟踪事件

Alter session set events ‘10046 trace name context forever,level 12’;

Alter session set events ‘10046 trace name context off;

Explain 和 auto trace 可以对单条SQL语句分析。而Sql_trace 与 10046事件会将用户session中全部SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace 文件来了解用户会话中所有SQL语句真实的执行计划和各项性能信息，方便我们全面直观分析系统整体运行状况。