sql_trace跟蹤工具(轉)

lixianlinde發表於2013-02-04
SQL
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 ct=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 ct=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 ct=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
    
  在命令列模式下執行(資料庫在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 0 0 0 0
  Fetch 1 0.00 0.01 5 7 0 1
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  total 3 0.00 0.03 6 38 0 1
   
  Misses in library cache during parse: 1
  Optimizer goal: CHOOSE
  Parsing user id: 62   
 
  通過設定tkprof的關鍵字[EXPLAIN = ],也可以在跟蹤檔案中增加SQL語句的執行計劃：
  C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt explain=test/test;   
   
  ********************************************************************************
  
  select count(*)
  from
  sys_dept
  
 
  call count cpu elapsed disk query current rows
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  Parse 2 0.00 0.01 1 31 0 0
  Execute 2 0.00 0.00 0 0 0 0
  Fetch 2 0.00 0.01 5 14 0 2
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  total 6 0.00 0.03 6 45 0 2
  
  Misses in library cache during parse: 1
  Optimizer goal: CHOOSE
  Parsing user id: 62  
 
  Rows Row Source Operation
  ------- ---------------------------------------------------
  1 SORT AGGREGATE
16 TABLE ACCESS FULL SYS_DEPT

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24570973/viewspace-753887/，如需轉載，請註明出處，否則將追究法律責任。

相關文章