Tkprof: 分析ORACLE跟蹤檔案並且產生一個更加人性化清晰的輸出結果的可執行工具

轉自：

Tkprof: 分析ORACLE跟蹤檔案並且產生一個更加人性化清晰的輸出結果的可執行工具

TKPROF使用步驟

1.設定引數檔案

設定三個引數timed_staticstices user_dump_dest max_dump_file_size

timed_staticstices 用於啟動或禁止對定時統計資訊（如CUP時間、佔用時間），以及動態效能表中多種統計資訊的收集功能

alter session set timed_statistics true;
alter system set timed_statistics false;

MAX_DUP_FILE_SIZE 當例項層啟用SQL TRACE的時候，在每次請求伺服器的時候，都將在跟蹤檔案中產生一個文字行，這些檔案的最大尺寸受限於初始化引數的設定。預設為500(blocks)。若裡面的資料被截斷則增大SIZE。若為UNLIMITED則意味著沒有上限。
USER_DUMP_DEST 設定跟蹤檔案的儲存位置。預設為admin/使用者/udump;

alter system set user_dump_dest=newdir

[@more@]

2.啟動SQL TRACE實用工具對會話啟動SQL TRACE
alter session set sql_trace=true;
alter session set sql_trace=false;
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION=（ SID ， SERIAL# ， TRUE ）；

SID，SERIAL#可以從V$SESSION檢視獲得。
ALTER SESSION SET EVENTS
alter session set events '10046 trace name context forever,level';
alter session set events '10046 trace name context off';
alter system set events '10046 trace name context forever,level 1'
alter system set events '10046 trace name context off'

n=1---------啟用標準SQL_TRACE工具，這與設定SQL_TRACE=TRUE 沒有任何不同；
n=4---------啟用標準SQL_TRACE，且可以撲獲跟蹤檔案中的繫結變數。
n=8---------啟用標準SQL_TRACE,且可在查詢級上撲獲跟蹤檔案中的等待事件。
n=12--------啟用標準SQL_TRACE,幷包括撲獲繫結變數與等待事件。
對使用者例項啟動 SQL TRACE
alter system set sql_trace=true;
alter system set sql_trace=false;

3.使用tkprof格式化trace檔案 Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
輸入檔案 格式化後的輸出檔案
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
用於指定在將執行規劃寫進輸出檔案之前，TKPROF用於臨時存放執行規劃所用表的架構和名稱
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements. 只列出輸出檔案中的第一個INTEGER的SQL語句，若忽略，則TKPROF將列出所有跟蹤的SQL語句
aggregate=yes|no 若使用者指定AGGREGATE=NO，TKPROF將不會對相同SQL文字的多個使用者進行彙總
insert=filename List SQL statements and data inside INSERT statements. SQL指令碼的一種，用於將跟蹤檔案的動機資訊儲存到資料庫中
sys=no TKPROF does not list SQL statements run as user SYS. 於啟動或禁止將使用者SYS所釋出的SQL語句列表到輸出檔案之中，也包括遞迴SQL(為執行使用者的SQL語句，ORACLE還必須執行一些附加語句)語句在內。預設為YES
record=filename Record non-recursive statements found in the trace file. 對於跟蹤檔案中的所用非遞迴SQL語句，TKPROF 將以指定的名稱來建立某個SQL指令碼。用於對跟蹤檔案中的使用者時間進行重放
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options: 在將被跟蹤的SQL語句列表輸出到跟蹤檔案之前，先將其按照指定排序選項的降序關係對其進行排序；若指定了多種排序選項，那麼根據排序選項所指定值的和的降序關係對其進行排序；若忽略此引數，那麼TKPROF將按照使用次序把語句列表到輸出檔案中
prscnt number of times parse was called 語句解析的數目
prscpu cpu time parsing 語句解析所佔用的CPU時間
prsela elapsed time parsing 語句解析所佔用的時間（總是大於或等於CPU時間）；
prsdsk number of disk reads during parse 語句解析期間，從磁碟進行物理讀取的數目
prsqry number of buffers for consistent read during parse 語句解析期間，一致模式塊讀取（CONSISTENT MODE BLOCK READ）的數目
prscu number of buffers for current read during parse 語句解析期間，當前模式讀取（CURRENT MODE BLOCK READ）的數目
prsmis number of misses in library cache during parse 語句解析期間，庫快取失敗的數目

execnt number of execute was called 語句執行的數目
execpu cpu time spent executing 語句執行所佔用的CPU時間
exeela elapsed time executing 語句執行所佔用的時間（總是大於或等於CPU時間）
exedsk number of disk reads during execute 語句執行期間，從磁碟進行物理讀取的數目
exeqry number of buffers for consistent read during execute 語句執行期間，一致模式塊讀取（CONSISTENT MODE BLOCK READ）的數目
execu number of buffers for current read during execute 語句執行期間，當前模式讀取（CURRENT MODE BLOCK READ）的數目
exerow number of rows processed during execute 語句執行期間，所處理的語句行數
exemis number of library cache misses during execute 語句執行期間，庫快取失敗的數目

