设置10046 event跟踪SQL执行
event 10046: 跟踪SQL执行计划,存储过程,递归语句,等待事件
oradebug event 10046
mypid 当前会话
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> select count(*) from dba_objects where object_id=40400;
COUNT(*)
----------
1
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15942.trc
setospid 其它会话
SQL> select sid from v$mystat where rownum < 2;
SID
----------
31
SQL> select paddr from v$session where sid=31;
PADDR
----------------
00000000914BF9A8
SQL> select spid from v$process where addr='00000000914BF9A8';
SPID
------------------------
2341 ------------------查询到对应会话的ospid
select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum < 2));
SQL> oradebug setospid 2341
Oracle pid: 26, Unix process pid: 2341, image: oracle@DB-X (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever ,level 12
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2341.trc
sqlplus set events
session级别
SQL> alter session set events '10046 trace name context forever ,level 12';
Session altered.
SQL> select count(*) from dba_objects where object_id=40402;
COUNT(*)
----------
1
SQL> alter session set events '10046 trace name context off';
Session altered.
system级别(谨慎使用!!!通常不用)
SQL> alter system set events '10046 trace name context forever ,level 12';
System altered.
SQL> alter system set events '10046 trace name context off';
格式化10046 trace
tkprof格式化10046跟踪输出文件
[oracle@kiwi trace]$ tkprof xdb_ora_20620.trc tfprof_10046_gather_statistics.trc --->指定格式化后文件
TKPROF: Release 11.2.0.4.0 - Development on Sat Dec 12 04:59:19 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@kiwi trace]$ ls -lrt tfprof_10046_gather_statistics.trc
-rw-r--r-- 1 oracle oinstall 168626 Dec 12 04:59 tfprof_10046_gather_statistics.trc
level级别含义
Level 0 | 停用SQL跟踪,相当于SQL_TRACE=FALSE |
Level 1 | 标准SQL跟踪,相当于SQL_TRACE=TRUE |
Level 4 | 在level 1的基础上增加绑定变量的信息 |
Level 8 | 在level 1的基础上增加等待事件的信息 |
Level 12 | 在level 1的基础上增加绑定变量和等待事件的信息 |
原文地址:https://blog.csdn.net/Story_begins/article/details/142795837
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!