Oracle 使用TRACE进行SQL性能分析-Mysql Tutorial-php.cn

设置sql_trace参数为true会对整个实例进行跟踪，包括所有进程：用户进程和后台进程，会造成比较严重的性能问题，生产环境一定要慎

设置sql_trace参数为true会对整个实例进行跟踪，包括所有进程：用户进程和后台进程，会造成比较严重的性能问题，生产环境一定要慎用。

一、根据相关事务或者sql 执行trace操作：

1）使用前需要注意的地方

1,初始化参数timed_statistics=true。允许sql trace 和其他的一些动态性能视图收集与时间（cpu，elapsed）有关的参数。一定要打开，不然相关信息不会被收集。这是一个动态的参数，也可以在session级别设置。

SQL>alter session set titimed_statistics=true

2,MAX_DUMP_FILE_SIZE跟踪文件的大小的限制，如果跟踪信息较多可以设置成unlimited。可以是KB,MB单位，9I开始默认为unlimited这是一个动态的参数，也可以在session级别设置。

SQL>alter system set max_dump_file_size=300

SQL>alter system set max_dump_file_size=unlimited

2）trace执行过程：

1. 启动SQL_TRACE：SQL> alter session set sql_trace=true;

2. 进行相关事务或者sql操作：SQL> select * from t;

3. 关闭SQL_TRACE：SQL> alter session set sql_trace=false;

也可以通过Oracle提供的系统包 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来实现。例如：首先从os上利用top命令找到当前占用cpu资源最高的一个进程的PID号；然后在数据库中根据PID号找到相应的sid和serial#。

SQL>execute dbms_system.set_sql_trace_in_session(sid,serial#,true);

SQL>execute dbms_system.set_sql_trace_in_session(sid,serial#,false);

二、获得当前生成trace文件的位置：

在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump；到了11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下。

也可以通过查询出trace文件所在的默认路径：SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'（BACKGROUND_DUMP_DEST）

如需修改：alter system set user_diagnostic_dest = 'd:\oracle\trace';

或者： select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

此外，也可以直接用如下SQL直接查出当前的trace文件名。

SELECT d.VALUE || '\' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc'

AS "trace_file_name"

FROM (SELECT p.spid

FROM v$mystat m, v$session s, v$process p

WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

(SELECT t.INSTANCE

FROM v$thread t, v$parameter v

WHERE v.NAME = 'thread'

AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

(SELECT VALUE

FROM v$parameter

WHERE NAME = 'user_dump_dest') d;

三、转换生成trace文件：

SQL_TRACE 生成最原始的trace文件的可读性比较差，所以通常我们使用tkprof 工具来处理trace文件。 Tkprof 工具是Oracle 自带的一个工具，用于处理原始的trace文件，它的作用主要是合并汇总trace文件中的一些项，规范化文件的格式，使文件更具有可读性。 Tkprof 是系统级别的，直接在系统下执行即可。

注意：tkprof 工具只能用在处理SQL_TRACE和10046事件产生的trace，其他事件如10053不能处理。

格式： tkprof tracefile outputfile [optional | parameters ]

C:/Users/Administrator/VIDI>tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc orcl_ora_3048.txt sys=no

参数和选项：

explain=user/password执行explain命令将结果放在SQL trace的输出文件中

sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句。设置为no后，trace文件具有更佳的可读性

sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序

sort_option选项：设置排序选项，可以用逗号分隔多个选项。默认是跟踪文件中发现的SQL顺序。

prscnt按解析次数排序

prscpu按解析所花cpu时间排序

prsela按解析所经历的时间排序

prsdsk按解析时物理的读操作的次数排序