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

昵称10504424 2013-08-28

展开全文

explain=username/passwd ##对trace文件中的sql语句产生执行计划，会在这个用户下产生plan_table，并在tkprof结束时删除。
sys=yes|no ##选no，用来屏蔽oracle内部递归sql
waits=yes|no ##选yes，记录等待事件概要
aggregate=yes|no ##选yes，oracle会组合相同sql文本的多个用户
record=filename ##会生成一个保存所有sql语句的文件
print=number ##只显示前N条语句，一般和sort一起用
insert=filename ##生成一个脚本，记录所追踪的sql语句的insert
sort=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 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: ora11g_ora_5829.trc
Sort options: fchela 
********************************************************************************
count     = number of times OCI procedure was executed
cpu        = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk       = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = 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: 3617692013

select * 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         573

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86  (U1)
Number of plan statistics captured: 1

Rows (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)

Trace file: ora11g_ora_5829.trc
Trace file compatibility: 11.1.0.7
Sort 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.