oracle sql tuning 14 --10046

10046event是oracle用於系統效能分析時的一個最重要的事件。當啟用這個事件後，將通知oracle kernel追蹤會話的相關即時資訊，並寫入到相應trace檔案中。這些有用的資訊主要包括sql是如何進行解析，繫結變數的使用情況，會話中發生的等待事件等。10046event 可分成不同的級別（level），分別追蹤記錄不同程度的有用資訊。對於這些不同的級別，應當注意的是向下相容的，即高一級的trace資訊包含低於此級的所有資訊。

    10046event的追蹤級別大致有：

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

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

    level 8：包括等待事件

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

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

    10046event的啟用和關閉：

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

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

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

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

    這裡還有幾種方式來啟用10046event：

    一種是在當前會話啟用event，可以利用alter session + 事件名稱 + level，

    如：sql>alter session set event ‘10046 trace name context forever, level 12’;

    另外一種是啟用別的會話進行event跟蹤，可以利用oracle提供的dbms_system來完成。

    如：sql>exec dbms_system.set_ev(sid,serial#,10046,12,’’);

    注意：

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

    相當於開啟了sql_trace。

    event的關閉：

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

    sql>alter session set event ‘10046 trace name context off’;

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

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

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

    當利用事件trace完當前或某個session後，接下來我們的工作就是找到oracle生成的trace了。Oracle的初始化檔案中user_dump_dest引數的設定將決定trace檔案的生成位置。

    從trace檔案中查詢和發現有用的資訊，然後尋找必要的效能調整點並進行相應的調整：

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

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

小知識：

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

    SQL>set serveroutput on

    2 declare i_event number;

    3 begin

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

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

     5 end;

     6 /

    the session sql_trace level is: 12

    PL/SQL 過程已成功完成。

例子：

select count(*)
from
 user_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04          1        653          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.10       0.11          1        653          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=653 pr=1 pw=0 time=44741 us)
     41   VIEW  USER_OBJECTS (cr=653 pr=1 pw=0 time=34144 us)
     41    UNION-ALL  (cr=653 pr=1 pw=0 time=33930 us)
     41     FILTER  (cr=652 pr=0 pw=0 time=33463 us)
     41      TABLE ACCESS FULL OBJ$ (cr=620 pr=0 pw=0 time=33208 us)
     15      TABLE ACCESS BY INDEX ROWID IND$ (cr=32 pr=0 pw=0 time=752 us)
     15       INDEX UNIQUE SCAN I_IND1 (cr=17 pr=0 pw=0 time=310 us)(object id 39)
      0     INDEX RANGE SCAN I_LINK1 (cr=1 pr=1 pw=0 time=7209 us)(object id 107)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

那可以看這些資料的單位和意思呢？

 

轉：http://blog.chinaunix.net/u1/56189/showart_489930.html