Oracle中如何跟蹤SQL或資料庫操作 [final]

 

一般生成的trace檔案格式為 imb_ora_10552.trc, 即 資料庫名+ora+SPID .
其中spid是OS process ID .  

1.    用SQL_TRACE
在資料庫級別上設定TIMED_STATISTICS為True。SQL TRACE相當於10046 事件
的Level 1:  啟用標準的sql_trace功能. 

A.  在全域性中使用:  設定在spfile中的引數sql_trace=true ;  會導致所有程式的活動
被跟蹤，包括後臺程式及所有使用者程式，這通常會導致比較嚴重的效能問題，
所以在生產環境中要謹慎使用  (除非特殊情況,  一般較少使用) . 

B. 在session級使用:   
啟用當前session跟蹤:    SQL> alter session set sql_trace=true;
追蹤一段時間....... 
結束跟蹤:    SQL> alter session set sql_trace=false;
這裡的啟動和結束跟蹤之間要隔一段時間。

找到本session對應生成的trace檔案 。
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  ；

 

2.    用DBMS_SUPPORT包或DBMS_SYSTEM包跟蹤其他使用者session : 
在資料庫級別上設定TIMED_STATISTICS為True。

查詢v$session檢視,獲取程式資訊 
SQL> select sid,  serial#,username from v$session; 
或者透過spid查詢sid, serial#  : 
SQL> select  b.*, a.*   from v$process a, v$session b 
          where a.addr = b.paddr and a.spid in (1914, 19140)

啟用相關session程式sql_trace 
SQL> exec dbms_system.set_sql_trace_in_session(7,284,true)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(11,214,true)
PL/SQL procedure successfully completed.

等候一段時間,關閉sql_trace
SQL> exec dbms_system.set_sql_trace_in_session(7,284,false)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(11,214,false)
PL/SQL procedure successfully completed.

檢查trace檔案或使用 tkprof  xxx.trc   xxx.txt  sys=no ...格式化trace檔案.   

 

 

3.    使用10046 event 跟蹤 . 
在資料庫級別上設定TIMED_STATISTICS為True。

使用10046 event的方法大致如下(當前session)：
alter session set events '10046 trace name context forever, level 12';
your sql statement...
alter session set events '10046 trace name context off';

其中的level有1，4，8，12幾個選項，其中1相當於設定SQL_TRACE=TRUE之後
的結果，4包括1的結果和繫結變數的實際值，8包括1的結果和等待事件的情況，
12則同時包含1的結果，繫結變數的實際值和等待事件情況，所以可以說level 12
是最為詳細的trace了。

備註: 

10046事件是oracle提供的內部事件，是對sql_trace的增強，可以設定以下4個級別：
Level 1:啟用標準的sql_trace功能，等價於sql_trace
Level 4:等價於Level 1+繫結值
Level 8: 等價於Level 4+等待事件跟蹤
Level 12: 等價於Level 1+level 4 + level 8

 

使用10046 event 跟蹤其他使用者session :

對其他使用者session設定  dbms_system.set_ev
說明：5個引數   sid/serial#/ev/level/username

Select sid,serial#   from v$session where username is not null ;
       SID    SERIAL# 
---------- ---------- -----
       113         227      
       292        314       
       189        2280  

或者透過spid查詢sid, serial#  : 
SQL> select  b.*, a.*   from v$process a, v$session b 
          where a.addr = b.paddr and a.spid in (1914, 19140) ; 

執行跟蹤：
exec dbms_system.set_ev(113,227,10046,8,'');

結束跟蹤：
exec dbms_system.set_ev(113,227,10046,0,'');

 

4. 使用 tkprof 命令示例：

“tkprof tracefile outputfile explain=userid/password" 

在作業系統oracle使用者下，鍵入“tkprof”，會有詳細的命令幫助。分析後的輸出
檔案 outputfile中，有每一條PL/SQL語句的“執行計劃”、CPU佔用、物理讀次數、
邏輯讀次數、執行時長等重要資訊。 根據輸出檔案的資訊，我們可以很快發現應
用中哪條PL/SQL語句是問題的癥結所在。

常用的使用方式: 
$ tkprof    xxx.trc    xxx.txt     sys=no   explain=userid/password   

 

 

5.  tkprof 得出的output檔案分析：

對Tkprof命令輸出的解釋,  首先解釋輸出檔案中列的含義：

CALL：每次SQL語句的處理都分成三個部分

Parse：這步將SQL語句轉換成執行計劃，包括檢查是否有正確的授權和所需要用
到的表、列以及其他引用到的物件是否存在。

Execute：這步是真正的由Oracle來執行語句。對於insert、update、delete操作，這步
會修改資料，對於select操作，這步就只是確定選擇的記錄。

Fetch：返回查詢語句中所獲得的記錄，這步只有select語句會被執行。

COUNT:這個語句被parse、execute、fetch的次數。

CPU：這個語句對於所有的parse、execute、fetch所消耗的cpu的時間，以秒為單位。

ELAPSED：這個語句所有消耗在parse、execute、fetch的總的時間。

DISK：從磁碟上的資料檔案中物理讀取的塊的數量。一般來說更想知道的是正
在從快取中讀取的資料而不是從磁碟上讀取的資料。

QUERY：在一致性讀模式下，所有parse、execute、fetch所獲得的buffer的數量。
一致性模式的buffer是用於給一個長時間執行的事務提供一個一致性讀的快照，緩
存實際上在頭部儲存了狀態。

CURRENT：在current模式下所獲得的buffer的數量。一般在current模式下執行insert、
update、delete操作都會獲取buffer。在current模式下如果在快取記憶體區發現有新的緩
存足夠給當前的事務使用，則這些buffer都會被讀入了快取區中。

ROWS: 所有SQL語句返回的記錄數目，但是不包括子查詢中返回的記錄數目。對
於select語句，返回記錄是在fetch這步，對於insert、update、delete操作，返回記錄則
是在execute這步。

-----------------
問題及解決措施
-----------------

解析數字太大
 應該增大SHARED_POOL_SIZE或使用繫結變數
 
DISK磁碟讀(物理讀)取量太高
 沒有使用索引或根本就不存在索引
 
query和/或current列值(記憶體讀取, 邏輯讀)太高
 索引位於低基數的列上(由一個值組成了表中大部分記錄的列；比如y/n欄位)。刪除/限制
索引，或使用直方圖或點陣圖索引或許可以提高效能。表連線順序或連線索引的順序不好
也會發生這個情況....... 
 
分析所需要的時間太多
   可能是開放遊標的數量有問題
 
EXPLAIN PLAN裡某一行語句要處理的行數相對於其他行語句而言太多
 這可能表明有一個索引對唯一鍵(一個列上的唯一值)進行了較差的分佈。
 
在分析期間庫快取裡Misses值大於1
 這表明需要過載這條語句。可能需增大init.ora檔案中的SHARED_POOL_SIZE值，或者執行
一次較好的共享SQL任務
 

 

6.  原始trace檔案內容分析 得出的output檔案分析：

參考