ITPub博客

首页 > 数据库 > Oracle > 会话级SQL跟踪

会话级SQL跟踪

Oracle 作者:羽化残虹 时间:2014-03-01 12:17:49 0 删除 编辑

会话级SQL跟踪

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);

 

 

2    10046诊断事件

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

 

2.1  跟踪级别

 

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

Level 1-与sql_trace记录内容相同

Level 4level 1 + 绑定变量信息

Level 8level 1 + 等待事件信息

Level 12level 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;

 

 

3    小结

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

 

 

 

DBA_建瑾

2014.1.19

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

请登录后发表评论 登录
全部评论

注册时间:2013-08-24

  • 博文量
    69
  • 访问量
    263626