如何收集用來診斷效能問題的10046 Trace(SQL_TRACE) (文件 ID 1523462.1)

適用於:

用途

這篇文件列舉幾種有效地收集10046 Trace的方法，用於診斷查詢效能方面的問題。
此文件可供DBA, 開發和技術支援人員使用。

問題和答案

收集10046 trace

Event 10046是為Oracle session收集擴充套件的sql_trace資訊的標準方法。
關於這個event的詳細描述請參見以下文件：

通常為了診斷SQL調優類問題，我們需要記錄下這些語句在執行過程中產生的等待以及bind variables(繫結變數)的資訊。 這些資訊可以透過級別為12的10046 trace獲得。下面的例子列舉了在各種場景下，如何設定10046事件。

• Trace檔案的位置
• 在Session級開啟trace
• 跟蹤一個已經開始的程式
• 例項層的跟蹤
• 初始化引數設定
• 透過logon trigger設定跟蹤
• 用SQLT收集trace
• 用DBMS_MONITOR進行跟蹤
• 其它特定場景下開啟跟蹤的方法
• Trace檔案解析

• Trace檔案的位置
  
  11g R1以上:
  
  從11gR1開始，Oracle引入了新的診斷結構，以引數DIAGNOSTIC_DEST控制存放trace檔案與core檔案的路徑。
  可以用以下命令，獲取DIAGNOSTIC_DEST的位置：
  
    SQL> show parameter diagnostic_dest
  
  11gR1以前:
  
  如果是使用者程式，10046 trace檔案會被生成在user_dump_dest下；如果是後臺程式，trace檔案會被生成在background_dump_dest下。
  下面的命令可以顯示user_dump_dest:
    SQL> show parameter user_dump_dest
  
  
  注：下面的某些例子中會設定tracefile_identifier，透過這個設定可以幫助我們更容易的找到生成的trace檔案。
  
  
• 在Session級開啟trace
  
  適用於SQL語句可以在新的session建立後再執行。
  在session級收集10046 trace：
  
  alter session set tracefile_identifier='10046';
  
  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;
  
  alter session set events '10046 trace name context forever,level 12';
  
  -- 執行需要被trace的SQL --
  
  select * from dual;
  exit;
  如果不退出當前session, 可以用以下命令關閉trace:
  
  alter session set events '10046 trace name context off';
  注意，如果session沒有被徹底地關閉並且跟蹤被停止了，某些重要的trace資訊的可能會丟失。
  
  注意：這裡我們將"statistics_level"設定為all，這是因為有可能這個引數在系統級不是預設值"TYPICAL"（比如 BASIC）。為了收集效能相關問題的資訊我們需要開啟某個級別的statistics。我們推薦在 session 級將這個引數設定成 ALL 以便於收集更多的資訊，儘管這不是必須的。
   
  
• 跟蹤一個已經開始的程式
  
  如果需要跟蹤一個已經存在session，可以用 oradebug連線到session上，併發起10046 trace。
  
  
  1. 首先，用某種方法找到需要被跟蹤的session.
     
     例如，在SQL*Plus裡，找出目標session的OS的程式ID(spid):
     
     select p.PID,p.SPID,s.SID
     from v$process p,v$session s
     where s.paddr = p.addr
     and s.sid = &SESSION_ID
     /
     SPID 是作業系統的程式識別符號（os pid）
     PID 是Oracle的程式識別符號(ora pid)
     
     
  
  
  如果你不知道session的ID, 那麼可以使用類似下面的SQL語句來幫助你找到它
  
  
  column line format a79
  set heading off
  select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
    s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
  from v$session s , v$process p
  where p.addr = s.paddr
  and s.username <> ' ';
   
  
  
  如果是使用了12c的multi thread下，那麼需要使用v$process中新的列stid來找到對應的thread, 因為Oracle把多個processes放進了一個單獨的 ospid 中。如果想找到特定的thread, 使用下面的語法:
  
  
  
  oradebug setospid <spid> <stid>
  
  
  
• 一旦找到OS PID，就可以用以下命令初始化跟蹤：
  
  假設需要被跟蹤的OSPID是9834。
  以sysdba的身份登入到SQL*Plus並執行下面的命令：
  
  connect / as sysdba
  oradebug setospid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
  記得把例子中的'9834' 替換成真實的os pid。

