前一篇文章介紹瞭如何安裝trca，接下來介紹如何使用trca：必須進入 trca/run 目錄裡面或者把 /home/oracle/software/trca/run 放到SQL_PATH環境變數裡面：

使用trcanlzr.sql的時候可以直接跟trace 檔名字，該工具預設會到user_dump_dest 裡面去找（對於10g以前的，會到bdump_dest 裡面去找跟蹤檔案）

sys@RAC> show parameter user_dump_dest

NAME TYPE VALUE

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

user_dump_dest string /opt/rac/oracle/diag/rdbms/rac

/rac1/trace

oracle@rac1:/home/oracle/software/trca/run>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 8 19:57:15 2011

sys@RAC> @trcanlzr.sql rac1_ora_17428.trc

Parameter 1:

Trace Filename or control_file.txt (required)

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: rac1_ora_17428.trc

Analyzing rac1_ora_17428.trc

To monitor progress, login as TRCANLZR into another session and execute:

SQL> SELECT * FROM trca$_log_v;

... analyzing trace(s) ...

Trace Analyzer completed.

Review first trcanlzr_error.log file for possible fatal errors.

Review next trca_e85484.log for parsing messages and totals.

Copying now generated files into local directory

TKPROF: Release 11.2.0.1.0 - Development on Sat Oct 8 19:57:34 2011

adding: trca_e85484.html (deflated 90%) 執行分析指令碼以後，會產生五個檔案，並打包成.zip檔案

adding: trca_e85484.log (deflated 83%)

adding: trca_e85484.tkprof (deflated 85%)

adding: trca_e85484.txt (deflated 87%)

adding: trcanlzr_error.log (deflated 81%)

test of trca_e85484.zip OK

deleting: trcanlzr_error.log

Archive: trca_e85484.zip

Length Date Time Name

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

215465 10-08-11 19:57 trca_e85484.html

15721 10-08-11 19:57 trca_e85484.log

27117 10-08-11 19:57 trca_e85484.tkprof

115329 10-08-11 19:57 trca_e85484.txt

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

373632 4 files

File trca_e85484.zip has been created

TRCANLZR completed.

SQL>EXIT

oracle@rac1:/home/oracle/software/trca/run>ls

trca_e85484.zip trcanlzr.sql

oracle@rac1:/home/oracle/software/trca/run>unzip trca_e85484.zip

Archive: trca_e85484.zip

inflating: trca_e85484.html

inflating: trca_e85484.log

inflating: trca_e85484.tkprof

inflating: trca_e85484.txt

oracle@rac1:/home/oracle/software/trca/run>ls

trca_e85484.html trca_e85484.log trca_e85484.tkprof trca_e85484.txt trca_e85484.zip trcanlzr.sql

trca_e85484.html --可以以網頁形式檢視統計資訊

trca_e85484.log --分析過程的日誌

trca_e85484.tkprof --和常規的tkprof 產生的檔案一樣：記錄了所有sql的執行計劃，Parse，Execute，Fetch 。

trca_e85484.txt --以文字形式看出統計資訊,和html的內容一樣，只是形式是文字的方式。

相對於tkprof的優勢：

1. 跟蹤檔案分析器能夠顯示出確切的繫結變數的值，dba再也不用考慮sql執行時，變數的值具體是什麼!

顯示的sql語句如下

DELETE FROM HISTORY where ALERT_TIME <= :b1 AND INSTANCE_NUMBER = :b2

Trace Analyzer 將會顯示如下形式:

0:"2/4/2003 15:57:35" 1:1

就相當於：

DELETE FROM HISTORY where ALERT_TIME <= :"2/4/2003 15:57:35" AND INSTANCE_NUMBER = 1

2. Trace Analyzer 提供熱塊，最佳化器收集索引和表的統計資訊和其他的資訊，而這些是tkprof所不能提供的。

下面的輸出展示了sql語句及其執行計劃和相關物件的統計資訊。

DELETE FROM SCOTT.EMPLOYEE

call count cpu elapsed disk query current rows misses

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

Parse 1 0.00 0.00 0 0 0 0 0

Execute 3 0.05 0.52 0 27 224 216 0

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

total 4 0.05 0.52 0 27 224 216 0

Explain Plan

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

...3 DELETE STATEMENT

...2 .DELETE OF 'SCOTT.EMPLOYEE

...1 ..TABLE ACCESS (FULL) OF ‘SCOTT.EMPLOYEE'

OWNER.TABLE_NAME

...owner.index_name num rows blocks sample last analyzed date

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

SCOTT.EMPLOYEE..........................

The output above indicates that the EMPLOYEE table does not have statistics.

3.Trace Analyzer與tkprof工具不同，Trace Analyzer 將普通使用者的遞迴和系統內部的遞迴呼叫分開。

4.Trace Analyzer 提供更多的等待事件的細節，更有利於dba做出基於等待事件的最佳化措施

Event Times Count Max. Total Blocks

waited on Waited Zero Time Wait Waited Accessed

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

PL/SQL lock timer........................ 15 0 5.01 75.08

log file sync............................ 1 0 0.01 0.01

library cache pin........................ 1 0 0.00 0.00

SQL*Net message from client (idle)....... 2 0 17.22 30.21

SQL*Net message to client (idle)......... 3 0 0.00 0.00

total.................................... 22 0 17.22 105.30 0

【Oracle】一款非常好用的trace檔案分析工具之二

相關文章