sql_trace是oracle提供的一個非常好的跟蹤工具，主要用來檢查資料庫的異常情況，透過跟蹤資料庫的活動，找到有問題的語句。

一、概述：
SQL_TRACE是Oracle的一個非常強大的工具。開啟SQL_TRACE就可以逐步捕獲任何一個會話的資料庫活動，或者捕獲整個資料庫的活動，並將資料庫活動記錄成跟蹤檔案。每次使用完之後需要關閉跟蹤,否則會降低系統的效能。
SQL_TRACE可以幫助診斷許多問題，其中包括：

二、用法：
 1、檔案跟蹤的分類：
 跟蹤DBA可以採用兩種方式進行跟蹤：
 . 跟蹤整個資料庫例項。只需要簡單的修改引數檔案(pfile/spfile)引數 SQL_TRACE = TRUE ，然後重新啟動資料庫即可。在全域性啟用SQL_TRACE會導致所有程式的活動被跟蹤，包括後臺程式及所有使用者程式,這樣也會資料庫導致效能下降比較明顯。
 . 會話級跟蹤。SQL_TRACE的通常使用方式是僅跟蹤一個會話。被跟蹤的會話可以是您自己的，也可以是其它使用者的會話。如果是自己的會話，只需要在SQL*PLUS中執行一下命令即可：
 SQL> alter session set sql_trace = true;
 類似的如果取消對會話的跟蹤，執行一下命令：
 SQL> alter session set sql_trace = false;

 如果需要跟蹤一個特定的會話，首先需要獲取會話的SID和Serial#，這些資訊可以在檢視V$SESSION中獲得，一旦知道了這兩個引數，就可以執行一下命令：
 SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,true);
 同樣也可以使用這個過程關閉會話跟蹤：
 SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,false);

2、跟蹤檔案的位置：
 一旦為會話啟用了SQL_TRACE，ORACLE就會在udump管理區建立跟蹤檔案，檔案的目標位置由引數user_dump_dest來確定。每個操作都不會覆蓋原來的檔案，新的跟蹤記錄將會被追加到檔案末尾。通常情況下，可以根據檔案的修改時間判斷目錄下哪個檔案是最新的檔案。
 SQL> show parameter user_dump_dest;

 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 user_dump_dest string d:oracleadminora9iudump

 也可以透過以下SQL來確定檔名：

 select d.value||''||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
 from
 (
 select p.spid
 from sys.v$mystat m,sys.v$session s,sys.v$process p
 where m.statistic# = 1
 and s.sid = m.sid
 and p.addr = s.paddr
 ) p,
 (
 select t.instance
 from sys.v$thread t,sys.v$parameter v
 where v.name = 'thread'
 and ( v.value = 0 or t.thread# = to_number(v.value) )
 ) i,
 (
 select value from sys.v$parameter where name = 'user_dump_dest'
 ) d ;

TRACE_FILE_NAME
 --------------------------------------------------------------------------------

 d:oracleadminora9iudumpora9i_ora_2060.trc

3、計時資訊：
 為了最大限度的利用跟蹤檔案，應該開啟計時標誌,透過引數TIMED_STATISTICTS=TRUE進行設定，這樣可以對每個SQL語句的執行時間等進行記錄，這個功能對系統效能的負擔很小。
 開啟會話的計時資訊:
 SQL> alter session set timed_statistics = true ;
 開啟資料庫系統的計時資訊
 SQL> alter system set timed_statistics = true ;

4、TKPROF：
 透過前三步的設定已經知道如何生成SQL跟蹤檔案了，ORACLE生成的跟蹤檔案閱讀起來很困難（也就是易讀性很差），可以看跟蹤檔案的一部分，執行以下SQL語句：
 SQL> select count(*) from sys_dept;

 COUNT(*)
 ----------
 16
 執行完後，檢視跟蹤檔案中這個語句的內容如下：

 PARSING IN CURSOR #1 len=31 dep=0 uid=62 oct=3 lid=62 tim=14727407741 hv=2200985491 ad='128e3820'
 select count(*) from sys_dept
 END OF STMT
 PARSE #1:c=0,e=16348,p=1,cr=31,cu=0,mis=1,r=0,dep=0,og=4,tim=14727407735
 EXEC #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727407814
 FETCH #1:c=0,e=15641,p=5,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=14727423807
 =====================
 PARSING IN CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727508742 hv=3517412409 ad='12bbcff4'
 begin :id := sys.dbms_transaction.local_transaction_id; end;
 END OF STMT
 PARSE #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727508735
 EXEC #2:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727508945
 =====================
 PARSING IN CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727587562 hv=3517412409 ad='12bbcff4'
 begin :id := sys.dbms_transaction.local_transaction_id; end;
 END OF STMT
 PARSE #2:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727587556
 EXEC #2:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727587721

 這樣不僅閱讀麻煩，並且也有很多內容難以理解。ORACLE提供了一個格式化跟蹤檔案的工具 - TKPROF( Transient Kernel Profiler ),透過這個工具能將SQL檔案轉化為分析人員容易理解的格式。

 一般TKPROF工具的使用的簡單方法,只用到了兩個關鍵字:跟蹤檔名和輸出檔名 (TKPROF的具體請參閱其他資料):
 TKPROF <trace file> <output file>

 在命令列模式下執行(資料庫在window2000下安裝的)
 C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt

 執行完後,在reprot.txt中查詢剛才的語句內容如下:
 select count(*)
 from
 sys_dept

 call count cpu elapsed disk query current rows
 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
 Parse 1 0.00 0.01 1 31 0 0
 Execute 1 0.00 0.00

Oracle sql trace用法

相關文章