下面的例中， 使用

PID

（Oracle程式識別符號）(而不是SPID), oradebug命令將被改為：

記得把例子中的9834替換成真實的ora pid。

跟蹤過程完成以後，關閉oradebug跟蹤：

         如果是使用了12c的multi thread下，那麼需要使用v$process中新的列stid來找到對應的thread, 因為Oracle把多個processes放進了一個單獨的 ospid 中。如果想找到特定的thread, 使用下面的語法::

          oradebug setospid <spid> <stid>oradebug unlimit

         tracefile名字會是 <instance><spid>_<stid>.trc 的格式.

• Instance wide tracing
  
  

• 例項層的跟蹤
  
  
  注意: 在例項層設定跟蹤需要非常小心，這是因為整體效能會由於所有session都被跟蹤而受到影響。
  
  這個設定將會跟蹤在這個引數設定“以後”建立的每個session。已經存在的session不會被跟蹤。
  
  系統層的10046跟蹤適用於當我們知道問題session會出現，但是不能預先識別它的時候。
  在這種情況下，可以開啟系統層跟蹤一小段時間，當問題被重現以後立即將其關閉，然後從已經生成的trace中查詢需要的資訊。
  
  用以下命令開啟系統層的跟蹤：
  
  alter system set events '10046 trace name context forever,level 12';
  用以下命令關閉在所有session中的10046跟蹤：
  
  alter system set events '10046 trace name context off';

• 初始化引數設定
  
  設定以下引數並重新啟動例項後，例項上所有的session都會開啟跟蹤。
  
  event="10046 trace name context forever,level 12"
  移除這個引數並且重啟例項， 或者使用下面的alter system命令可以關閉跟蹤。
  
  alter system set events '10046 trace name context off';

• 透過logon trigger設定跟蹤
  
  有的時候當需要跟蹤某個特定使用者的操作時，可以使用logon trigger來開啟跟蹤
  下面是一個例子:
  
  CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like '&USERNAME')
  DECLARE
  lcommand varchar(200);
  BEGIN
  EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
  EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
  EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
  EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /
  為了能開啟跟蹤session, 執行trigger的使用者需要被顯式地授予'alter session' 許可權. 例如，
  
  

  grant alter session to <USERNAME> ;

• 用SQLT收集trace
  
  SQLTXPLAIN的Xecute方法生成的診斷檔案中會包含10046 trace。正如XECUTE這個名字所示，SQLT會執行被分析的SQL語句， 然後生成一個診斷檔案集（包括10046 trace）。詳見：
  
  
  Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)
  
  用這種方法生成的trace檔案會被包含在SQLT輸出包中， 格式為：
  
  sqlt_s12345_10046_execute.trc
  
  12345是SQLT報告ID。
  
  

• 用DBMS_MONITOR進行跟蹤
  
  DBMS_MONITOR包提供幾個開啟跟蹤的方法。詳細請見：
  
  
  Note:293661.1 Tracing Enhancements In 10g Using DBMS_MONITOR
  
  Oracle Database PL/SQL Packages and Types Reference
  10g Release 2 (10.2)
  Part Number B14258-02
  Chapter 60 DBMS_MONITOR
• 其它特定場景下開啟跟蹤的方法
  
  
  Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
  Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
  
  Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
  Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
  
  Note:1102801.1 How to Get 10046 Trace for Parallel Query
  Note:242374.1 Tracing PX session with a 10046 event or sql_trace
  
  Note:258418.1 Getting 10046 Trace for Export and Import
  
  如果您執行的是PL/SQL儲存過程或包，那麼可以透過使用PL/SQL profiler判斷PL/SQL執行期間的時間消耗的具體資訊。
  下面的文件介紹了PL/SQL profiler的使用方法：
  
  
  Note:243755.1 Implementing and Using the PL/SQL Profiler
  
  
  
  
• Trace檔案解析
  
  
  Note:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
  Note:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
  
  Note:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
  
  NOTE:41634.1 - TKProf Basic Overview
  NOTE:32951.1 - TKProf Interpretation (9i and below)
  NOTE:760786.1 - TKProf Interpretation (9i and above)
  NOTE:214106.1 - Using TKProf to compare actual and predicted row counts
  
  Note:199083.1 * Master Note: SQL Query Performance Overview
  Note:398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions

參考