10046事件是Oracle提供的內(nèi)部事件，是增強版的SQL_TRACE。

一、10046的級別

不同的Level對應(yīng)不同的跟蹤級別：

0級：SQL_TRACE=FASLE。

1級：SQL_TRACE=TRUE，默認(rèn)級別。啟用標(biāo)準(zhǔn)的SQL_TRACE功能 ( 默認(rèn)) 包含了 SQL語句、響應(yīng)時間、服務(wù)時間、處理的行數(shù)，物理讀和寫的數(shù)目、執(zhí)行計劃以及其他一些額外信息。到版本10.2中 執(zhí)行計劃寫入到 trace 的條件是僅當(dāng)相關(guān)游標(biāo)已經(jīng)關(guān)閉時， 且與之相關(guān)的執(zhí)行統(tǒng)計信息是所有執(zhí)行次數(shù)的總和數(shù)據(jù)。到版本11.1中僅在每次游標(biāo)的第一次執(zhí)行后將執(zhí)行計劃寫入到trace ，執(zhí)行統(tǒng)計信息僅僅和這第一次執(zhí)行相關(guān)。

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

8級：比level 1多出等待事件，特別對于9i中指出 latch free等待事件很有用，對于分析全表掃描和索引掃描很有用。

12級：4級+8級，比level 1多出綁定變量和等待事件。

16級：在11g中為每一次執(zhí)行生成STAT信息，僅在11.1之后可用

32級：比level1少執(zhí)行計劃

64級：和level1相比在第一次執(zhí)行后還可能生成執(zhí)行計劃信息；條件是某個游標(biāo)在前一次執(zhí)行的前提下 運行耗時變長了一分鐘。僅在 11.2.0.2中可用。

Level 28 (4+8+16) 代表同時啟用level4、level8、level16。

level 68 ( 64+4 )代表同時啟用level64、level4

對于4級的10046，若用tkprof格式化，則會隱藏每一點SQL語句在做什么以及怎么做。

對于8級的10046，等待事件得到的比較多，此時我們不妨用tkprof對等待事件進(jìn)行匯總。

所以，理解擴展SQL跟蹤文件的格式，是每一個面臨性能問題或故障排除任務(wù)的數(shù)據(jù)庫工程師必備技能。

二、10046 trace文件路徑

從11gR1開始，Oracle引入了新的診斷結(jié)構(gòu)，以參數(shù)DIAGNOSTIC_DEST控制存放trace文件與core文件的路徑。可以用以下命令，獲取DIAGNOSTIC_DEST的位置：

SQL> show parameter diagnostic_dest;NAME?????????????????????????????TYPE???????VALUE------------------------------------ ----------- ------------------------------diagnostic_dest string /oracle/app/oracle

11gR1以前，如果是用戶進(jìn)程，10046 trace文件會被生成在user_dump_dest下，如果是后臺進(jìn)程，trace文件會被生成在background_dump_dest下：

SQL> show parameter user_dump_dest ;NAME?????????????????????????????TYPE???????VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /oracle/app/oracle/diag/rdbms/xddb/xddb/traceSQL> show parameter background_dump_dest;NAME?????????????????????????????TYPE???????VALUE------------------------------------ ----------- ------------------------------background_dump_dest string /oracle/app/oracle/diag/rdbms/xddb/xddb/trace

注：

通過設(shè)定tracefile_identifier即alter session set tracefile_identifier='my_trace'?，通過這個設(shè)置可以幫助我們更容易的找到生成的trace文件。

通過SQL查看當(dāng)前session的trace文件位置：

select a.value || '/' || b.instance_name || '_ora_' || c.spid || '.trc' trace_file from (select value from v$parameter where name = 'user_dump_dest') a, (select instance_name from v$instance) b, (select spid from v$process where addr = (select paddr from v$session where sid = (select distinct sid from v$mystat))) c;或：select tracefile from v$process where addr = (select paddr from v$session where sid = (select distinct sid from v$mystat));

三、10046 trace相關(guān)參數(shù)

1.timed_statistics

timed_statistics這個參數(shù)決定了是否收集與時間相關(guān)的統(tǒng)計信息，如果這個參數(shù)為FALSE的話，那么SQL Trace的結(jié)果基本沒有多大的用處，默認(rèn)情況下這個參數(shù)設(shè)置為TRUE。

