Oracle 10046事件介紹(一)

做Oracle方面的工作時間長了，經常會聽人提起10046事件，尤其是涉及到SQL調優的時候更甚。那10046事件到底是什麼呢，先做一個簡單的介紹。

1、什麼是10046事件

10046事件是Oracle提供的一個用于分析性能的工具，它能幫助我們解析一條/多條SQL、PL/SQL語句的運作狀态，這些狀态包括：Parse/Fetch/Execute三個階段中遇到的等待事件、消耗的實體和邏輯讀、CPU時間、執行計劃等等。

2、10046事件的Level

不同的Level對應不同的跟蹤級别

1 啟用标準的SQL_TRACE功能(預設)包含了SQL語句、響應時間、服務時間、處理的行數，實體讀和寫的數目、執行計劃以及其他一些額外資訊。到版本10.2中執行計劃寫入到trace的條件是僅當相關遊标已經關閉時，且與之相關的執行統計資訊是所有執行次數的總和資料。到版本11.1中僅在每次遊标的第一次執行後将執行計劃寫入到trace，執行統計資訊僅僅和這第一次執行相關

4 比level 1時多出綁定變量的trace

8 比level 1多出等待事件，特别對于9i中指出latchfree等待事件很有用，對于分析全表掃描和索引掃描也很有用

12 比level 1多出綁定變量和等待事件

11g及以上版本

16 在11g中為每一次執行生成STAT資訊

32 比level 1少執行計劃

11.2.0.2及以上版本

64 和level 1相比在第一次執行後還可能生成執行計劃資訊；條件是某個遊标在前一次執行的前提下運作耗時變長了一分鐘。

3、啟用10046事件

1)對本session啟用10046事件

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

oradebug setmypid

oradebug event 10046 trace name context ,level 12

其中能修改的隻有level級别

2)對其他session啟用10046事件

oradebug setospid|setorapid xxx

oradebug event 10046 trace name context ,level 12

4、停用10046事件

分别對應上面不同的啟用方式

alter session set events '10046 trace name context forever off'

oradebug event 10046 trace name context off

或者退出啟用10046事件的session

5、擷取10046事件生成的trace檔案

a.對于11g及以上的版本，使用如下語句可以輕松得到

select value from v$diag_info where name='Default Trace File';

b.對于10g及以前的版本中需要使用如下sql

<code>SELECT</code> <code>D.VALUE || </code><code>''</code> <code>|| </code><code>LOWER</code><code>(RTRIM(I.INSTANCE, CHR(0))) || </code><code>'_ora_'</code> <code>||</code>

<code> </code><code>P.SPID || </code><code>'.trc'</code> <code>TRACE_FILE_NAME</code>

<code> </code><code>FROM</code> <code>(</code><code>SELECT</code> <code>P.SPID</code>

<code> </code><code>FROM</code> <code>SYS.V$MYSTAT M, SYS.V$SESSION S, SYS.V$PROCESS P</code>

<code> </code><code>WHERE</code> <code>M.STATISTIC# = 1</code>

<code> </code><code>AND</code> <code>P.ADDR = S.PADDR) P,</code>

