Oracle Trace tkprof

tkprof主要是用来解释trace文件内容，把原始的trace文件转化为容易理解的文件。

tkprof格式化trace文件

它的语法格式如下：
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

参数和选项：
explain=user/password: 执行explain命令将结果放在SQL trace的输出文件中
table=schema.table: 指定tkprof处理sql trace文件时临时表的模式名和表名
insert=scriptfile: 创建一个文件名为scriptfile的文件，包含了tkprof存放的输出sql语句
sys=[yes/no]: 确定系统是否列出由sys用户产生或重调的sql语句
print=number: 将仅生成排序后的第一条sql语句的输出结果
record=recordfile: 这个选项创建一个名为recorderfile的文件，包含了所有重调用的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	按返回数据所经历的时间排序（将最耗时的sql放在最前面）
fchdsk	按返回数据时的物理读操作的次数排序
fchqry	按返回数据时一致模式读取数据块的次数排序
fchcu	按返回数据时当前模式读取数据块的次数排序
fchrow	按返回数据时处理的数据数量排序

tkprof实例

tkprof /d01/VIS/apps/tech_st/11.2.0/admin/PJSIT_fmssitdb1/diag/rdbms/pjsit/PJSIT/trace/PJSIT_ora_9372010_ERPADMIN_CR26952884.trc  /pjebs/sit/temp/2/PJSIT_ora_9372010_ERPADMIN_CR26952884.txt  sort='(prsela exeela fchela)' explain=apps/apps sys=no

实例分析

Example.txt是对一段查询sql做trace，截取格式化后的部分文本文件；文件里内容如下：

其中：

1. SQL语句执行情况总览，主要看elapsed处理时间（单位秒）

参数说明如下：
call：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在；
Execute：这步是真正的由Oracle来执行语句，对于insert、update、delete操作，这步会修改数据，对于select操作，这步只是确定选择的记录；
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
count:这个SQL语句被parse、execute、fetch的次数；
cpu:这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位；
elapsed:这个语句所有消耗在parse、execut、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这步。

可以按照下述方法根据数据指标分析sql的执行效率：
A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
B、Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse
C、rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
F、cpu　Or　elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化
G、执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

2. 运行环境信息；

第一行表示发生在解析的硬解析数量，如果是软解析则Misses in library cache during parse将为0；本例中是做的软解析；
第二行表示优化模式是：ALL_ROWS (即CBO优化方式)

3. 行源操作，查看cost值以及关联关系

Trace文件中我主要关注的除了SQL语句执行情况中的elapsed SQL总耗时时间以外，主要关注点就是该部分数据。
a. 关注其中的cost值和表之间的关联情况。Cost值和执行计划中的cost值一样，值越大，说明耗时越长，这是需要注意有可能存在优化项的地方；
b. 另一点，就是看表关联之间是否存在TABLE ACCESS FULL全表扫描(下图是截取其他某trace文件)，全表扫描可视情况通过建立适当的索引来优化；在sql语句中，我们也可能使用了某些不必要的视图，这些视图对应的sql查询本身部分就是要进行全表扫描，这种情况下可考虑放弃视图，使用基表；
如下图所示，是一个非常大的查询sql中，其中使用视图：org_organization_definitions ，结果查看trace文件发现该视图后台sql走了全表扫描~
处理方法：将视图换成了基表查询；(详见下述例子“视图换基表的实例”)

视图换基表的实例:

知道一个库存组织的id，要获取对应的code值，我们可以选择使用视图：org_organization_definitions ood
或者使用基表：mtl_parameters ，虽然都可以得到正确的code值，但是在执行效率上它们是有很大差别的；
使用视图，查看执行计划：

SELECT organization_code FROM org_organization_definitions ood WHERE organization_id = 736;

使用基表，查看执行计划：

select organization_code from mtl_parameters where  organization_id = 736;

在一个很大的SQL查询语句中，尽量避免使用多余的视图，如果直接使用基表就能获取的信息尽量使用基表。
任何对视图的查询，Oracle实际上都是转换为视图SQL语句的查询；

4. 执行计划

这是sql运行的实际执行计划，使用trace文件获取的执行计划比直接在pslql按F5获取的执行计划更加准确和靠谱。这部分我关注的不是很多~
5) 等待事件

程序运行中的等待事件及对应的等待时间。
Sql运行的时候，有的会出现等待事件，这些等待事件会耗去相应时间。可以根据等待事件来判断sql的运行，等待事件包含的内容非常多，属于底层的东西，做性能优化，后面不可避免的了解底层的相关机制，加美推荐读物《Oracle wait interface》。

目前来说，针对sql的sql_trace文件，可以通过查看排在最前面的耗时sql，看对应的执行计划，是否存在全表扫描，全表扫描很耗时，主要是针对其进行索引优化；