程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle10046事件

oracle10046事件

編輯：Oracle教程

oracle10046事件

10046 事件
Oracle 10046是一個Oracle內部事件。最常用的是在Session級別設置sql_trace(alter session set sql_trace=true)即是開啟了級別為1
的10046調試事件。當設置了10046事件之後，Oracle 將產生一個dump文件。通過得到的dump文件進行進一步分析，可以得到Oracle 內部執行系統解析、調用、等待、綁定變量等詳細的trace信息，對於分析系統的性能有著舉足輕重的作用。
當SQL語句操作出現性能問題時，我們可以用SQL_TRACE 或者10046事件進行跟蹤是最合適的。如果是數據庫整體性能下降，就需要使用statspack或者AWR對數據庫進行分析。

1.10046事件的相關參數

該事件需要設置一些參數以控制dump文件的輸出：
TIMED_STATISTICS
用於控制計時信息，可以設定為true和false。當設定為true時，計時信息將會被添加到trace文件中。

MAX_DUMP_FILE_SIZE
用於控制trace文件的最大尺寸。當使用10046事件時，建議將該參數設定為unlimited。

USER_DUMP_DEST
用於設定trace文件寫入到哪個文件目錄

STATISTICS_LEVEL
用於控制統計信息的收集度。此參數有3個選擇，baisc，typical，all。
basic:僅收集滿足trace所需的最基本的信息，象Timed statistics，Object level statistics，以及一些advisory會被忽略。
typical:此為缺省值。此設置將在basic的基礎上增加一些額外的統計信息，象操作系統耗用時間的統計信息，執行計劃的統計信息都會被收集
all:當設置為all時，所有與該session相關的信息全部會被收集。

TRACEFILE_IDENTIFIER
用於設置識別Trace文件的字符串，便於更快捷的找到生成的Trace文件。
以上參數可以基於系統級別以及會話級別進行修改。
ALTER SESSION/SYSTEM SET timed_statistics=true
ALTER SESSION/SYSTEM SET max_dump_file_size=unlimited

ALTER SESSION SET tracefile_identifier='trace_sql_example' -->僅session級別

2. 10046調試事件的等級

10046調試事件可以分為多個不同的等級，不同的等級輸出不同的trace信息。
等級作用
0 禁止調試事件
1 調試事件處於激活狀態。針對每個被處理的數據庫調用，輸出SQL語句,APPNAME（應用程序名）,PARSING IN CURSOR,PARSE ERROR（SQL解析）
,EXEC（執行）,FETCH（獲取數據）,UNMAP,SORT UNMAP（排序，臨時段）,ERROR,STAT（執行計劃）,XCTEND（事務）等行。
4 包括等級1的輸出，加上BIND行（綁定變量信息）
8 包括等級1的輸出，加上WAIT行（等待事件信息）。對於處理過程中的每個等待，提供如下信息：等待時間的名字，持續時間，以及一些額外
的參數，可表明所等待的資源。
12 輸出等級4以及等級8的所有信息

3. 生成trace文件

[oracle@server8 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 10 16:20:39 2015



sys@GHSJDB> alter session set tracefile_identifier='10046';

Session altered.

sys@GHSJDB>alter session set events '10046 trace name context forever,level 12';(如果沒有權限賦予 alter session 權限)

Session altered.

sys@GHSJDB> select code,name,to_char(parent_code) parent,to_char(connect_by_isleaf) isleaf,lev
2 from ghsj_xm.v_organdataset
3 where level = 1 connect by prior code = parent_code start with code = '14142' order siblings by code
4 ;

CODE NAME
------------------------------------------------------------------------------------------ -----------------------------------
PARENT ISLEAF LEV
------------------------------------------------------------------------------------------ ---------------------------------------- ----------------------------------------
14142 國網湖北省電力公司
14000

sys@GHSJDB>alter session set events '10046 trace name context off';

Session altered.

4. 獲取跟蹤文件

方法1：

sys@GHSJDB> show parameter dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /home/u01/app/oracle/diag/rdbm
s/ghsjdb/ghsjdb/trace
core_dump_dest string /home/u01/app/oracle/diag/rdbm
s/ghsjdb/ghsjdb/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /home/u01/app/oracle/diag/rdbm
s/ghsjdb/ghsjdb/trace

去相應的目錄下：

cd /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace

查找標識符10046：

ls | grep 10046

ghsjdb_ora_9481_10046.trc
ghsjdb_ora_9481_10046.trm

方法2：

取得當前用戶的sid,根據ID取得tracefile的地址

sys@GHSJDB> select * from v$mystat where rownum=1;

SID STATISTIC# VALUE
----- ---------- ----------
247 0 ##########



sys@GHSJDB> SELECT tracefile FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid='247');

TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_9481_10046.trc

5. 格式化輸出tkprof

[oracle@server8 ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor



tkprof /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_9481_10046.trc 10046.log sys=no



[oracle@server8 ~]$ more 10046.log

TKPROF: Release 11.2.0.3.0 - Development on Tue Feb 10 17:11:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 2
Fetch 2 0.25 0.26 0 22123 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.25 0.26 0 22123 0 3

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 18.65 27.67


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
115 lines in trace file.
27 elapsed seconds in trace file.