SQL性能的度量 - 会话级别的SQL跟踪sql_trace

最新推荐文章于 2024-09-23 14:16:45 发布

cldh1492

最新推荐文章于 2024-09-23 14:16:45 发布

阅读量144

点赞数

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

通过初始化参数sql_trace开启会话跟踪，通过oradebug命令输出到跟踪文件，提供更加详细的信息。但一般还需要借助tkprof工具格式化跟踪文件便于阅读。

在使用sql_trace之前，有两个初始化参数可能需要注意一下，不过从9i开始都是默认按以下设置的。

初始化参数timed_statistics最好设为true，否则一些重要信息不会被收集

show parameter timed_statistics;

NAME TYPE VALUE

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

timed_statistics boolean TRUE

对跟踪文件的大小限制，可以KB、MB为单位设置，或者干脆设置为unlimited

show parameter max_dump_file_size;

NAME TYPE VALUE

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

max_dump_file_size string UNLIMITED

启用自己的会话跟踪

alter session set sql_trace = true;

或

exec dbms_session.set_sql_trace(true);

跟踪指定的会话

exec dbms_system.set_sql_trace_in_session(sid, serial#, true|false);

如果要对其它用户的会话进行设置，可以用dbms_system的另外两个过程

exec dbms_system.set_bool_param_in_session(sid, serial#, 'timed_statistics', true);

exec dbms_system.set_int_param_in_session(sid, serial#, 'max_dump_file_size', 2147483647);

生成自己会话的跟踪文件

oradebug setmypid;

oradebug tracefile_name;

生成指定会话的跟踪文件

oradebug setospid ;

oradebug tracefile_name;

跟踪文件的命名规则：_ora_.trc，其中sid是实例名，spid是会话对应的操作系统服务器进程ID。

跟踪文件的路径由初始化参数user_dump_dest指定

show parameter user_dump_dest;

NAME TYPE VALUE

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

user_dump_dest string c:\oracle\diag\rdbms\mes\mes\trace

以下查询可以找到会话的sid、serial#、pid、spid几个参数

select s.username, s.sid, s.serial#, p.pid, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username is not null;

执行一些操作后关闭跟踪，观察跟踪文件的内容。跟踪文件的内容比较专业，不宜看懂，可以通过操作系统命令行工具tkprof格式化跟踪文件

tkprof tracefile outputfile [explain=] [table=] [print=] [insert=] [sys=] [sort=]

主要参数含义：

sys：yes或no，默认yes，指定输出文件中是否包含以SYS用户运行的sql语句。

record：指定一个路径下的文件，用来生成在跟踪文件中找到的所有非递归的SQL。如：record=d:\test.log。

aggregate：no或yes，默认no，指定tkprof是否将同样文本内容的sql聚合处理，如执行了十次select * from a，如果指定这个参数为no，那么产生的输出文件会有十个这样语句的执行信息，如果指定的是yes，那么tkprof会把这十次的执行信息汇总显示。

sort：指定tkprof输出文件里sql语句按照什么排序，默认是按照执行的先后顺序排序的，可以指定它按照其他方式排序，比如磁盘读取数，CPU时间等。这个参数最经常用的方式是：sort=prsela,exeela,fchela,也就是按照解析、执行、提取，这三个值加起来就是响应时间，tkprof会根据三个值的和进行排序，即按照响应时间排序。

print：经常搭配sort参数一起使用，用来指定tkprof输出sql语句的数量。这两个参数搭配使用起来就比较妙，如想知道一个跟踪文件里响应时间排前十的SQL，那么你就可以sort=prsela,exeela,fchela print=10来搭配使用。

explain：为每一个SQL提供一个执行计划。使用方法是explain=用户名/密码。

wait：指定输出文件中是否包含等待事件，默认为包含。

如对于11g的跟踪路径

tkprof c:\oracle\diag\rdbms\mes\mes\trace\mes_ora_2372.trc d:\2372.txt sys=no sort=prsela,exeela,fchela record=d:\2372.log

而对于10g

tkprof c:\oracle\product\10.2.0\admin\mes\udump\mes_ora_3888.trc d:\3888.txt sys=no sort=prsela,exeela,fchela record=d:\3888.log

对于linux系统，在操作系统层面上利用top命令找到当前占用CPU资源最高的Oracle进程的ID号，然后根据该ID号找到相应会话的sid和serial#