前言
sql_trace 是我在工作中经常要用到的调优工具相比较statspack 我更愿意用这个工具
因为数据库慢原因的%以上是由于sql问题造成的statspack没有sql的执行计划显示没有它直观方便对想要针对性不强
介绍数据库调优需要经常会用到的工具可以很精确地跟抓取相关session正在运行的sql再通过tkprof分析出来sql的执行计划等相关信息从而判断那些sql语句存在问题
统计如下信息（摘字官方文档）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each commit and rollback
使用
使用前需要注意的地方
初始化参数timed_statistics=true 允许sql trace 和其他的一些动态性能视图收集与时间（cpuelapsed）有关的参数一定要打开不然相关信息不会被收集这是一个动态的参数也可以在session级别设置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟蹤文件的大小的限制如果跟蹤信息较多可以设置成unlimited可以是KBMB单位I开始默认为unlimited这是一个动态的参数也可以在session级别设置
SQL>alter system set max_dump_file_size=
SQL>alter system set max_dump_file_size=unlimited
USER_DUMP_DEST指定跟蹤文件的路径默认路径实在$ORACLE_BASE/admin/ORA_SID/udump这是一个动态的参数也可以在session级别设置
SQL>alter system set user_dump_dest=/oracle/trace
数据库级别
设置slq_trace参数为true会对整个实例进行跟蹤包括所有进程用户进程和后台进程会造成比较严重的性能问题生产环境一定要慎用
SQL>alter system set sql_trace=true;
Session级别
当前会话
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他会话
通过oracle提供的系统包 DBMS_SYSTEMSET_SQL_TRACE_IN_SESSION来实现
SQL>execute dbms_systemset_sql_trace_in_session(sidserial#true);
SQL>execute dbms_systemset_sql_trace_in_session(sidserial#false);
注
sidserial#从v$session视图中获得
DBMS_SYSTEM包里还可以对其他用户的参数（如timed_statisticsmax_dump_file）进行设置在这不做介绍了很少用到想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件后我们要用tkprof他进行格式化通过sql语句快速定位到相应的trace文件
Tkprof
tkprof的目的是将sql trace生成的跟蹤文件转换成用户可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
参数和选项（这里只介绍最常用的也是最实用的）
explain=user/password执行explain命令将结果放在SQL trace的输出文件中
sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句
sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序
sort_option选项
prscnt按解析次数排序
prscpu按解析所花cpu时间排序
prsela按解析所经历的时间排序
prsdsk按解析时物理的读操作的次数排序
prsqry按解析时以一致模式读取数据块的次数排序
prscu按解析时以当前读取数据块的次数进行排序
execnt按执行次数排序
execpu按执行时花的cpu时间排序
exeela按执行所经历的时间排序
exedsk按执行时物理读操作的次数排序
exeqry按执行时以一致模式读取数据块的次数排序
execu按执行时以当前模式读取数据块的次数排序
exerow按执行时处理的记录的次数进行排序
exemis按执行时库缓沖区的错误排序
fchcnt按返回数据的次数进行排序
fchcpu按返回数据cpu所花时间排序
fchela按返回数据所经历的时间排序
fchdsk按返回数据时的物理读操作的次数排序
fchqry按返回数据时一致模式读取数据块的次数排序
fchcu按返回数据时当前模式读取数据块的次数排序
fchrow按返回数据时处理的数据数量排序
注
这些排序中我经常用到的是fchdskfckchela fchqry因为有问题的sql一般都是大的查询造成的当然更新插入删除时也会存在全表扫描这就需要:exedskexeqryexeela等选项根据具体情况具体分析
Cpu时间和Elapsed时间都是以秒为单位而且两个值基本上一样但我比较常用elapsed他是反映的用户相应时间从运行sql到用户得到结果的时间会更实际些
tkprof输出文件各列的含义（理解下面的含义对我们快速定位问题很有帮助）
parse:
将sql语句转换成执行计划包括检查是否有正确的授权需要到得表列及其他引用到得对象是否存在这些信息分别存在v$librarycachev$rowcache
execute
oracle实际执行的语句如insertupdatedelete这些会修改数据对于select操作这部只是确定选择的行数
fetch
返回查询获得的行数只有执行select会被收集
Count
这个语句被parseexecutefetch的次数的统计
Cpu
这个语句所有的parseexecutefetch所用的cpu总的时间以秒为单位如果TIMED_STATISTICS 关闭的话值为
Elapsed
这个语句所有的parseexecutefetch所消耗的总的时间以秒为单位如果TIMED_STATISTICS 关闭的话值为
Disk
这个语句所有的parseexecutefetch从磁盘上的数据文件中读取的数据块的数量
Query
在一致性读的模式下这个语句所有的parseexecutefetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的相当于执行计划里的consistent gets）
Current
在current模式下这个语句所有的parseexecutefetch所获取的buffer数量一般是current模式下发生的delectinsertupdate的操作都会获取buffer
Rows
语句返回的行数不包括子查询中返回的记录数目对于select语句返回在fetch这步对于insertdeleteupdate操作返回记录是在execute这步
分析
我一般的思路步骤是
先找磁盘多的sq l(sort= fchdsk )意味着全表扫描找运行时间长的(sort= fchela)意味着sql可能写的不好或磁盘逻辑读较多找出一致性读较多的(sort= fchqry)当表不是很大的时候（可能全部缓存住了）没有发生磁盘读但不意味着不需要建立索引或者sql需要优化找出当前模式从缓沖区获得数据的数量(sort=exedskexeelaexeqry)这些主要集中在dml语句里的操作看是否有必要优化sql或建立索引之所以排序是为了在sql很多的时候快速定位sql如果sql比较少的话就没必要排序了但我们要有分析问题的思路
举例
我自己建立了一个表
create table t (id int);
begin
for v in loop
insert into t values(v);
end loop
commit;
end;
下面是sql_trace所抓到得sql
不正常状态
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache during parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先这是一个select语句它走了全部扫描
磁盘读（）和逻辑读（）都很多
运行了次（Execute）分析了次（Parse）一共用了将近秒（elapsed）
我只是选择表的一行的数据的结果就发生这么大的成本很显然是全表扫描的结果造成的
正常状态
在做跟蹤前我为这个表建立了一个索引
Create index t on t(id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache during parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NONUNIQUE)
*******************************************************************************
同样的语句
它走了索引物理读 这个其实是开始读索引时需要第一次读入的以后运行就没有了
逻辑读（平均这个sql一次个逻辑读）
同样运行了次（Execute）
分析了次（Parse） 运行次数越多分析次数越少越好一共只用了秒（elapsed）
可以看出前后很大的差距
