利用10046事件收集SQL的trace檔案

Event 10046是為Oracle session收集擴充套件的sql_trace資訊的標準方法，通常為了診斷SQL調優類問題，我們需要記錄下這些語句在執行過程中產生的等待以及bind variables(繫結變數)的信。 這些資訊可以透過級別為12的10046 trace獲得。

一、trace檔案的位置：
在11g之前：
SQL> show parameter user_dump_dest
在11g之後：
SQL> show parameter diagnostic_dest
注：下面的某些例子中會設定tracefile_identifier，透過這個設定可以幫助我們更容易的找到生成的trace檔案

二、在Session級開啟trace
適用於SQL語句可以在新的session建立後再執行
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資訊的可能會丟失。

三、跟蹤一個已經開始的程式
如果需要跟蹤一個已經存在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)
2、一旦找到OS PID，就可以用以下命令初始化跟蹤：
使用SPID跟蹤：
假設需要被跟蹤的OSPID是9834. 以sysdba的身份登入到SQL*Plus並執行下面的命令：
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Note: 也可以透過oradebug使用 'setorapid'命令連線到一個session.
使用ORAPID跟蹤：
使用PID（Oracle程式識別符號）(而不是SPID), oradebug命令將被改為：
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟蹤過程完成以後，關閉oradebug跟蹤：
oradebug event 10046 trace name context off

四、例項層的跟蹤
注意: 在例項層設定跟蹤需要非常小心，這是因為整體效能會由於所有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 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 ;

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

八、用DBMS_MONITOR進行跟蹤
DBMS_MONITOR包提供幾個開啟跟蹤的方法。 詳細請見：
Document: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

九、其它特定場景下開啟跟蹤的方法
Document:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Document:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
Document:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Document:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
Document:1102801.1 How to Get 10046 Trace for Parallel Query
Document:242374.1 Tracing PX session with a 10046 event or sql_trace
Document:258418.1 Getting 10046 Trace for Export and Import
如果您執行的是PL/SQL儲存過程或包，那麼可以透過使用PL/SQL profiler判斷PL/SQL執行期間的時間消耗的具體資訊。
下面的文件介紹了PL/SQL profiler的使用方法：
Document:243755.1 Implementing and Using the PL/SQL Profiler

十、Trace檔案解析
Document:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
Document:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
Document:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
Document:41634.1 - TKProf Basic Overview
Document:32951.1 - TKProf Interpretation (9i and below)
Document:760786.1 - TKProf Interpretation (9i and above)
Document:214106.1 - Using TKProf to compare actual and predicted row counts
Document:199083.1 * Master Note: SQL Query Performance Overview