溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

使用 oracle 10046 event

發(fā)布時間：2020-08-18 06:24:26 來源：ITPUB博客 閱讀：186 作者：kunlunzhiying 欄目：建站服務器

描述

遇到sql語句查詢出錯的問題.Yong Huang版提示做10046事件.對這個事件以前一直是模糊概念.想理清楚,所以寫成這個文檔.供以后使用.

環(huán)境

RHEL5.4 64 + 10.2.0.4

說明

10046 event 能干什么

目前知道這是一個系統(tǒng)性能分析事件,這個事件可以告訴oracle內核把相應session的詳細時間信息輸出到trace文件中.

10046 event level (不翻譯了,水平有限,翻了半天還是覺得原文好)

You can think of the event 10046 “l(fā)evel” attribute associated with an Oracle session as a 4-bit flag whose bits have the following meanings:

Level
 Function
Decimal Binary
1 0001 Emit statistics for parse, execute, fetch, commit, and rollback database calls (standard sql_trace)
2 0010 Unknown
3 0100 Emit values for SQL bind variables (also called “placeholders”)
4 1000 Emit statistics for Oracle kernel internal function calls (also called “wait events”) listed in v$event_name

For example, a level-12 trace combines the effects of level-4  and level-8 tracing. Strangely, activating any non-zero tracing level also activates level-1 tracing. Therefore, tracing at levels 4, 8, and 12 are exactly equivalent to tracing at levels 5, 9, and 13, respectively: all these levels include the standard sql_trace output.

trace 文件位置

11gR1 或 11gR1 以上版本

SQL> show parameter diagnostic_dest

11gR1以前版本

SQL> show parameter user_dump_dest

trace 文件命名規(guī)則

tracefile 命名規(guī)則 :<ORACLE_SID>_ora_<pid>_<tracedid>.trc

其中pid為相應session所對應的OS PID,tracedid 跟session的TRACEFILE_IDENTIFIER參數(shù)相關,默認TRACEFILE_IDENTIFIER為null.

eg.

給當前session設置TRACEFILE_IDENTIFIER

ALTER SESSION SET TRACEFILE_IDENTIFIER='TOMS';
此處的"TOMS"即為trace file 命名規(guī)則中<ORACLE_SID>_ora_<pid>_<tracedid>.trc的 tracedid.
可以設置TRACEFILE_IDENTIFIER參數(shù)的session里查詢V$PROCESS.TRACEID查看tracefile_identifier的設置.

取消session標識將 tracefile_identifier置空即可.
alter session set tracefile_identifier='';

定位trace文件(10g版本測試通過)

1.自己當前session的trace file (需要有查詢 v$mystat, v$session ,v$process 的權限)

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE</w:LidThemeComplexScript. MicrosoftInternetExplorer4 <style. /* Style. Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-font-kerning:1.0pt;} </style.

select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from v$mystat m, v$session s, v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
       (select t.instance
          from v$thread t, v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from v$parameter where name = 'user_dump_dest') d;

2.以sys用戶查找其他session的trace file (需要知道其他session sid)

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE</w:LidThemeComplexScript. MicrosoftInternetExplorer4 <style. /* Style. Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-font-kerning:1.0pt;} </style.

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE</w:LidThemeComplexScript. MicrosoftInternetExplorer4 <style. /* Style. Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-font-kerning:1.0pt;} </style.

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE</w:LidThemeComplexScript. MicrosoftInternetExplorer4 <style. /* Style. Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-font-kerning:1.0pt;} </style.

select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from v$session s, v$process p
         where s.sid = &sid
           and p.addr = s.paddr) p,
       (select t.instance
          from v$thread t, v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from v$parameter where name = 'user_dump_dest') d;

會提示輸入sid的數(shù)值.嘿嘿,就是把1給小小的改動了下.sid到v$session視圖中查詢.這里面的trace file名字是拼出來的.所以僅僅符合trace命名規(guī)則的<ORACLE_SID>_ora_<pid>.trc部分


步驟

session級的trace

1.10046 trace 自己的 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 max_dump_file_size = 2147483647;
  alter session set events '10046 trace name context forever,level 12';

  -- Execute the queries or operations to be traced here --

  select * from dual;  
  alter session set events '10046 trace name context off';
  exit;

2. dbms_system包 10046 trace 指定的session (Oracle release 8.1.6 and newer)

  conn / as sysdba
  exec sys.dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true);
  exec sys.dbms_system.set_int_param_in_session(sid,serial#,'max_dump_file_size',2147483647);
  exec sys.dbms_system.set_ev(sid,serial#,10046,12,'');

  -- Execute the queries or operations to be traced here --

  select * from dual;

  exec sys.dbms_system.set_ev(sid,serial#,10046,0,'');
  exit;

3.oradebug

查詢出session的SID,SERIAL#,PID和SPID(OS PID)
  conn / as sysdba
  select p.PID,p.SPID,s.SID,s.SERIAL#
  from v$process p,v$session s
  where s.paddr = p.addr
  and s.sid = &SESSION_ID;
通過OS的PID生成10046 trace
  connect / as sysdba
  oradebug setospid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
  oradebug event 10046 trace name context off
記得把9834換成自己查詢出來的OS PID
通過數(shù)據(jù)庫的PID生成10046 trace
  connect / as sysdba
  oradebug setorapid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
  oradebug event 10046 trace name context off

記得把9834換成自己查詢出來的OS PID

instance級的10046 trace

Note: Please be cautious when setting system wide, as this will impact performance due to every session being traced.

修改參數(shù)文件events參數(shù)實現(xiàn)instance級的10046
1.只對參數(shù)修改后新連接的session生效.
  alter system set events '10046 trace name context forever,level 12';
  alter system set events '10046 trace name context off';
2.重啟數(shù)據(jù)庫后生效
  alter system set events '10046 trace name context forever,level 12' scope=spfile;
關閉也用 alter system set events '10046 trace name context off';

通過 Logon Trigger 做10046 trace

有些情況我們需要trace一個登錄用戶.這時可以通過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;
  /

注意登錄用戶必須擁有alter session權限才能成功trace.

grant alter session to <USERNAME> ;
通過trace文件命名規(guī)則,我們可以知道,oracle kernel只在process生命期內寫trace.所以,當session自然斷開后,trace的工作也自然停止,而不必顯式的敲入關閉trace命令.


個人總結

生成10046 trace僅僅是分析的第一步.后面還有好多分析要去學習.

疑問:

1.TRACEFILE_IDENTIFIER在某一個session中設置后,其他session怎么才能知道設置的TRACEFILE_IDENTIFIER值? session設置TRACEFILE_IDENTIFIER后,會在trace目錄下有兩個trace文件,一個含tracedid,一個不含.

2.dbms_system包的用法沒找到.待解決.


參考文檔

Oracle System Performance Analysis Using Oracle Event 10046

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues [ID 376442.1]

如何快速獲取trace文件名

向AI問一下細節(jié)

免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內容。

AI
助
手