SQL> show parameter timed_statistics ;NAME TYPE VALUE------------------------------------ ----------- ------------------------------timed_statistics boolean TRUESQL> ALTER SESSION SET timed_statistics=true ;

2.max_dump_file_size

max_dump_file_size這個參數(shù)指定dump文件的大小，也就是決定是否限制SQL Trace文件的大小，在一個很忙的系統(tǒng)上面做SQL Trace的話可能會生成很多的信息，因此最好在會話級別將這個參數(shù)設(shè)置成unlimited(默認(rèn))。

SQL> show parameter max_dump_file_size;NAME TYPE VALUE------------------------------------ ----------- ------------------------------max_dump_file_size string unlimitedSQL> ALTER SESSION SET max_dump_file_size=unlimited ;

3.?tracefile_identifier

tracefile_identifier這個參數(shù)給Trace文件設(shè)置識別字符串，設(shè)置一個易讀的字串能更快的找到Trace文件。

SQL> show parameter ?tracefile_identifier;

NAME TYPE VALUE------------------------------------ ----------- ------------------------------tracefile_identifier string當(dāng)前session啟用：SQL> ALTER SESSION SET tracefile_identifier=’my_trace’;

??4. diagnostic_dest

diagnostic_dest這個參數(shù)11g新增的，用于控制存放trace文件與core文件的路徑，默認(rèn)是$ORACLE_BASE目錄。

SQL> show parameter diagnostic_dest;NAME TYPE VALUE------------------------------------ ----------- ------------------------------diagnostic_dest string /oracle/app/oracle

5. user_dump_dest

user_dump_dest參數(shù)指定用戶進(jìn)程trace文件目錄。

SQL> show parameter user_dump_dest ;NAME?????????????????????????????TYPE???????VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /oracle/app/oracle/diag/rdbms/xddb/xddb/trace SQL> alter system set user_dump_dest ='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目錄

6.?background_dump_dest

background_dump_dest參數(shù)用于后臺進(jìn)程產(chǎn)生的trace文件存儲目錄下。

SQL> show parameter background_dump_dest ;NAME?????????????????????????????TYPE???????VALUE------------------------------------ ----------- ------------------------------background_dump_dest string /oracle/app/oracle/diag/rdbms/xddb/xddb/traceSQL> alter system setbackground_dump_dest='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目錄

四、10046 trace設(shè)置方法

4.1全局設(shè)置

在參數(shù)文件中增加:

event="10046 trace name context forever,level 12"

此設(shè)置對所有用戶的所有進(jìn)程生效、包括后臺進(jìn)程。

如果要關(guān)閉可以通過移除這個參數(shù)并且重啟實例，或者使用下面的alter system命令可以關(guān)閉跟蹤。

SQL> alter system set events '10046 trace name context off';

4.2實例級別跟蹤

?在實例級別設(shè)置跟蹤需要非常小心，這是因為整體性能會由于所有session都被跟蹤而受到影響，這個設(shè)置將會跟蹤在這個參數(shù)設(shè)置“以后”創(chuàng)建的每個session。已經(jīng)存在的session不會被跟蹤，系統(tǒng)層的10046跟蹤適用于當(dāng)我們知道問題session會出現(xiàn)，但是不能預(yù)先識別它的時候，在這種情況下，可以打開系統(tǒng)層跟蹤小段時間，當(dāng)問題被重現(xiàn)以后立即將其關(guān)閉，然后從已經(jīng)生成的trace中查找需要的信息，用以下命令打開系統(tǒng)層的跟蹤：

SQL> alter system set events '10046 trace name context forever,level 12'; 用以下命令關(guān)閉在所有session中的10046跟蹤： SQL> alter system set events '10046 trace name context off';

4.3對當(dāng)前session設(shè)置

(1)語法：

alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'

(2)通過alter session的方式修改，需要alter session的系統(tǒng)權(quán)限:

SQL> alter session set tracefile_identifier='my_trace_10046'; --trace文件標(biāo)識SQL> alter session set statistics_level=all; --實例級別設(shè)置需要注意，會消耗大量的CPUSQL> alter session set timed_statistics = true; --默認(rèn)是trueSQL> alter session set max_dump_file_size = unlimited; --默認(rèn)是unlimitedSQL> alter session set events '10046 trace name context forever, level 12'; --開啟level 12級別的10046事件。Session altered.SQL> select count(*) from test.test; COUNT(*)---------- 227626SQL> alter session set events '10046 trace name context off';Session altered.

