10046 Event的追蹤級別大致有：

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

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

level 8：包括等待事件；

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

level 16: 針對sql的每次執行過程，產生統計資訊

level 32: 針對sql的每次執行過程，不產生統計資訊

level 64: 針對sql的每次執行過程，自適應產生統計資訊。假設sql被執行超過1分鐘，將產生統計資訊。將自動針對消耗資源較大sql的執行過程收集更多的統計資訊

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,’’); —n代表級別,1/4/8/12/16/32/64

說明：

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,’’); —n為0，代表關閉10046時間

這裡應當注意的是，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檔案中記錄的資訊能夠被我們容易獲取和理解。

基本用法：

tkprof tracefile 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語句執行情況以及繫結變數的使用情況來進行分析和查詢。

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

race檔案內容：

分析（parse）：SQL的分析階段

執行（execute）：SQL的執行階段

資料提取（Fetch）：資料提取階段

橫向的列除了call之外，還包含了一下資訊：

count：計算器，表示當前的操作被執行了多少次。

cpu：當前的操作消耗的cpu時間（單位秒）

Elapsed: 當前的操作一共用時多少（包括cpu事件和等待時間）

Disk：當前操作的物理讀（磁碟i/o次數）

Query：當前操作的一致性讀方式讀取的資料塊數（通常是查詢）

Current：當前操作的current的方式讀取的資料庫數（通常是修改資料塊使用的方式）

Rows：當前操作處理的資料記錄數

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 3 0 1

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

total 4 0.00 0.00 0 3 0 1

Rows 當前操作返回的實際返回的記錄數。

Row Source Operation 表示當前操作的資料訪問方式。

cr（consistent read）一致性讀取的資料塊，相當於query列上的fetch的值

pr（physical read）物理讀取的資料塊，相當於disk列上的fetch的值

pw（physical write）物理寫

time 當前操作執行時間

Rows Row Source Operation

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

1 TABLE ACCESS BY INDEX ROWID TEST (cr=3 pr=0 pw=0 time=0 us cost=2 size=29 card=1)

1 INDEX UNIQUE SCAN SYS_C00399080 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 217036)

PARSING IN CURSOR 部分：

len：被分析sql的長度

dep：產生遞迴sql的深度

uid：user id

otc：Oracle command type命令的型別

lid：私有用的id

tim：時間戳

hv： hash value

ad： sql address

PARSE，EXEC，FETCH部分：

c: 消耗的cpu time

e：elapsed time 操作的用時

p：physical reads次數

cr：consistent reads資料的塊

cu：current方式讀取的資料塊

mis：cursor miss in canche硬分析次數

r：rows處理的行數

dep：depth遞迴sql的深度

og：optimize goal最佳化器模式

tim：timstamp時間戳

stats部分：

id：執行計劃的行源號

cnt：當前行源返回的行數

pid：當前行源的父號

pos：執行計劃中的位置

obj：當前操作的物件id

op：當前行源的資料訪問操作

PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=1381490108569206 hv=459648918 ad='6d9c7590' sqlid='d3gtqbsdqbbwq'

select * from test where id=100

END OF STMT

PARSE #1:c=1999,e=1757,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4274779609,tim=1381490108569205

EXEC #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4274779609,tim=1381490108569319

FETCH #1:c=0,e=92,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=4274779609,tim=1381490108569461

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=217035 op='TABLE ACCESS BY INDEX ROWID TEST (cr=3 pr=0 pw=0 time=0 us cost=2 size=29 card=1)'

STAT #1 id=2 cnt=1 pid=1 pos=1 obj=217036 op='INDEX UNIQUE SCAN SYS_C00399080 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'

FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4274779609,tim=1381490108591269

10046設定和檔案引數講解

相關文章