深入理解Oracle調試事件：10046事件詳解

       之前也寫過一篇10046的文章：10046簡介

       今天，Think想和大家一起共同深入去理解一下Oracle的這些調試事件
     
       10046事件是SQL_TRACE的擴充，被戲稱為"吃了興奮劑的SQL_TRACE"
       有效追蹤層級：
       
       ① 0級：SQL_TRACE=FASLE
       ② 1級：SQL_TRACE=TRUE，這是預設層級
       ③ 4級：1級+綁定變數
       ④ 8級：4級+等待事件
       ⑤ 12級：4級+8級
       
       對於4級的10046，若用tkprof格式化，則會隱藏每一點SQL語句在做什麼以及怎麼做
       對於8級的10046，等待事件散得到處多是，此時我們不妨用tkprof對等待事件進行匯總
       所以，理解擴充SQL追蹤檔案的格式，是每一個面臨效能問題或故障排除任務的DBA的必備技能
          
       ㈠ 為什麼需要10046？
     
          對一個擁有alter session許可權但是沒有被授權DBA角色的資料庫使用者
          alter session set events是在他自己會話中啟動擴充SQL跟蹤的唯一方法
          通過這種方法將等待事件或者綁定變數包含在SQL追蹤檔案中，然後進行最佳化或者錯誤診斷    
          10046 看到語句的執行過程，知道語句的執行計畫，包括各種步驟，關聯方式，分別在哪些步驟耗時多少，有哪些等待事件等
          這些都是最佳化的基礎，知道這些，就知道該如何最佳化以及troubleshoting
          
       
     
       ㈡ 如何擷取trc檔案？

          這裡主要介紹3種方法
          
          ① 使用tracefile_identifier，比如：
             alter session set tracefile_identifier='Think'

          ② oradebug,詳細請請參看Think之前的一篇部落格：oradebug

          ③ 使用下面的指令碼：

hr@ORCL> edWrote file afiedt.buf  1  select  2         d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name  3    from  4         ( select p.spid  5             from v$mystat m,v$session s,v$process p  6            where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,  7            ( select t.instance from v$thread  t,v$parameter  v  8               where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,  9*             ( select value from v$parameter where name = 'user_dump_dest') dhr@ORCL> /TRACE_FILE_NAME-----------------------------------------------------------------/u01/app/oracle/admin/orcl/udump/orcl_ora_4012.trc

       ㈢ 如何讀懂10046事件的檔案？
       
       ① 資料庫調用
       
       含3個子分類：解析，執行和擷取
       這3個分類與通過調用DBMS_SQL的子常式DBMS_SQL.PARSE,DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS來跑SQL的步調相一致
       
       解析
       解析在追蹤檔案中通常通過兩個相鄰的條目表示
       第一個是PARSING IN CURSOR,第二個是PARSE

PARSING IN CURSOR #9 len=28 dep=0 uid=55 oct=2 lid=55 tim=1327904235010505 hv=119728103 ad='2fc6ae84'insert into t values('ooxx')END OF STMTPARSE #9:c=52003,e=65698,p=0,cr=30,cu=0,mis=1,r=0,dep=0,og=1,tim=1327904235010494

     

     

       執行和擷取同解析在格式上是一樣的，這裡就不贅述了

       ② commit和rollback和XCTEND條目格式

XCTEND rlbk=0, rd_only=0

       Oracle不需要用戶端顯示地開始一個事務，DBMS在第一個資料項目被修改或分布式操作執行後會自動開啟一個事務
       比如，通過dblink從一個表執行select
       在trc中事務的邊界通過XCTEND條目標記，格式如下：
       XCTEND rlbk=[0-1],rd_only=[0-1]

     

      

       ③ 執行計畫，統計資訊與STAT條目格式
       
       STAT條目報告了執行計畫和統計資訊

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=194 us)'STAT #6 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=95 us)'  

       一組STAT條目的每一行代表了形成語句結果的行源
       所謂的行源，指從索引或表中檢索的資料或者多表串連的中間結果(因為必須先進行兩表串連)
       
       10g以後，STAT條目僅在TIMED_STATISTICS=TRUE,並且SQL_TRACE=TRUE時才被寫入
       請注意，若STATISTICS_LEVEL=BASIC(預設為TYPICAL)時會隱式設定TIMED_STATISTICS=FASLE

     

     

       ④ 等待事件和WAIT條目格式

WAIT #9: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=52523 tim=1327922883350249WAIT #9: nam='SQL*Net message from client' ela= 301 driver id=1650815232 #bytes=1 p3=0 obj#=52523 tim=1327922883350743WAIT #11: nam='db file sequential read' ela= 253 file#=1 block#=420 blocks=1 obj#=355 tim=1327923455671258WAIT #11: nam='db file sequential read' ela= 7073 file#=1 block#=43998 blocks=1 obj#=355 tim=1327923455678537WAIT #11: nam='db file sequential read' ela= 91 file#=1 block#=43999 blocks=1 obj#=355 tim=1327923455678836WAIT #11: nam='db file sequential read' ela= 14433 file#=1 block#=53521 blocks=1 obj#=355 tim=1327923455693393

     

       ⑤ 綁定變數和BINDS條目格式
       
       綁定變數的詳細資料包括綁定變數的資料類型和值
       通過這些資訊我們可以得到最大化的診斷
       例如，索引列的資料類型與綁定變數的資料類型不匹配，導致索引失效，CPU使用率增加，因為還存在隱式資料類型轉換
       一個BINDS條目的結構由後面跟著遊標編號的單詞BINDS和每一個綁定變數單獨的子部分組成

BINDS #9:kkscoacd Bind#0  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0  kxsbbbfp=b7ee5a5c  bln=22  avl=02  flg=05  value=20 Bind#1  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0  kxsbbbfp=b7ee5a38  bln=24  avl=02  flg=05  value=3

       當將綁定變數與子部分相關聯時，不用關心數字，他們會被包含在綁定變數的名稱中，例如 ":B1"

     

     

     

hr@ORCL> select dump(employee_id) from employees where rownum=1;DUMP(EMPLOYEE_ID)----------------------------------------------------------------------------------------------------Typ=2 Len=2: 194,2

       這裡的Typ就是資料類型編號
       
       下次Think再把最佳化或者診斷的案例給附上，此文未完待續哦，，