terry笔记］Oracle会话追踪（二）：TKPROF

在系统下执行tkprof，效果是把trace文件转换成可供人类阅读的格式

（个人感觉不是很爽，trace文件输出的时候直接弄个好格式不就得了）

语法：

tkprof tracefile output_file

explain=username/passwd ##对trace文件中的sql语句产生执行计划，会在这个用户下产生plan_table，并在tkprof结束时删除。sys=yes|no ##选no，用来屏蔽oracle内部递归sqlwaits=yes|no ##选yes，记录等待事件概要aggregate=yes|no ##选yes，oracle会组合相同sql文本的多个用户record=filename ##会生成一个保存所有sql语句的文件print=number ##只显示前N条语句，一般和sort一起用insert=filename ##生成一个脚本，记录所追踪的sql语句的insertsort=parameters ##输出的内容按照某项排序：按照实际执行时间排序fchela，物理读排序fchdsk

例子：

tkprof ora11g_ora_11111.trc scott.txt explain=scott/tiger sys=no waits=yes sort=fchela

 

tkprof的输出（分上下两部分）：

TKPROF: Release 11.2.0.3.0 - Development on Sat Aug 24 13:51:24 2013Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Trace file: ora11g_ora_5829.trcSort options: fchela ********************************************************************************count     = number of times OCI procedure was executedcpu        = cpu time in seconds executingelapsed  = elapsed time in seconds executingdisk       = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows     = number of rows processed by the fetch or execute call********************************************************************************SQL ID: 9kcpyjv574vjc Plan Hash: 3617692013select * from t1 where owner='SYSTEM'call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0  Execute      1      0.00       0.00          0          0          0           0  Fetch       40      0.00       0.01       1065       1106          0         573------- ------  -------- ---------- ---------- ---------- ----------  ----------total       42      0.00       0.01       1065       1107          0         573Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 86  (U1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------       573        573        573  TABLE ACCESS FULL T1 (cr=1106 pr=1065 pw=0 time=4866 us cost=298 size=71622 card=346)Rows     Execution Plan-------  ---------------------------------------------------      0  SELECT STATEMENT   MODE: ALL_ROWS    573   TABLE ACCESS (FULL) OF 'T1' (TABLE)

View Code

Trace file: ora11g_ora_5829.trcTrace file compatibility: 11.1.0.7Sort options: fchela       1  session in tracefile.       5  user  SQL statements in trace file.  ##这里面共有5个sql       1  internal SQL statements in trace file.  ##1个sql是oracle内部递归       6  SQL statements in trace file.       6  unique SQL statements in trace file.       1  SQL statements EXPLAINed using schema:           U1.prof$plan_table             Default table was used.             Table was created.             Table was dropped.     159  lines in trace file.      24  elapsed seconds in trace file.

View Code

 

这里解释下query和current

query就是select语句在内存中检索了多少块

current就是update、delete等语句在内存中修改了多少块

 

一些指标：

好的情况是以很少的fatch获取更多的rows。

query的parse指的是从数据字典(dictionary cache)中获取多少块。

主要以CPU列和elapsed列来衡量资源的消耗。

要消灭全表扫描。