Oracle效能分析1：開啟SQL跟蹤和擷取trace檔案

標籤：oracle   效能   資料庫   database   

當Oracle查詢出現效率問題時，我們往往需要瞭解問題所在，這樣才能針對問題給出解決方案。Oracle提供了SQL執行的trace資訊，其中包含了SQL語句的文本資訊，一些執行統計，處理過程中的等待，以及解析階段（如產生執行計畫）產生的資訊。這些資訊有助於你分解sql語句的服務時間和等待時間，並瞭解所用資源和同步點的詳細資料，從而協助你診斷存在的效能問題。
這篇文章介紹了怎麼開啟SQL跟蹤和擷取trace檔案，詳細資料如下。

開啟SQL跟蹤

從內部技術細節看，SQL跟蹤是基於10046調試事件的，下面是支援的等級：

 0
 禁止調試事件
 1
 調試事件是啟用的。針對每個被處理的資料庫調用，給定如下資訊：SQL語句、回應時間、服務時間
 處理的行數、處理的行數、邏輯讀數量、物理讀與寫的數量、執行計畫以及一些額外資訊
 4
 如果等級1，包括綁定變數的額外資訊。主要是資料類型、精度以及每次執行時所用的值
 8
 同等級1，加上關於等待時間的細節資訊。為了處理過程中的每個等待，提供如下資訊：等待時間的名字、期間，以及一些額外的參數，可標明所等待的資源
 12
 同時啟動等級4和等級8
在Oracle 9i或者之前，下面SQL語句針對所在會話啟用SQL跟蹤：

alter session set sql_trace = true

還可通過dbms_session包中的set_sql_trace預存程序，或者通過dbms_system包的set_sql_tarce_in_session預存程序方法，但這些都只是在等級1啟用SQL跟蹤，在實踐中用處不大，就不詳述了。
更有用的是指定層級的方式，下面是對所在會話開始等級12的SQL跟蹤：

alter session set events '10046 trace name context forever, level 12'

對應的對所在會話禁止SQL跟蹤的語句如下：

alter session set events '10046 trace name context off'

也可以通過dbms_system包中的set_ev預存程序，這裡也不詳述了，我下面重點講講Oracle 10g之後提供的方法。
Oracle 10g之後提供了dbms_monitor包來開啟或關閉SQL跟蹤，提供了在會話、用戶端、組件以及資料庫層級開啟SQL跟蹤方法，注意只有dba角色的使用者才允許使用。

會話級

下面的PL/SQL為ID為122，序號為6734的會話開啟第8級的SQL跟蹤：

begin  dbms_monitor.session_trace_enable(session_id => 122,                                    serial_num => 6734,                                    waits      => true,                                    binds      => false);end;

session_id
session標識，對應v$session視圖中的SID列，下面是擷取當前會話id的方法：

select userenv('sid') from dual

serial_num
對應v$session視圖中的SERIAL#列，由於SID會重用，當SID被重用時，SERIAL#增加，擷取方法如下：

select serial# from v$session where sid = 122

waits
對應v$session視圖中的SQL_TRACE_WAITS，表示等待事件跟蹤是否被啟用，預設為true。

binds
對應v$session視圖中的SQL_TRACE_BINDS，表示綁定跟蹤是否被啟用，預設false。

當執行成功後，v$session視圖中的SQL_TRACE被修改為ENABLED，SQL_TRACE_WAITS和SQL_TRACE_BINDS為你設定的對應值。
下面的PL/SQL用於關閉SQL跟蹤：

begin  dbms_monitor.session_trace_disable(session_id => 122, serial_num => 6734);end;

用戶端級

下面的PL/SQL調用為所有具有指定用戶端標記的會話開啟第8級的SQL跟蹤：

begin  dbms_monitor.client_id_trace_enable(client_id => 'test',                                      waits     => true,                                      binds     => false);end;

需要注意用戶端標記區分大小寫，可以通過下面的方法看是否設定成功：

select primary_id as client_id, waits, binds  from dba_enabled_traces where trace_type = 'CLIENT_ID'

當設定成功後，每次查詢前指定對應的用戶端標記就可以開啟SQL跟蹤，指定用戶端標記的方法如下：

begin  DBMS_SESSION.SET_IDENTIFIER('test');end;//該會話的SQL跟蹤已經開啟