(3)查看session sql trace文件

SQL> select tracefile from v$process where addr = (select paddr from v$session where sid = (select distinct sid from v$mystat));TRACEFILE--------------------------------------------------------------------------------/oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

4.4對其他用戶session設(shè)置

4.4.1通過DBMS_SYSTEM.SET_EV系統(tǒng)包來實現(xiàn)

SQL> desc dbms_system...PROCEDURE SET_EV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SI BINARY_INTEGER IN SE BINARY_INTEGER IN EV BINARY_INTEGER IN LE BINARY_INTEGER IN NM VARCHAR2 IN...

其中的參數(shù)SI(SID)、SE(SERIAL#)、NM(username)來自v$session視圖:

SQL> select sid,serial#,username from v$session;SID SERIAL# USERNAME---------- ---------- ------------------------------ 5 52733 TEST 492 4299 SYS執(zhí)行跟蹤:SQL> exec dbms_system.set_ev(5,52733,10046,12,'');PL/SQL procedure successfully completed.SQL> exec dbms_system.set_sql_trace_in_session(5,52733,true); --有時候需要提前開啟SQL_TRACE才可以生成trace文件。PL/SQL procedure successfully completed

測試執(zhí)行SQL語句：

SQL> select * from tab;TNAME?????????????????????????TABTYPE??????CLUSTERID------------------------------ ------- ----------COURSE TABLESC TABLESTUDENT TABLETEACHER TABLETEST TABLE5?rows?selected.SQL>?select?*?from?course;??????C_ID?C_NAME??????????????????????????T_ID---------- -------------------------------- ---------- 1 語文 1 2 數(shù)學(xué) 2 3 英語 3 4 物理 4

結(jié)束跟蹤：

SQL> exec dbms_system.set_sql_trace_in_session(5,52733,false);SQL> exec dbms_system.set_ev(5,52733,10046,0,'');PL/SQL procedure successfully completed.

獲取trace文件：

SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=5);

讀取當(dāng)前session設(shè)置的參數(shù)：

declare lev integer; begin dbms_system.read_ev('10046',lev); dbms_output.put_line(lev); end;/

針對所有event：

SQL> set feedback offSQL> set serveroutput onSQL> declareevent_level number;beginfor event_number in 10000..10999 loopsys.dbms_system.read_ev(event_number, event_level);if (event_level > 0) thensys.dbms_output.put_line('Event ' ||to_char(event_number) || ' is set at level ' || to_char(event_level) ); end if; end loop; end; /

4.4.2通過DBMS_MONITOR.SESSION_TRACE_ENABLE實現(xiàn)

PROCEDURE SESSION_TRACE_ENABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SESSION_ID BINARY_INTEGER IN DEFAULT SERIAL_NUM BINARY_INTEGER IN DEFAULT WAITS BOOLEAN IN DEFAULT BINDS BOOLEAN IN DEFAULT PLAN_STAT VARCHAR2 IN DEFAULT-- 實例-- 開啟級別為12的Trace，當(dāng)前會話EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);-- 開啟級別為12的Trace，其他會話EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);-- 關(guān)閉Trace，當(dāng)前會話EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();-- 關(guān)閉Trace，其他會話EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);

????4.4.3?10046事件進(jìn)程級別跟蹤

假設(shè)需要對9834號進(jìn)程做跟蹤，其語法如下所示：

conn / as sysdbaalter session set tracefile_identifier = 'trace_10046'oradebug setospid=9834oradebug unlimitoradebug evnet 10046 trace name context forever,level 12oradebug dump errorstack 3oradebug dump errorstack 3oradebug dump errorstack 3oradebug tracefile_nameoradebug evnet 10046 trace name context off

五、分析trace文件

5.1?可直接查看

more /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

trace?文件可以看出?sql?語句經(jīng)過了?parse(解析) -> exec(執(zhí)行) -> fetch(從游標(biāo)中獲取數(shù)據(jù))?幾個過程。這3個分類與通過調(diào)用DBMS_SQL的子例程DBMS_SQL.PARSE,DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS來跑SQL的步調(diào)相一致。

相關(guān)內(nèi)容含義：

(1)解析含義

解析在跟蹤文件中通常通過兩個相鄰的條目表示

第一個是PARSING IN CURSOR，第二個是PARSE。

