我們在做sql優(yōu)化的時候會用到10046和10053跟蹤診斷，當(dāng)然其他場景也會用到。今天在讀《DBA攻堅指南》的時候又看到了10053的知識，這里對這兩個做個總結(jié)。
一.10046事件
1.1.10046事件介紹
10046事件是Oracle提供的內(nèi)部跟蹤事件，是對SQL_TRACE的增強，通過10046可以通知Oracle內(nèi)核執(zhí)行SQL_TRACE類的跟蹤操作。
如果我們需要獲得更多的跟蹤信息，就需要用到10046事件，而在實際工作中最常用的就是10046事件。
1.2.10046事件跟蹤級別
- 1 - 啟用標(biāo)準(zhǔn)的SQL_TRACE功能,等價于SQL_trace
- 4 - Level 1 加上綁定值(bind values) [ bind=true ]
- 8 - Level 1 + 等待事件跟蹤 [ wait=true ]
- 12 - Level 1 + Level 4 + Level 8
從11g開始增加了以下兩個跟蹤級別。 - 16 – 為每次SQL執(zhí)行生成STAT信息輸出 [ plan_stat=all_executions ]
- 32 – 不轉(zhuǎn)儲執(zhí)行統(tǒng)計信息 [ plan_stat=never ]
從 11.2.0.2開始增加以下級別。 - 64 – 自適應(yīng)的STAT轉(zhuǎn)儲 [ plan_stat=adaptive ]
1.3.啟用和停止
--啟用跟蹤
SQL> alter session set events '10046 trace name context forever, level 12';
--停止跟蹤
SQL> alter session set events '10046 trace name context off';
--11g之后的可選設(shè)置方式類似。
alter session set events 'SQL_trace wait=true';
--對其他session設(shè)置跟蹤，可以通過DBMS_SYSTEM的SET_EV過程來實現(xiàn)
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、SE來自v$session視圖
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 EYGLE
--執(zhí)行跟蹤
SQL> exec dbms_system.set_ev(9,437,10046,8,'');
PL/SQL procedure successfully completed.
--結(jié)束跟蹤
SQL> exec dbms_system.set_ev(9,437,10046,0,'');
PL/SQL procedure successfully completed.
1.4.產(chǎn)生的跟蹤文件
基于會話級別跟蹤產(chǎn)生的文件，可以通過查詢V$DIAG_INFO視圖，找到跟蹤文件的名稱和位置信息，查看其中的內(nèi)容。
select value TRACE_FILE from v$diag_info where name='Default Trace File';
--或者
show parameter user_dump_dest
1.5.對生成的trc文件進行格式化
tkprof
[oracle@single ~]$ 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
[oracle@single ~]$
1.6.示例
我們就用select * from scott.dept;這個作為示例，來看看它詳細的執(zhí)行計劃。
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select value TRACE_FILE from v$diag_info where name='Default Trace File';
TRACE_FILE
--------------------------------------------------------------------------------
/data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1853.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@single ~]$ cd /data/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@single trace]$ tkprof orcl_ora_1853.trc 10046.txt
TKPROF: Release 11.2.0.4.0 - Development on Fri May 6 21:09:19 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@single trace]$
[oracle@single trace]$ cat 10046.txt
TKPROF: Release 11.2.0.4.0 - Development on Fri May 6 21:09:19 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: orcl_ora_1853.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
********************************************************************************
SQL ID: f6hhpzwv5jrna Plan Hash: 3383998547
select *
from
scott.dept
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.07 0 7 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=448 us cost=3 size=108 card=6)
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
SQL*Net message from client 2 8.05 8.05
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.07 0.07 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.07 0.07 0 7 0 4
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 8.05 15.55
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
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: orcl_ora_1853.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
50 lines in trace file.
8 elapsed seconds in trace file.
[oracle@single trace]$
二.10053事件
2.1.10053事件介紹
當(dāng)一個SQL出現(xiàn)性能問題時，可以使用SQL_TRACE或10046事件來跟蹤SQL，通過生成的跟蹤信息來了解SQL的執(zhí)行過程。
當(dāng)我們查看一條SQL的執(zhí)行計劃時，只能看到CBO最終顯示的執(zhí)行計劃結(jié)果，卻不知道CBO是根據(jù)什么來做的。
如果遇到了執(zhí)行計劃異常，則可以借助Oracle 10053事件進行跟蹤。
10053事件是Oracle提供的用于跟蹤SQL語句成本計算的內(nèi)部事件，它能記載CBO模式下，oracle優(yōu)化器是如何計算SQL成本，并生成相應(yīng)的執(zhí)行計劃的。
注意：10053只對CBO有效，而且如果一個SQL語句已經(jīng)解析過，就不會產(chǎn)生新的跟蹤信息。
2.2.啟用和停止
--啟用
alter session set EVENTS='10053 trace name context forever,level 1';
or
alter session set EVENTS='10053 trace name context forever,level 2';
--停止
alter session set EVENTS '10053 trace name context off';
--對其他session設(shè)置
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
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 EYGLE
執(zhí)行跟蹤。
SQL> exec dbms_system.set_ev(9,437,10053,1,'');
PL/SQL procedure successfully completed.
結(jié)束跟蹤。
SQL> exec dbms_system.set_ev(9,437,10053,0,'');
PL/SQL procedure successfully completed.
2.3.示例
SQL> alter session set EVENTS='10053 trace name context forever,level 2';
Session altered.
SQL> explain plan for select * from scott.dept;
Explained.
SQL> alter session set EVENTS '10053 trace name context off';
Session altered.
SQL> select value TRACE_FILE from v$diag_info where name='Default Trace File';
TRACE_FILE
--------------------------------------------------------------------------------
/data/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3099.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@single trace]$ cd select value TRACE_FILE from v$diag_info where name='Default Trace File';
-bash: cd: select: No such file or directory
[oracle@single trace]$ cd /data/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@single trace]$ more orcl_ora_3099.trc
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關(guān)內(nèi)容。