當你為一個session設定了標記後，可以在v$session的client_identifier列查看該標記。
下面的PL/SQL用於關閉SQL跟蹤：

begin  dbms_monitor.client_id_trace_disable(client_id => 'test');end;

組件級

下面的PL/SQL調用為所有具有指定用戶端標記的會話開啟第8級的SQL跟蹤：

begin  dbms_monitor.serv_mod_act_trace_enable(service_name  => 'ly',                                         module_name   => 'PL/SQL Developer',                                         action_name   => 'SQL 視窗 - 建立',                                         waits         => true,                                         binds         => false,                                         instance_name => null);end;

參數中的service_name對應v$session視圖的service_name，module_name對應v$session視圖的的module，action_name對應v$session視圖的action，查詢方式如下：

SELECT sid, serial#,            client_identifier, service_name, action, module       FROM V$SESSION

設定之後可以通過如下方法查看設定：

select primary_id    as service_name,       qualifier_id1 as module_name,       qualifier_id2  as action_name,       waits,       binds  from dba_enabled_traces where trace_type = 'SERVICE_MODULE_ACTION'

下面的PL/SQL用於關閉SQL跟蹤：

begin  dbms_monitor.serv_mod_act_trace_disable(service_name  => 'ly',                                          module_name   => 'PL/SQL Developer',                                          action_name   => 'SQL 視窗 - 建立',                                          instance_name => null);end;

資料庫級

下面的PL/SQL調用開啟了資料庫的12級SQL跟蹤：

begin  dbms_monitor.database_trace_enable(waits         => true,                                     binds         => true,                                     instance_name => null);end;

下面的方法查看設定是否成功：

select instance_name,       waits,       binds  from dba_enabled_traces where trace_type = 'DATABASE'

下面的PL/SQL用於關閉SQL跟蹤：

begin  dbms_monitor.database_trace_disable(instance_name => null);end;

trace檔案中的計時資訊

下面的語句用於為trace檔案提供計時資訊：

alter session set timed_statistics = true

一般情況下預設都為true，如果不提供計時資訊，trace檔案就沒有什麼用了，因此開啟SQL跟蹤之前，最好確認一下參數被設定為true。

擷取產生的trace檔案

開啟SQL跟蹤後，會產生一個trace檔案，通過初始化參數user_dump_dest配置其所在目錄，該參數的值可以通過下面方法擷取到：

select name, value from v$parameter where name = 'user_dump_dest'

但如果我們需要定位到具體的檔案，則需要瞭解trace檔案的命名。trace檔案的名字是獨立於版本和平台的，在大部分常見的平台下，命名結構如下：

{instance name}_{process name}_{process id}.trc

 1）instance name
 初始化參數instance_name的小寫值。通過v$instance視圖的instance_name列可以得到這個值。
 2）process name
 產生追蹤檔案進程的名字的小寫值。對於專有伺服器處理序，使用ora，對於共用伺服器處理序，可以通過v$diapatcher或v$shared_server視圖的name列獲得。對於並行從屬進程，可以通過v$px_process視圖server_name列獲得，對於其他多數後台進程來說，可以通過v$bgprocess視圖的name列獲得。
 3）process id
 作業系統層面的進程標記。這個值可以通過v$process視圖的spid列擷取。
根據這些資訊，可以通過下面的方式擷取trace檔案名稱：

select s.SID,       s.SERVER,       lower(case               when s.SERVER in ('DEDICATED', 'SHARED') then                i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||                p.SPID || '.trc'               else                null             end) as trace_file_name  from v$instance      i,       v$session       s,       v$process       p,       v$px_process    pp,       v$shared_server ss where s.PADDR = p.ADDR   and s.SID = pp.SID(+)   and s.PADDR = ss.PADDR(+)   and s.TYPE = 'USER'   and s.SID = 'your sid' order by s.SID

將上面的‘your sid‘替換為你的session的sid就可以查出指定session產生的trace檔案的名字，session的sid在v$session視圖中得到，或者直接查詢當前session的sid：

select userenv('sid') from dual

將路徑（user_dump_dest）和檔案名稱結合在一起，我們就得到了trace檔案的完整路徑。

而在Oracel 11g中，查詢當前會話產生的trace檔案則非常簡單：

select value from v$diag_info where name = 'Default Trace File'