PARSING IN CURSOR #140614507473560 len=30 dep=0 uid=0 oct=3 lid=0 tim=1598323647702391 hv=1715741776 ad='dd80da38' sqlid='br5bhw1m4892h'select count(*) from test.testEND OF STMTPARSE #140614507473560:c=46992,e=128564,p=6,cr=130,cu=0,mis=1,r=0,dep=0,og=1,plh=1950795681,tim=1598323647702390

(2)SQL具體執(zhí)行過程以及CPU等性能指標(biāo)

PARSE #140614507449024:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647601076EXEC #140614507449024:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647601322FETCH #140614507449024:c=1000,e=5896,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=299250003,tim=1598323647607235FETCH #140614507449024:c=0,e=8,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647607293

(3)執(zhí)行過程中發(fā)生的等待

WAIT #140614507449024: nam='db file sequential read' ela= 5664 file#=1 block#=81256 blocks=1 obj#=42 tim=1598323647607122

(4)commit和rollback和XCTEND條目格式

XCTEND rlbk=0, rd_only=0

?Oracle不需要客戶端顯示地開始一個事務(wù)，DBMS在第一個數(shù)據(jù)項目被修改或分布式操作執(zhí)行后會自動打開一個事務(wù)

比如，通過dblink從一個表執(zhí)行select

在trc中事務(wù)的邊界通過XCTEND條目標(biāo)記，格式如下：

XCTEND rlbk=[0-1],rd_only=[0-1]

(5)游標(biāo)執(zhí)行計劃、統(tǒng)計信息與STAT條目格式

一組STAT條目的每一行代表了形成語句結(jié)果的行源

所謂的行源，指從索引或表中檢索的數(shù)據(jù)或者多表連接的中間結(jié)果(因為必須先進(jìn)行兩表連接)。

10g以后，STAT條目僅在TIMED_STATISTICS=TRUE,并且SQL_TRACE=TRUE時才被寫入。

注意，若STATISTICS_LEVEL=BASIC(缺省為TYPICAL)時會隱式設(shè)置TIMED_STATISTICS=FASLE。

STAT #140614507449024 id=1 cnt=1 pid=0 pos=1 obj=20 op='TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=1 pw=0 time=5899 us cost=2 size=54 card=2)'

(6)????? 等待事件和WAIT條目格式

WAIT #140614507473560: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1598323647702555WAIT #140614507473560: nam='Disk file operations I/O' ela= 48 FileOperation=2 fileno=4 filetype=2 obj#=265662 tim=1598323647702747WAIT #140614507473560: nam='db file sequential read' ela= 3857 file#=4 block#=586 blocks=1 obj#=265662 tim=1598323647706628(7)綁定變量和BINDS條目格式

綁定變量的詳細(xì)信息包括綁定變量的數(shù)據(jù)類型和值。

通過這些信息我們可以得到最大化的診斷。

例如，索引列的數(shù)據(jù)類型與綁定變量的數(shù)據(jù)類型不匹配，導(dǎo)致索引失效，CPU使用率增加，因為還存在隱式數(shù)據(jù)類型轉(zhuǎn)換。

一個BINDS條目的結(jié)構(gòu)由后面跟著游標(biāo)編號的單詞BINDS和每一個綁定變量單獨的子部分組成

BINDS #140614507455464: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7fe35dc9f8d8 bln=22 avl=02 flg=05 value=31 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7fe35dc9f8f0 bln=22 avl=02 flg=01 value=11

當(dāng)將綁定變量與子部分相關(guān)聯(lián)時，不用關(guān)心數(shù)字，他們會被包含在綁定變量的名稱中，例如 ":B"。

5.2 tkprof工具查看

tkprof /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc tkprof_10046.txtSQL ID: br5bhw1m4892h Plan Hash: 1950795681select?count(*)from test.testcall?????count???????cpu????elapsed???????disk??????query????current????????rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.14 0.22 3135 3139 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.14 0.24 3135 3139 0 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=3139 pr=3135 pw=0 time=227352 us) 227626 227626 227626 TABLE ACCESS FULL TEST (cr=3139 pr=3135 pw=0 time=684591 us cost=855 size=0 card=227626)Elapsed?times?include?waiting?on?following?events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 Disk file operations I/O 1 0.00 0.00 db file sequential read 1 0.00 0.00 db file scattered read 40 0.01 0.10 SQL*Net message from client 2 12.47 12.47******************************************************************************** 《新程序員》：云原生和全面數(shù)字化實踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的oracle 增加ora容量_Oracle的10046事件详解的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。