1、SQL TRACE说明：
| 参数类型 | 布尔型 |
| 缺省值 | false |
| 参数类别 | 动态 |
| 取值范围 | True|false |
2、类型
1）sql trace参数：alter system改变对全局进程影响，如果启用，要保证满足以下条件。
1．至少保证有25%的CPU idle。
2．为USER_DUMP_DEST 分配足够的空间。
3．条带化磁盘以减轻IO 负担。
alter session改变对当前对话产生影响。
2）dbms_system.set_sql_trace_in_session:可以指定跟踪特定的会话。
3、在使用 SQL_TRACE 之前，几个注意事项需要简单说明一下：
???? 初始化参数TIMED_STATISTICS
参数TIMED_STATISTICS 最好设置为True，否则一些重要信息不会被收集。
???? 设置MAX_DUMP_FILE_SIZE
该参数设置跟踪文件的大小限制，可以以操作系统块为单位设置；也可以以KB 或MB 为单位设置；如果跟踪的信息较多，可以干脆设置为unlimited。从9i 开始，该参数默认值为unlimited。
在session 级可以设置如下：
SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited;
Session altered.
记住前面的警告，你需要有足够的空间保存trace 文件，跟踪过程产生的trace 文件可能远远大于你的想象。
4、跟踪其他用户进程
在很多时候需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle 提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION 来完成。
SET_SQL_TRACE_IN_SESSION 过程要提供3 个参数：
SQL> desc dbms_system
…
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
—————– —————– —— ——–
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
…
通过查询v$session 可以获得SID、SERIAL#等信息。获得进程信息后，选择需要跟踪的进程，设置跟踪，具体如下：
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
———- ———- ——————————
8 2041 SYS
9 437 EYGLE
设置跟踪：
SQL> exec dbms_system.set_sql_trace_in_session(9,437,true)
PL/SQL procedure successfully completed.
…
可以等候片刻，跟踪 session 执行任务，捕获SQL 操作……
如果确定某个功能或模块存在问题，可以在此期间有意识地调用，以确保可以捕获问题代码。
…
停止跟踪：
SQL> exec dbms_system.set_sql_trace_in_session(9,437,false)
PL/SQL procedure successfully completed.
5、如果要对其他用户的参数进行设置，可能需要用到DBMS_SYSTEM 包中的另外一个过程
SET_INT_PARAM_IN_SESSION：
SQL> desc dbms_system
…
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
…
比如设置MAX_DUMP_FILE_SIZE 等参数，可以参考如下：
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
———- ———- ——————————
18 1605 EYGLE
SQL> begin
2 sys.dbms_system.set_bool_param_in_session(18, 1605, ‘timed_statistics’, true);
3 sys.dbms_system.set_int_param_in_session(18, 1605, ‘max_dump_file_size’, 2147483647);
4 sys.dbms_system.set_sql_trace_in_session(18, 1605, true);
5 end;
6 /
PL/SQL procedure successfully completed.
6、获取跟踪文件
1）以上生成的跟踪文件位于user_dump_dest 目录中，位置及文件名可以通过以下SQL 查询获得（alter session设置的情况适合）：
SQL> 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
/
TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/admin/hsjf/udump/hsjf_ora_1026.trc
2）也可以进入到user_dump_dest目录通过时间和进程号进行查找。
7、TKPROF进行格式化跟踪文件
1）TPKROF比较有用的一个排序选项是fchela，即按照elapsed time fetching 来对分析的结果排序
（记住要设置初始化参数TIME_STATISTICS=true），生成的.prf 文件将把最消耗时间的SQL 放在最前面显示。另外一个有用的参数就是SYS，这个参数设置为no 可以阻止所有以SYS 用户执行的SQL 被显示出来，这样可以减少分析出来的文件的复杂度，便于查看。
首先解释TKPROF 命令输出文件中各个列的含义。
（1）call：每次SQL 语句的处理都分成3 个部分。
???? Parse：这步将SQL 语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
???? Execute：这步是真正的由Oracle 来执行的语句。对于insert、update、delete 操作，这步会修改数据，对于select 操作，这步就只是确定选择的记录。
???? Fetch：返回查询语句中所获得的记录，这步只有select 语句会被执行。
（2）count:这个语句被parse、execute、fetch 的次数。
（3）cpu：这个语句对于所有的parse、execute、fetch 所消耗的CPU 的时间，以秒为单位。
（4）elapsed：这个语句所有消耗在parse、execute、fetch 的总的时间。
（5）disk：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
（6）query：在一致性读模式下，所有parse、execute、fetch 所获得的buffer 的数量。一致性模式的buffer 是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
（7）current：在current 模式下所获得的buffer 的数量。一般在current 模式下执行insert、update、delete 操作都会获取buffer。在current 模式下，如果在高速缓存区发现了有新的缓存足
够给当前的事务使用，则这些buffer 都会被读入缓存区中。
（8）rows: 所有SQL 语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select
语句，返回记录是在fetch 这步，对于insert、update、delete 操作，返回记录则是在execute这步。
2）对trace 文件使用TKPROF 工具进行分析。
tkprof tracefile outfile [explain=user/password] [options…]
一般来说，使用TKPROF 得到的输出文件中包含3 个部分。
???? SQL 语句本身。
???? 相关的诊断信息，包括CPU 时间、总共消耗的时间、读取磁盘数量、逻辑读的数量、以
及查询中返回的记录数目等。
???? 列出这个语句的执行计划。
$tkprof orcl_ora_14483.trc allan.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela
TKPROF: Release 9.2.0.4.0 – Production on Sun Dec 5 22:27:28 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.