ORACLE 10046事件詳解-轉載

Oracle的10046事件，可以跟蹤應用程式所執行的SQL語句，並且得到其解析次數.執行次數,CPU使用時間等資訊。這對我們分析、定位資料庫效能問題是非常有用的。

10046event是oracle用於系統效能分析時的一個最重要的事件。當啟用這個事件後，將通知oracle kernel追蹤會話的相關即時資訊，並寫入到相應trace檔案中。這些有用的資訊主要包括sql是如何進行解析，繫結變數的使用情況，會話中發生的等待事件等

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

10046event的追蹤級別大致有：

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

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

level 8：包括等待事件

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

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

前提條件：

（先確保要event的會話環境符合條件）

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

2、為了確保trace輸出能夠完整進行，還要調整此會話對trace檔案大小的限制，一般將此限制取消，即將max_dump_file_size設定為UNLIMITED，或者設定為一個很大的闕值。

在滿足了上述條件後，就可以啟用10046event對會話進行後臺跟蹤了。

 

1、首先獲得spid、sid、serial#，machine為連線oracle的機器名

select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr  and a.sid = '159'

繼續往下看，就可以知道為什麼找到SPID了；

2、10046事件啟動-需要SYSDBA許可權

SQL>execute sys.dbms_system.set_ev(15,196,10046,1,'');
PL/SQL procedure successfully completed.

引數說明：
15:SID
196:SERIAL#

注意這裡必須以sysdba登入。

或者會話級別的跟蹤：

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

會話已更改。

3、在應用程式中執行對資料庫的操作

比如效能較差的一些查詢、插入、刪除操作等。

4、10046事件的關閉

SQL>execute sys.dbms_system.set_ev(15,196,10046,0,'');

PL/SQL procedure successfully completed.

或者對應上面的，在會話級別關閉

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

會話已更改。

5、獲得產生的跟蹤檔案所在的目錄

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

轉到該目錄下可以看到生成了一個zftang_ora_596.trc檔案，此處的596即SPID的值。

6、使用tkprof命令，轉換TRC檔案

在命令列下執行oracle的tkprof命令，將zftang_ora_596.trc轉化為文字檔案

如：
$ tkprof  ora9i_ora_24722.trc ora9i_ora_24722.sql

tkprof /u01/diag/rdbms/bysrac/bysrac1/trace/bysrac1_ora_15241_TEST9_1.trc /home/oracle/test9_1.trc

此時在zftang_ora_596.sql檔案中，就可以看到剛才執行應用程式時所執行的sql語句的執行次數、CPU使用時間等資料。

TRACE將消耗相當的系統資源，因此我們在使用TRACE的時候應當慎重。對於正式的系統，應當只在必要的時候進行TRACE操作，並且應當及時關閉。

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

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

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

update test_01 set flag1 = '測試'

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.01          0          2          0           0

Execute      1      2.35       2.64        358       1865     220043       50782

Fetch        0      0.00       0.00          0          0          0           0

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

total        2      2.35       2.65        358       1867     220043       50782

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 63        

timed_statistics的理解

關於引數timed_statistics的一段解說
Without timed statistics, Oracle records the reason for each wait before it begins to wait, and when the wait is over, it records whether it timed out. But with timed statistics enabled, Oracle checks the time just before and after each wait, and also records the time waited. The time waited is recorded in hundredths of a second— that is, centiseconds.

本人的理解是:引數timed_statistics沒有設定的話.Oracle只記錄wait開始和wait結束這一段時間內的wait reason和wait結束是否是因為timed out.而設定timed_statistics=true後.Oracle就記錄了wait的時間資訊.有了這些時間資訊可以確定等待事件在哪個時間段出現比較頻繁.   請指正

在timed_statistics  = false時,我們只能夠知道這些等待時間的次數(records the reason for each wait ), 以及等待時間timed out的次數(records whether it timed out)
在timed_statistics = true時,我們可以知道系統在某個具體的等待事件上等待的時間(records the time waited),這樣我們就可以判斷具體是什麼原因導致我們的系統變慢或者...