oracle如何使用trace跟踪文件，如何使用tkprof工具

浏览数：42 / 时间：2015年06月12日

oracle如何使用trace跟踪文件，以及如何使用tkprof工具读取trace文件

在这里首先要解释一下什么叫trace文件？
Trace file（追踪文件)是以trc为后续的文本文件,它记录了各种sql操作及所消耗的时间等,根据trace文件我们就可以了解哪些sql导致了系统的性能瓶颈，进而采取恰当的方式调优.
当我们利用工具sqlplus、plsql developer等工具连接oracle数据库，实际上会产生一个会话（session），session中我们的所有操作都会记录到trace文件。

SQL>alter session set sql_trace=true;      --------设置session的sql_trace值为true，这样才可以用trace工具，
SQL>show parametersql_trace;               --------查询sql_trace是否以及设置为true
SQL>alter system set timed_statistics=true;     -------此sql可以不输入
SQL>select id from lok.stu2;
-----------------------
id
1001
1002
1003
1004
1005
1006
-----------------------

SQL>select a.spid from v$process a,v$session b where a.addr=b.paddr and b.audsid=userenv(‘sessionid‘);       -----查询当前会话spid号为5744
   ----------
   5744

接下来我们要在查找trace文件在我们电脑的位置,trace 的默认路径
SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘user_dump_dest‘
------------------
${oracle_home}\admin\orcl\udump
我电脑的位置是D:\oracle\product\10.2.0\db_1\admin\orcl\udump，在该文件夹下找到orcl_ora_5744.trc文件，注意‘5744’是我们刚才查到的spid号，‘5744’是指我们当前连接到oracle的session的spid号，实际上也只是一个编号而已，每次连接都会变的。

--trace文件的内容很难读懂，所以我们可以用tkprof工具转化为简单的阅读文件，注意在sql的模式下不能用tkprof指令的，但可以打开cmd命令窗口使用。
c:\>tkprof D:\oracle\product\10.2.0\db_1\admin\orcl\udump\orcl_ora_5744.trc output=F:\orcl_trc_5744.txt;

打开orcl_trc_5744.txt，其中一部分内容如下：
select owner,count(*)
from
all_objects group by owner
call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.07       0.06          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      1.33       1.29          0      74404          0          26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        5      1.40       1.36          0      74404          0          26

此处看到查询的三个主要阶段：
×PARSE阶段：这一阶段oracle在共享池（软分析）中查找该查询，并为他创建一个新的计划（硬分析）
×EXECUTE阶段：这一阶段oracle完成查询的open或execute工作，对于select来说，此阶段将有很多次为空，而对于update来说，所有的工作都在此阶段完成。
×FETCH阶段：对于select来说，大部分工作在此阶段完成并可见，但象update那样的语句将显示没有任何工作（不用从update进行fetch）。
本节中列标题的意义：
×call：是parse、execute、fetch或者total之一，仅表示正在查看的查询处于哪一阶段。
×count：事件出现多少次，他是一个很重要的数，下面将看如何解释这个值。
×cpu：按cpu秒计算，这阶段执行查询要花多长的时间。
×elapsed：如墙壁上时钟所测量的，执行此查询要花多少时间，若启用timed_statistics，则将其填值。
×disk：执行查询时需要多少次对磁盘的屋里i/o。
×query：在一致读模式下要处理多少块，他包括从回滚段读的块数。
×current：在current模式下要读多少块，在current模式而不是一致读模式下，只要存在块，就对其检索。通常，在查询开始时，只要块存在，就为该查询检索块，在select期间，由于读数据字典，可以看到current模式下的检索，为寻找一个表的区域信息进行完全扫描（我们需要现在的信息，而不是一致读模式下的信息）。在修改期间，为了写块，我们将在current模式下访问他们。
×rows：多少行受到哪个处理阶段的影响，select将在fetch阶段显示他们，update将在execute阶段显示多少行收到影响。
这个报表的重要线索或事实如下：
在执行数大于1时，一个高的（接近100%）分析数与执行数之比－－在此用分析语句的次数除以执行语句的次数。若比值为1，则每次执行这个查询就分析他，这需要进行修正，我们希望该比值接近0。理想情况下，分析数为1，而执行数远大于1。如果看到一个大的分析数，则意味着对这个查询执行了很多次软分析。回顾前面的部分，就可知道这急剧地降低了可伸缩性，即使在单用户会话时也会影响运行性能。您应该保证，每次会话中查询只分析了一次，并重复执行他。
对所有或几乎所有sql的一次执行行数－－如果有一个tkprof报表，在此报表中所有的语句仅执行一次，您可能没有使用绑定变量。在实际应用程序跟踪时，我们只能期望很少的独特sql，同样的sql应该执行多次。太多独特的sql意味着您没有正确地使用帮定变量。
在cpu时间与经过的时间之间的大差异－－这意味着您花了大量的时间等待某些事情。如果花了一个cpu秒来之行，但他要求10秒壁钟指示的时间，他意味着您花了90%的运行时间等待一个资源。这个等待可以是任何原因，例如，被另一个会话阻塞的修改可能有很大的经过的时间（与cpu时间相比），执行大量屋里磁盘i/o的sql查询有大量等待i/o的时间。
较长的cpu或经过的时间－－这是表示最容易得到的查询。如果您能是他们运行更快，则您的程序将运行更快。
一个高的（fetch count）/所获得行数比－－在此取fetch调用之数与所获得行数（例子中为26）。如果这个数接近1，且行数远大于1，则应用程序不执行大批量取数操作，每种语言API有能力完成这个功能，即一次取很多行。如果您没有利用这个能力进行批量取，您将花费很多的时间在客户端与服务器之间来回往返。这个过多的来回转换除了产生拥挤的网络状况之外，比一次调用获得很多行要慢得多。比如在pl/sql中可以通过select into 中的bulk collect来返回多行。
非常大的磁盘数－－这很难估计。然而如果disk count=query + current mode block count满足的话，则所有的块从磁盘读，我们希望，如果同一个查询在此执行，有些块将可在sga中找到。应该考虑将一个大的瓷盘数作为一个“报警”标志。使sga优化大小，或研究查询，以开发一个要求更少块读的查询。
一个非常大的查询或当前计数－－这说明查询做了大量的工作。这是否是一个问题是很主观的。有些查询碰到了大量的数据，如上例。然而，一个经常执行的查询应该有一个相对来说较小的计数，如果在total行中增加查询和当前模式块，您可能期望较小的计数。
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 63
他告诉我们，我们执行的查询在共享池中找到了，在这个分析期间在库缓存上没有产生一个错误。这说明执行了一个查询的软分析。在查询一开始执行时，我们希望这个数为1，若几乎每个查询都为1，则说明没有使用绑定变量（且要对此进行修改），不用重用sql。
第二行告诉我们这个查询执行期间使用了优化器模式，所开发和使用的查询方案受此设置影响。