fchcnt number of times fetch was called 取資料的數目
fchcpu cpu time spent fetching 取資料所佔用的CPU時間
fchela elapsed time fetching 取資料所佔用的時間（總是大於或等於CPU時間）
fchdsk number of disk reads during fetch 取資料期間，從磁碟進行物理讀取的數目
fchqry number of buffers for consistent read during fetch 取資料期間，一致模式塊讀取（CONSISTENT MODE BLOCK READ）的數目
fchcu number of buffers for current read during fetch 取資料期間，當前模式讀取（CURRENT MODE BLOCK READ）的數目
fchrow number of rows fetched 所獲取的行數
userid userid of user that parsed the cursor

4.tkprof檔案的閱讀

==================

ORACLE TKPROF使用步驟

1．Tkprof是一個分析ORACLE跟蹤檔案並且產生一個更加人性化清晰的輸出結果的可執行工具。C:oracleora92bintkprof.exe

2. tkprof 全稱

TKPROF stands for transient kernel profiler.

3. 基本的使用步驟

1) SQL> alter system set timed_statistics=true;

2) 使用者級自跟蹤：

SQL>ALTER SESSION SET SQL_TRACE=TRUE;

SQL>ALTER SESSION SET SQL_TRACE=FALSE;

使用者級DBA跟蹤：(例如sys跟蹤test，需要用sysdba登入)

. a). SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s

where s.USERNAME='COLM' ;

b). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,true);

c). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,false);

ps：9為SID，7為SERIAL#

3) C:> cd C:oracleadminCOLMudump

4) C:oracleadminCOLMudump> tkprof colm_ora_2056.trc trace.txt print=100 record=sql.txt sys=no

5) 檢視trace.txt檔案

範例：

*******************************************************************************

SELECT *

FROM

col_case

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 0 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 62

需要調整的語句符合以下幾點：

(1).CPU佔用過多

(2).Parse,Execute,Fetch花費太多時間

(3).DISK讀取太多，query/current(SGA)中資料塊讀取太少

(4).訪問許多塊，只返回2行

========================
使用TKPROF轉換跟蹤檔案為可讀格式

Oracle可以使用ALTER SESSION來設定一個底層的跟蹤，只要啟用該跟蹤，Oracle將所有的SQL和應用程式使用的頂層的
PL/SQL呼叫記錄到伺服器上的跟蹤檔案中（udump/*.trc）。該跟蹤檔案不僅有SQL和PL/SQL呼叫，也將包括定時的資訊、
等待事件的資訊，執行的邏輯I/O和物理I/O的數量、CPU和掛鐘時間、已經處理的行數、帶有行記數的查詢計劃等，但是.trc
檔案很難閱讀，我們可以使用TKPROF轉換其為易讀的格式。
首先、為了起用跟蹤並使得Oracle生成有效的.trc檔案我們需要做的第一件事就是在session級別上開啟SQL_TRACE：
sql 程式碼
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever,level 12';
上面的SQL語句1主要是設定定時系統，而第2句主要是開啟跟蹤：需要注意的一點是level ，說明如下：
1 啟用標準的SQL_TRACE工具，等價於SQL_TRACE=true
4 啟用SQL_TRACE並捕捉跟蹤檔案中的繫結變數
8 啟用SQL_TRACE並捕捉跟蹤檔案的等待事件
12 啟用標準的SQL_TRACE並捕捉繫結變數和等待事件

如果您需要確認某個資料庫中有多少.trc檔案的話，這裡有一個查詢可以幫助您（Oracle9i）上除錯透過：

sql 程式碼
select rtrim(c.value,'/') || '/' || d.instance_name || '_ora_' || ltrim(to_char(a.spid)) || '.trc' from v$process a,v$session b,v$parameter c,v$instance d
where a.addr = b.paddr and b.audsid= sys_context('userenv','sessionid')
and c.name ='user_dump_dest';
測試結果大致為：
absolute_pathdbnameudump/dbname_ora_384.trc
absolute_pathdbnameudump/dbname_ora_2000.trc
下面我們就使用TKPROF來轉化dbname_ora_2000.trc為可讀的格式（因為本人在win系統上做業，因此轉換其為
.txt格式）：
>tkprof dbname_ora_2000.trc traceview.txt
執行完此轉化後會有一個traceview.txt檔案在當前目錄下生成（其內容我們結合具體的跟蹤語句說明）：
這裡我們隨便執行一條查詢語句：

sql 程式碼
select count(*) from test_trace_an;
exit;--退出sql plus

假設上面的dbname_ora_2000.trc就是目前的跟蹤結果：則使用TKPROF轉化後的結果檔案中，我們可以發現：

select count(*) from test_trace_an

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL OBJ#(30327)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
至於該.txt檔案該怎麼閱讀，以及從中可以看到什麼資訊，並將如何處理它所提供的資料。