通过10046事件跟踪分析执行计划,便于性能优化。其中sql_trace有分三个不同方式的跟踪。
1、全局模式的10046:这种跟踪需要在参数文件设置10046参数;
event="10046 trace name context forever,level 12"
2、会话模式的10046:这只能个跟踪DBA角色用户开启的会话;
开启与关闭跟踪(12为最高级别的):
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';
3、针对某个用户模式的10046:这个通过调用包dbms_system.set_ev:
来跟踪,需要设置包的相关参数,就可以针对某个用户开启的会话进行收集会话详细的操作过程。
启用:Exec dbms_system.set_ev(41,829,10046,12,'');
关闭:Exec dbms_system.set_ev(41,829,10046,0,'');
#.set_ev里面5个参数查看:
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
#以上的参数说明都比较详细,相信大家都看得懂。但是,上面的5个参数中,有一个参数 username ,
最容易误导。说实话,自己开始使用的时候,也犯了这个错误,被套进了这个陷阱,而且不容易发现所犯的错误。
开始参考的例子是这样的:
#开启跟踪: Exec dbms_system.set_ev(26,35,10046,12,’HR’);
#等去查看它出发生成“最 新的trace文件 ” 的时候,并没有生成新的trace文件。
#这时候,我们联想SQL_TRACE触发生成新trace文件的方法:
exec dbms_system.SET_SQL_TRACE_IN_SESSION(26,35,true);
exec dbms_system.SET_SQL_TRACE_IN_SESSION(26,35,false);
即有开启则有关闭才会触发生成新的trace文件。可现在 对于跟踪针对某个用户的会话的 10046事件没有
完整的触发机制,没有start/stop,也没有begin/end,更没有TRUE/FALSE等参数值。那该怎么办呢?
#通过查看先前探索者们的资料叙述,大家都开始针对NM这个参数下手。 我就尝试了三种NM的可能参数值:
1、NM=SUXING --当前会话的数据库 用户名
2、NM=null --空值
3、NM='' --空值 (与2、中的空值表示法不一样 )
#最后发现,1、2两种的参数值都不能使10046事件 触发生成新的trace文件,只有3方法触发生成了新的trace文件。这样才得以让我的测试顺利完成。
以下是我的测试过程:
----10046事件跟踪文件:
---查看会话的SID:
sys@PROD>select sid,serial#,username from v$session
2 where username in ('SUXING','SUSU');
SID SERIAL# USERNAME
---------- ---------- ------------------------------
41 829 SUXING
44 417 SUSU
sys@PROD>Exec dbms_system.set_ev(41,829,10046,12,'SUXING');
PL/SQL procedure successfully completed.
#后面发现该调用不能触发生成新的trace文件。---最新的调用方法:
---开启跟踪:
sys@PROD>Exec dbms_system.set_ev(41,829,10046,12,'');
PL/SQL procedure successfully completed.
---用户会话的系列操作:
suxing@PROD>delete from yourtest where id =127;
5 rows deleted.
suxing@PROD>insert into yourtest values
2 (127,'haha',sysdate,'man');
1 row created.
suxing@PROD>commit;
Commit complete.
suxing@PROD>select * from yourtest;
ID NAME CREATED SEX
---------- ------ --------- -----
125 susu 02-NOV-16 man
123 yyyy 02-NOV-16 man
124 hhhh 10-NOV-16 man
126 wwww 10-NOV-16 man
125 dddd 11-NOV-16 woman
123 yyyy 24-NOV-16 man
127 haha 06-DEC-16 man
7 rows selected.
#总共1条删除,1条插入,1条查询操作。---禁用(关闭)跟踪:
sys@PROD>Exec dbms_system.set_ev(41,829,10046,0,'');
PL/SQL procedure successfully completed.
---查看最新生成的trace文件:
[oracle@enmo trace]$ ls -lrt
... ...
-rw-r--r-- 1 oracle oinstall 6801 Dec 6 17:59 my15504.sql
-rw-r----- 1 oracle oinstall 1376 Dec 6 18:24 PROD_mmon_4603.trm
-rw-r----- 1 oracle oinstall 13945 Dec 6 18:24 PROD_mmon_4603.trc
-rw-r----- 1 oracle oinstall 498 Dec 6 18:25 PROD_ora_15504.trm
-rw-r----- 1 oracle oinstall 81204 Dec 6 18:25 PROD_ora_15504.trc
---转换trace文件容易读取的文件格式:
[oracle@enmo trace]$ tkprof PROD_ora_15504.trc my10046_15504_20161206.sql
TKPROF: Release 11.2.0.4.0 - Development on Tue Dec 6 18:29:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
---读取新生成trace文件的内容:
[oracle@enmo trace]$ ls my10046_15504_20161206.sql
my10046_15504_20161206.sql
[oracle@enmo trace]$
[oracle@enmo trace]$ cat my10046_15504_20161206.sql
... ...
SQL ID: ftj9uawt4wwzb Plan Hash: 1884964958
select condition
from
cdef$ where rowid=:1
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 0
Fetch 4 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 8 0 4
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: c75j1y27gdy77 Plan Hash: 1899096591
delete from yourtest
where
id =127
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 262 0 0
Execute 1 0.00 0.00 0 8 13 5
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 270 13 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE YOURTEST (cr=8 pr=0 pw=0 time=413 us)
5 5 5 TABLE ACCESS FULL YOURTEST (cr=6 pr=0 pw=0 time=105 us cost=4 size=9 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 16.19 16.19
********************************************************************************
SQL ID: 23wm3kz7rps5y Plan Hash: 0
commit
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 2 0
Misses in library cache during parse: 0
Parsing user id: 90
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 6.11 6.11
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 264 0 0
Execute 7 0.00 0.00 0 14 35 7
Fetch 4 0.00 0.00 0 14 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 292 35 25
Misses in library cache during parse: 5
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 4 16.19 25.26
log file sync 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 89 0.02 0.02 0 0 0 0
Fetch 97 0.00 0.00 0 266 0 719
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.03 0.03 0 266 0 719
Misses in library cache during parse: 12
Misses in library cache during execute: 12
7 user SQL statements in session.
14 internal SQL statements in session.
21 SQL statements in session.
********************************************************************************
Trace file: PROD_ora_15504.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
7 user SQL statements in trace file.
14 internal SQL statements in trace file.
21 SQL statements in trace file.
16 unique SQL statements in trace file.
1613 lines in trace file.
1661 elapsed seconds in trace file.
[oracle@enmo trace]$
#该博文篇幅较长,但是总体条理还比较清晰, 谢谢您的查阅!来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2129897/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2129897/