<code> </code><code>(</code><code>SELECT</code> <code>T.INSTANCE</code>

<code> </code><code>FROM</code> <code>SYS.V$THREAD T, SYS.V$PARAMETER V</code>

<code> </code><code>WHERE</code> <code>V.</code><code>NAME</code> <code>= </code><code>'thread'</code>

<code> </code><code>AND</code> <code>(V.VALUE = 0 </code><code>OR</code> <code>T.THREAD# = TO_NUMBER(V.VALUE))) I,</code>

<code> </code><code>(</code><code>SELECT</code> <code>VALUE </code><code>FROM</code> <code>SYS.V$PARAMETER </code><code>WHERE</code> <code>NAME</code> <code>= </code><code>'user_dump_dest'</code><code>) D;</code>

c.如果使用oradebug指令則使用相對應的oradebug tracefile_name即可得到trace檔案

6、格式化trace檔案

10046事件所産生的原始trace檔案習慣稱之為裸trace檔案(raw trace)，Oracle記錄在裸trace檔案中的内容一眼看上去并不是那麼觀，也不是那麼容易看懂。為了祼trace檔案能夠以一種更直覺、更容易懂的方式展現出來，Oracle提供了tkprof指令，這個指令是Oracle自帶的，可以用它來翻譯祼trace檔案。

tkprof的文法如下：

<code>[oracle@rhel6 10046]$ tkprof</code>

<code>Usage: tkprof tracefile outputfile [explain= ] [table= ]</code>

<code> </code><code>[print= ] [insert= ] [sys= ] [</code><code>sort</code><code>= ]</code>

<code> </code><code>table=schema.tablename Use </code><code>'schema.tablename'</code> <code>with </code><code>'explain='</code> <code>option.</code>

<code> </code><code>explain=user</code><code>/password</code> <code>Connect to ORACLE and issue EXPLAIN PLAN.</code>

<code> </code><code>print=integer List only the first </code><code>'integer'</code> <code>SQL statements.</code>

<code> </code><code>aggregate=</code><code>yes</code><code>|no</code>

<code> </code><code>insert=filename List SQL statements and data inside INSERT statements.</code>

<code> </code><code>sys=no TKPROF does not list SQL statements run as user SYS.</code>

<code> </code><code>record=filename Record non-recursive statements found </code><code>in</code> <code>the trace </code><code>file</code><code>.</code>

<code> </code><code>waits=</code><code>yes</code><code>|no Record summary </code><code>for</code> <code>any wait events found </code><code>in</code> <code>the trace </code><code>file</code><code>.</code>

<code> </code><code>sort</code><code>=option Set of zero or </code><code>more</code> <code>of the following </code><code>sort</code> <code>options:</code>

<code> </code><code>prscnt number of </code><code>times</code> <code>parse was called</code>

<code> </code><code>prscpu cpu </code><code>time</code> <code>parsing</code>

<code> </code><code>prsela elapsed </code><code>time</code> <code>parsing</code>

<code> </code><code>prsdsk number of disk reads during parse</code>

<code> </code><code>prsqry number of buffers </code><code>for</code> <code>consistent </code><code>read</code> <code>during parse</code>

<code> </code><code>prscu number of buffers </code><code>for</code> <code>current </code><code>read</code> <code>during parse</code>

<code> </code><code>prsmis number of misses </code><code>in</code> <code>library cache during parse</code>

<code> </code><code>execnt number of execute was called</code>

<code> </code><code>execpu cpu </code><code>time</code> <code>spent executing</code>

<code> </code><code>exeela elapsed </code><code>time</code> <code>executing</code>

<code> </code><code>exedsk number of disk reads during execute</code>

<code> </code><code>exeqry number of buffers </code><code>for</code> <code>consistent </code><code>read</code> <code>during execute</code>

<code> </code><code>execu number of buffers </code><code>for</code> <code>current </code><code>read</code> <code>during execute</code>

<code> </code><code>exerow number of rows processed during execute</code>

<code> </code><code>exemis number of library cache misses during execute</code>

<code> </code><code>fchcnt number of </code><code>times</code> <code>fetch was called</code>

<code> </code><code>fchcpu cpu </code><code>time</code> <code>spent fetching</code>

<code> </code><code>fchela elapsed </code><code>time</code> <code>fetching</code>

<code> </code><code>fchdsk number of disk reads during fetch</code>

<code> </code><code>fchqry number of buffers </code><code>for</code> <code>consistent </code><code>read</code> <code>during fetch</code>

<code> </code><code>fchcu number of buffers </code><code>for</code> <code>current </code><code>read</code> <code>during fetch</code>

<code> </code><code>fchrow number of rows fetched</code>

<code> </code><code>userid userid of user that parsed the cursor</code>

7、簡單示例，資料庫版本11.2.0.4

<code>zx@ORCL></code><code>alter</code> <code>session </code><code>set</code> <code>events </code><code>'10046 trace name context forever,level 12'</code><code>;</code>

<code>Session altered.</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>scott.emp;</code>

<code> </code><code>EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO</code>

<code> </code><code>7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20</code>

<code> </code><code>7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30</code>

<code>14 </code><code>rows</code> <code>selected.</code>

<code>zx@ORCL></code><code>alter</code> <code>session </code><code>set</code> <code>events </code><code>'10046 trace name context off'</code><code>;</code>

<code>zx@ORCL></code><code>select</code> <code>value </code><code>from</code> <code>v$diag_info </code><code>where</code> <code>name</code><code>=</code><code>'Default Trace File'</code><code>;</code>

<code>VALUE</code>

<code>/u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trc</code>

<code>[oracle@rhel6 trace]$ tkprof /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trc 10046.trc</code>

<code>TKPROF: Release 11.2.0.4.0 - Development </code><code>on</code> <code>Thu Feb 16 21:38:57 2017</code>

<code>[oracle@rhel6 trace]$ cat 10046.trc</code>

<code>Trace file: /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trc</code>

<code>Sort options: </code><code>default</code>

<code>count</code> <code>= number </code><code>of</code> <code>times OCI </code><code>procedure</code> <code>was executed</code>

<code>cpu = cpu </code><code>time</code> <code>in</code> <code>seconds executing </code>

<code>elapsed = elapsed </code><code>time</code> <code>in</code> <code>seconds executing</code>

<code>disk = number </code><code>of</code> <code>physical reads </code><code>of</code> <code>buffers </code><code>from</code> <code>disk</code>

<code>query = number </code><code>of</code> <code>buffers gotten </code><code>for</code> <code>consistent </code><code>read</code>

<code>current</code> <code>= number </code><code>of</code> <code>buffers gotten </code><code>in</code> <code>current</code> <code>mode (usually </code><code>for</code> <code>update</code><code>)</code>

<code>rows</code> <code>= number </code><code>of</code> <code>rows</code> <code>processed </code><code>by</code> <code>the </code><code>fetch</code> <code>or</code> <code>execute</code> <code>call</code>

<code>......省略部分内容</code>

<code>SQL ID: ggqns3c1jz86c Plan Hash: 3956160932</code>

<code>select</code> <code>* </code>

<code> </code><code>scott.emp</code>

<code>call </code><code>count</code> <code>cpu elapsed disk query </code><code>current</code> <code>rows</code>

<code>Parse 1 0.00 0.00 0 0 0 0</code>

<code>Execute</code> <code>1 0.00 0.00 0 0 0 0</code>

<code>Fetch</code> <code>2 0.00 0.00 0 7 0 14</code>

<code>total 4 0.00 0.00 0 7 0 14</code>

<code>Misses </code><code>in</code> <code>library cache during parse: 0</code>

<code>Optimizer mode: ALL_ROWS</code>

<code>Parsing </code><code>user</code> <code>id: SYS</code>

<code>Number </code><code>of</code> <code>plan </code><code>statistics</code> <code>captured: 1</code>

<code>Rows</code> <code>(1st) </code><code>Rows</code> <code>(</code><code>avg</code><code>) </code><code>Rows</code> <code>(</code><code>max</code><code>) Row Source Operation</code>

<code> </code><code>14 14 14 </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>EMP (cr=7 pr=0 pw=0 </code><code>time</code><code>=81 us cost=3 </code><code>size</code><code>=532 card=14)</code>

<code>Elapsed times include waiting </code><code>on</code> <code>following events:</code>

<code> </code><code>Event waited </code><code>on</code> <code>Times </code><code>Max</code><code>. Wait Total Waited</code>

<code> </code><code>---------------------------------------- Waited ---------- ------------</code>

<code> </code><code>SQL*Net message </code><code>to</code> <code>client 2 0.00 0.00</code>

<code> </code><code>SQL*Net message </code><code>from</code> <code>client 2 0.00 0.00</code>

MOS文檔EVENT: 10046 "enable SQL statement tracing (including binds/waits)" (文檔 ID 21154.1)

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (文檔 ID 376442.1)

<b> 本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1898624</b><b>，如需轉載請自行聯系原作者</b>

Oracle 10046事件介紹(一)

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List

Oracle 10046事件 介紹(一)

繼續閱讀

Oracle 10046事件介紹(一)