terry筆記］Oracle會話追蹤（二）：TKPROF

昵稱10504424 2013-08-28

展開全文

在系統(tǒng)下執(zhí)行tkprof，效果是把trace文件轉換成可供人類閱讀的格式

explain=username/passwd ##對trace文件中的sql語句產生執(zhí)行計劃，會在這個用戶下產生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 ##輸出的內容按照某項排序：按照實際執(zhí)行時間排序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.