使用 oracle 10046 event
描述
遇到sql語句查詢出錯的問題.Yong Huang版提示做10046事件.對這個事件以前一直是模糊概念.想理清楚,所以寫成這個文件.供以後使用.
環境
RHEL5.4 64 + 10.2.0.4說明
10046 event 能幹什麼
目前知道這是一個系統效能分析事件,這個事件可以告訴oracle核心把相應session的詳細時間資訊輸出到trace檔案中.10046 event level (不翻譯了,水平有限,翻了半天還是覺得原文好)
You can think of the event 10046 “level” 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 檔案命名規則
tracefile 命名規則 :<ORACLE_SID>_ora_<pid>_<tracedid>.trc
其中pid為相應session所對應的OS PID,tracedid 跟session的TRACEFILE_IDENTIFIER引數相關,預設TRACEFILE_IDENTIFIER為null.
eg.
給當前session設定TRACEFILE_IDENTIFIER
ALTER SESSION SET TRACEFILE_IDENTIFIER='TOMS';
此處的"TOMS"即為trace file 命名規則中<ORACLE_SID>_ora_<pid>_<tracedid>.trc的 tracedid.
可以設定TRACEFILE_IDENTIFIER引數的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_' || |
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_' ||
|
會提示輸入sid的數值.嘿嘿,就是把1給小小的改動了下.sid到v$session檢視中查詢.這裡面的trace file名字是拼出來的.所以僅僅符合trace命名規則的<ORACLE_SID>_ora_<pid>.trc部分
步驟
session級的trace
1.10046 trace 自己的 session
|
2. dbms_system包 10046 trace 指定的session (Oracle release 8.1.6 and newer)
conn / as sysdba |
3.oradebug
查詢出session的SID,SERIAL#,PID和SPID(OS PID)
conn / as sysdba |
透過OS的PID生成10046 trace
connect / as sysdba |
透過資料庫的PID生成10046 trace
connect / as sysdba |
記得把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.修改引數檔案events引數實現instance級的10046
1.只對引數修改後新連線的session生效.
alter system set events '10046 trace name context forever,level 12'; |
alter system set events '10046 trace name context forever,level 12' scope=spfile; |
透過 Logon Trigger 做10046 trace
有些情況我們需要trace一個登入使用者.這時可以透過trigger完成.
CREATE OR REPLACE TRIGGER SYS.set_trace |
注意登入使用者必須擁有alter session許可權才能成功trace.
grant alter session to <USERNAME> ; |
個人總結
生成10046 trace僅僅是分析的第一步.後面還有好多分析要去學習.
疑問:
1.TRACEFILE_IDENTIFIER在某一個session中設定後,其他session怎麼才能知道設定的TRACEFILE_IDENTIFIER值? session設定TRACEFILE_IDENTIFIER後,會在trace目錄下有兩個trace檔案,一個含tracedid,一個不含.
2.dbms_system包的用法沒找到.待解決.
參考文件
如何快速獲取trace檔名
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28211342/viewspace-2144416/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle event 10046 level_事件Oracle事件
- Oracle 10046 event詳解-轉載Oracle
- oracle sql跟蹤 event 10046 - 轉OracleSQL
- Event 10046 - Enable SQL Statement TraceSQL
- event 10046等的設定
- 使用10046 event trace跟蹤全表掃描操作
- Oracle中開啟trace 10046 event 的各種方法[zt]Oracle
- Oracle 10046跟蹤的使用Oracle
- oracle 10046 事件使用方法Oracle事件
- oracle 10046Oracle
- oracle 10046其他使用者會話Oracle會話
- oracle10g使用10046 報錯Oracle
- ORACLE TRACE 10046Oracle
- 【EVENT】使用10046事件獲取SQL語句中繫結變數的具體值事件SQL變數
- Oracle 10046 SQL TRACEOracleSQL
- 使用10046事件檢視oracle執行計劃事件Oracle
- 使用oracle的10046事件跟蹤SQL語句Oracle事件SQL
- 使用10046 來了解oracle的啟動過程Oracle
- 使用10046跟蹤Oracle前映象資料讀Oracle
- EVENT: 10046 "enable SQL statement tracing (including binds/waits)"SQLAI
- 跟蹤資料庫的命令:event 10046等的設定(ZT)資料庫
- oracle event 2 (zt)Oracle
- Oracle SQL Trace 和 10046 事件OracleSQL事件
- 讀懂Oracle 10046 SQL TRACEOracleSQL
- oracle 10046當前會話Oracle會話
- Oracle SQL Trace 和10046 事件OracleSQL事件
- oracle sql tuning 14 --10046OracleSQL
- 【Oracle】Oracle常用EVENT之三Oracle
- 【Oracle】Oracle常用EVENT之二Oracle
- 【Oracle】Oracle常用EVENT之一Oracle
- 從10046看Oracle分割槽裁剪Oracle
- oracle sql trace與10046淺談OracleSQL
- Maclean教你讀Oracle 10046 SQL TRACEMacOracleSQL
- ORACLE 10046事件詳解-轉載Oracle事件
- How to read Oracle 10046 trace fileOracle
- ORACLE EVENT && ORADEBUGOracle
- ORACLE event和說明Oracle
- ORACLE 配置event引數Oracle