terry筆記］Oracle會(huì)話追蹤（二）：TKPROF

昵稱10504424 2013-08-28

展開全文

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

（個(gè)人感覺不是很爽，trace文件輸出的時(shí)候直接弄個(gè)好格式不就得了）

explain=username/passwd ##對(duì)trace文件中的sql語(yǔ)句產(chǎn)生執(zhí)行計(jì)劃，會(huì)在這個(gè)用戶下產(chǎn)生plan_table，并在tkprof結(jié)束時(shí)刪除。
sys=yes|no ##選no，用來(lái)屏蔽oracle內(nèi)部遞歸sql
waits=yes|no ##選yes，記錄等待事件概要
aggregate=yes|no ##選yes，oracle會(huì)組合相同sql文本的多個(gè)用戶
record=filename ##會(huì)生成一個(gè)保存所有sql語(yǔ)句的文件
print=number ##只顯示前N條語(yǔ)句，一般和sort一起用
insert=filename ##生成一個(gè)腳本，記錄所追蹤的sql語(yǔ)句的insert
sort=parameters ##輸出的內(nèi)容按照某項(xiàng)排序：按照實(shí)際執(zhí)行時(shí)間排序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個(gè)sql
       1  internal SQL statements in trace file.  ##1個(gè)sql是oracle內(nèi)部遞歸
       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.

這里解釋下query和current

query就是select語(yǔ)句在內(nèi)存中檢索了多少塊

current就是update、delete等語(yǔ)句在內(nèi)存中修改了多少塊

一些指標(biāo)：

好的情況是以很少的fatch獲取更多的rows。

query的parse指的是從數(shù)據(jù)字典(dictionary cache)中獲取多少塊。

主要以CPU列和elapsed列來(lái)衡量資源的消耗。

要消滅全表掃描。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自：昵稱10504424 > 《工作》

舉報(bào)/認(rèn)領(lǐng)