oracle sql跟蹤 event 10046 - 轉

我們知道，Oracle提供了Internal Events以便Oracle Support人員利用其對Oracle的內部行為進行一些更為深入細緻的記錄與跟蹤，從而分析查詢並解決Oracle使用者所遇到的棘手問題。對於大部分的Internal Events，Oracle是沒有官方文件加以闡述的，不過，這些Events中有幾個是被公認為非常重要並被廣泛研究和採用的。這其中，最重要的就是在進行Sql Tuning時常利用的10046 event。

如果你對oracle效能調優很感興趣或者比較專長，那麼你對oracle的10046事件一定不會陌生。10046 event是對oracle進行系統效能分析時一個最重要的手段。當啟用這個事件後，將通知oracle kernel追蹤會話的相關即時資訊，並寫入到相應trace檔案中。這些有用的資訊主要包括：sql是如何進行解析及其執行計劃，繫結變數的使用情況，會話中發生的等待事件等。

10046 event可分成不同的級別（level），分別追蹤記錄不同程度的有用資訊。對於這些不同的級別，應當注意的是向下相容的，即最高階的trace資訊包含低於此級的所有資訊。

[@more@]

10046 Event的追蹤級別大致有：

level 1：跟蹤sql語句，包括解析、執行、提取、提交和回滾等；

level 4：包括變數的詳細資訊；

level 8：包括等待事件；

level 12：包括繫結變數與等待事件。

其中，level 1相當於開啟了sql_trace。

10046 Event啟用前的準備：

前提條件：（先確保例項或會話的環境符合條件）

1、 必須確保timed_statistics為TRUE，這個引數可以在會話級上進行修改。

2、 為了確保Event的Trace輸出能夠完整進行，還要調整此會話對Trace檔案大小的限制，一般地，將max_dump_file_size設定為一個很大的闕值，或者取消此限制，即設定為UNLIMITED。（不建議設定為UNLIMITED，因為有可能會造成磁碟空間使用異常）

在設定完了上述條件後，就可以啟用Event進行後臺跟蹤了。

Oracle event的啟用可以在例項級或者會話級上來進行。其中，例項級的啟用方式是在初始化引數檔案中設定event引數的值。

如果想要在例項級啟用10046 event，則可在初始化引數檔案pfile中增加如下一行：

event=’10046 event trace name context forever, level n’

或者：

SQL >alter system set events ‘10046 trace name context forever, leveln’;

由於event的啟用對系統資源消耗很大，因此，一般不建議在生產執行庫中啟用例項級上的event。實際情況中，也是沒有必要對整個例項進行event的TRACE。下面就主要針對會話級10046 event的情況進行大致闡述。

啟用10046 Event的幾種方式：

一種是在當前會話啟用event。可以利用alter session set events語句，如下：

SQL >alter session set events ‘10046 trace name context forever, leveln’;

一種是在當前會話中對別的會話啟用event跟蹤。可以利用oracle提供的包dbms_system來完成，如下：

SQL >exec dbms_system.set_ev(sid,serial#,10046,n,’’);

說明：

1、獲取當前會話的sid和serial#可以透過下面語句來實現：

SQL >select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);

2、SQL >exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

相當於開啟了sql_trace。

10046 Event的關閉：

可以透過下面的語句來關閉當前會話的event：

SQL >alter session set events ‘10046 trace name context off’;

也可以利用dbms_system包來關閉某個會話的event：

SQL >execute dbms_system.set_ev(sid,serial#,10046,0,’’);

這裡應當注意的是，TRACE將消耗相當的系統資源，因此我們在使用TRACE的時候應當慎重。對於線上的生產系統，在必要時應當選擇合適的時候進行TRACE操作，並且應當及時關閉。

Trace檔案的查詢：

當利用事件trace完當前或某個session後，接下來我們的工作就是找到oracle生成的trace檔案了。Oracle的初始化引數檔案中user_dump_dest的設定將決定trace檔案的生成位置。我們可以透過檢視引數檔案，或者藉助sqlplus來查詢得出user_dump_dest所設定的路徑：

SQL> show parameter user_dump_dest

NAME TYPE VALUE

---------------------- ----------- ------------------------------

user_dump_dest string /oracle/app/oracle/admin/SIDDB/udump

SQL> col name format a20

SQL> col value format a50

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

NAME VALUE

-------------------- --------------------------------------------------

user_dump_dest /oracle/app/oracle/admin/ SIDDB /udump

unix下，trace檔案的命名規則一般是sid_ora_spid.trc。spid為該session對應的外部程式號。

TKPROF的用途和使用：

oracle提供了一個工具tkprof來對trace檔案進行格式的翻譯，以便trace檔案中記錄的資訊能夠被我們容易獲取和理解。

基本用法：

tkproftracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAIN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no tkprofdoes not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse

從Trace檔案中發現有用的資訊，尋找必要的效能調整點：

大部分情況下，透過10046事件trace到檔案裡的資訊包含了此會話中存在的效能問題，可以根據trace到的等待事件、SQL語句執行情況以及繫結變數的使用情況來進行分析和查詢。

這部分的內容就要結合實際應用情況，具體情況具體分析了。

小知識：

檢查當前會話的sql_trace狀態或級別：

SQL>set serveroutput on

2 declare i_event number;

3 begin

4 sys.dbms_system.read_ev(10046,i_event);

5 dbms_output.put_line(‘the session sql_trace level is: ‘||i_event);

6 end;

7 /

the session sql_trace level is: 12

PL/SQL過程已成功完成。

****************************************************************

另類跟蹤使用者session 的方法:
1. top or sm50 --&gt PID

2. SELECT sid, serial#
FROM v$session s, v$process p
Where s.paddr = p.addr
AND p.spid = '&pid'

3. exec dbms_system.set_sql_trace_in_session(sid, serial#, true);

4. exec dbms_system.set_sql_trace_in_session(sid, serial#, false);

5. tkprof xxxx.trc session.txt explain=sapr3/xxxx aggregate=yes sys=no waits=yes sort=fchela;

轉自：%BC%FB%D6%A4my%B3%C9%B3%A4/blog/item/e5107dd03d51a0d2a8ec9a3c.html

taojia/blog/item/d303d0c4bfbee8c339db49be.html

-------------------------

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

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
/

alter session set events '10046 trace name context off';

ALTER SYSTEM SET EVENT='10046 trace name context forever,level 8';
ALTER SYSTEM SET EVENT='10046 trace name context off';

tkprof appstb_ora_1019924.trc session.txt explain=SYS/***** aggregate=yes waits=yes sort=fchela;