1.设置sql trace
(1)实例级
初始化参数据SQL_TRACE=TRUE
(2)对话级
ALTER SESSION SET SQL_TRACE=TRUE
DBMS_SESSION.SQL_TRACE(SQL_TRACE)
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,SQL_TRACE);
(3).trc文件存放的位置
查看初始化参数:用户跟踪文件user_dump_dest
2.设置时间统计(这样就可以统计每个sql执行的时间)
alter system set timed_statistics=true scope=both;
3.tkprof的使用
tkprof可对.trc文件进行分析,并生成一个分析文件.并且在分析.trc文件,生成分析文件时按各种选项进行排序,其中比较有用的一个排序选项是fchela，即按照elapsed time fetching来对分析的结果排序（记住要设置初始化参数time_statistics=true），生成的.prf文件将把最消耗时间的sql放在最前面显示。另外一个有用的参数就是sys，这个参数设置为no可以阻止所有以sys用户执行的sql被显示出来，这样可以减少分析出来的文件的复杂度，便于查看。
举例:
(1)设置跟踪SID,SERIAL#O为12.73
exec dbms_system.SET_SQL_TRACE_IN_SESSION(12,73,true);
(2)查询生成的.trc文件号
select spid from v$process where addr = (select paddr from v$session where sid=12);
(3)使用tkprof生成分析文件
c:\>tkprof e:\oracle\admin\rainny\rainny_ora_4188.trc c:\rainny.prf aggregate=yes sys=no sort=fchela(此例中将执行最耗时的sql放在分析文件的开头)
(4)打开.prf文件
可用ultraedit打开查看生成的分析文件
1．Tkprof是一个分析ORACLE跟踪文件并且产生一个更加人性化清晰的输出结果的可执行工具。C:\oracle\ora92\bin\tkprof.exe
2. tkprof 全称
参考信息
3. 基本的使用步骤
1) SQL> alter system set timed_statistics=true;
2) 用户级自跟踪：
SQL>ALTER SESSION SET SQL_TRACE=TRUE;
SQL>ALTER SESSION SET SQL_TRACE=FALSE;
用户级DBA跟踪：(例如sys跟踪test，需要用sysdba登录)
a). SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s
where s.USERNAME='COLM' ;
b). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,true);
c). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,false);
ps：9为SID，7为SERIAL#
3) C:> cd C:\oracle\admin\COLM\udump
4) C:\oracle\admin\COLM\udump> tkprof colm_ora_2056.trc trace.txt print=100 record=sql.txt sys=no
5) 查看trace.txt文件
范例：
*******************************************************************************
SELECT *
FROM
col_case
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62
需要调整的语句符合以下几点：
(1).CPU占用过多
(2).Parse,Execute,Fetch花费太多时间
(3).DISK读取太多，query/current(SGA)中数据块读取太少
(4).访问许多块，只返回2行
首先解释输出文件中列的含义：
CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT:这个语句被parse、execute、fetch的次数。
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。
sort参数是用来指定输出的SQL是按什么数据来排序(如cpu时间或elapsed时间,详见tkprof的使用参数说明)
在report.txt中有关于每个SQL的parse/execute/fetch/disk read/buffer get/cpu time/执行计划(包括每一步运行时的行数),
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16978544/viewspace-691838/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16978544/viewspace-691838/