设置10046 event跟踪SQL执行

kiwixing

于 2024-10-11 08:30:00 发布

阅读量165

点赞数 7

分类专栏： SQL优化文章标签： sql 数据库 oracle database

本文链接：https://blog.csdn.net/Story_begins/article/details/142795837

版权

SQL优化专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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的基础上增加绑定变量和等待事件的信息