sql跟踪及tkprof使用，sql跟踪tkprof使用
简述
在oracle数据库中，awr是关于数据库系统整体的负载情况和运行情况的报告。而当系统负载都显示正常，而客户端执行某些动作响应很慢，或者某些终端连接的会话执行缓慢或异常时，就需要用到会话级别的跟踪了。
常见方法概述
Session级别跟踪的方法有许多，比如当前会话的跟踪，可以执行命令
Alter session set sql_trace=true;
Alter session set sql_trace=false;
或者使用10046事件
Alter session set events ‘10046 trace name context forever,level 12’;
Alter session set events ‘10046 trace name context off’
其中level 12是可选级别。
Level 1是指标准的sql跟踪，与设置sql_trace效果相同。
Level 4是指在标准的基础上增加绑定变量信息。
Level 8是指在标准的基础上增加等待事件信息。
Level 12是指在标准的基础上同时增加绑定变量信息和等待事件信息。
以上例子是在session级别进行跟踪，若环境中需要在整个实例级别进行跟踪，则直接将以上命令中的session更改为system即可实现。但系统级别的跟踪将消耗大量的系统资源，因此，若非性能诊断需要，不要开启在系统级别的sql_trace。
我们要用的方法
方法有很多，会用一种就行，下面我们说下我推荐的一种方法。
首先，可以设置trace文件标志，使我们的trace文件更容易找出来。
Alter session set tracefile_identifier=’wjf’;
之后打开计时
Alter system set timed_statistics=true;
然后查找到要跟踪会话的sid与serial#,
Select sid,serial#,osuser,machine from v$session;
找到sid与serial#后，就可以执行跟踪了，比如sid=22，serial#=22;
执行以下命令
Exec dbms_monitor.session_trace_enable(22,22,waits=>true,binds=>true);
Exec dbms_monitor.session_trace_disable(22,22);
其中waits=>true跟binds=>true想必也不用解释了。
追踪文件处理（tkprof）
追踪后的trc文件可以在DIAGNOSTIC_DEST目录下找到，但是直接追踪产生的trc文件可读性并不好，需要借助一个oracle提供的工具处理以下，这个工具就是tkprof。
例如我们此处生成trc文件名称为orcl_ora_1234_wjf.trc.
则我们需要在系统级别，即退出sqlplus，在shell或cmd下执行以下命令
Shell>tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt
这样就能生成可读性较强的追踪文件进行分析了。Tkprof工具必须输入的两个参数也就是一个输入文件，一个输出文件而已。
但tkprof还有一些其他参数，很多时候需要利用其他参数来生成文件，从而更方便找出问题。
Tkprof工具的标准语法
tkprof filename1filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]
Tkprof参数介绍
介绍几个自我感觉比较常用的参数
Waits=yes|no：是否包含等待事件信息。
Print=n:设置显示多少行sql。比如你设置了排序信息，可以只查看top 10的sql，就可以设置print=10了。
Sys=yes|no:设置是否包含sys用户所发布的sql。主要是为了启动或禁止显示用户为执行自己的sql所产生的递归sql，默认为yes。
Sort=option：设置生成sql根据指定选项进行排序。
prscnt number oftimes parse was called
prscpu cpu timeparsing
prsela elapsedtime parsing
prsdsk numberof disk reads during parse
prsqry numberof buffers for consistent read during parse
prscu numberof buffers for current read during parse
prsmis numberof misses in library cache during parse
execnt numberof execute was called
execpu cputime spent executing
exeela elapsedtime executing
exedsk numberof disk reads during execute
exeqry numberof buffers for consistent read during execute
execu numberof buffers for current read during execute
exerow numberof rows processed during execute
exemis numberof library cache misses during execute
fchcnt numberof times fetch was called
fchcpu cputime spent fetching
fchela elapsedtime fetching
fchdsk numberof disk reads during fetch
fchqry numberof buffers for consistent read during fetch
fchcu numberof buffers for current read during fetch
fchrow numberof rows fetched
userid useridof user that parsed the cursor
其他还有一些参数，具体可以查看联机文档中的performance tunning guide。
Tkprof举例
所以，一个比较正常的tkprof语句可能是这个样子的
tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt waits=no sys=no sort=(prscpu,execpu,fchcpu) print=10;
就这样了，就像生成awr报告一样，生成文件永远是最简单的，能把生成的报告读的多透彻才是见功底的东西。在此，我仅仅把生成报告的过程做个记录，至于怎么读，慢慢再说。
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from dual;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- -......余下全文>>
查看归档模式
conn /as sysdba
archive log list
如果数据库为归档模式的话，可以通过logmnr来进行挖掘日志文件查看这些信息的。如果是非归档模式。对不起无法查看了
开启后台进程跟踪，
设置参数(initsid.ora)
.backgroudn_dump_dest=目录名 -- 指定根踪文件存放的路径
.user_dmup_test=目录名 --指定用户信息跟踪文件的存放路径
.用户的跟踪文件(.trc), 用TKPROF 来格式化用户跟踪文件
SQL 语句跟踪即可。
.imed_statistics=true; --设置启用 sql_trace =true;
.user_dump_dest=目录 --指定跟踪文件的存放路径
.max_dump_file_size=5M --指定跟踪文件最大尺寸
.SQL_TRACE=TRUE;
.动态改变 ：alter session set sql_trace=true;
或者打开生成的跟踪文件:
默认在..\oralce\admin\user\udump\*.trc，由于oralce 生成的*.trc 直接打开格式不规格，看得很累，可以用tkprof gk 来格式化 ：c:\tkprof ora00001.trc a.txt