使用10046 trace跟踪SQL

levels
1: Standard SQL TRACE                      标准的SQL Trace
4: Includes Bind Variables                 包括绑定变量
8: Includes Waits                          包括等待事件
12: Includes Bind Variables and Waits      包括绑定变量和等待事件

方法：
只能跟踪当前会话

alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';

跟踪当前会话或其他会话

select spid from v$process p,v$sessoin s where p.addr=s.paddr and s.sid in(select sid from v$mystat);

conn / as sysdba
oradebug setospid <PID>
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off —关闭trace

更多办法请参考文章： https://blog.csdn.net/u010692693/article/details/103081054

环境准备

drop table t purge;
create table t ( x int );
alter system flush shared_pool;        --测试需要，生产环境慎重执行
alter system flush buffer_cache;       --测试需要，生产环境慎重执行

开始跟踪

SQL> alter session set events '10046 trace name context  forever,level 12';

Session altered.

SQL> begin
  2      for i in 1 .. 100000
  3      loop
  4          execute immediate
  5          'insert into t values ( '||i||')';
  6      end loop;
  7      commit;  
  8  end;
  9  /

PL/SQL procedure successfully completed.

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

Session altered.

查找生成的跟踪文件

SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv('sessionid')
AND c.name = 'user_dump_dest';

TRACE_FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2199.trc


select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum<=1));

对trace文件进行格式化

[oracle@orasql ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2199.trc /home/oracle/10046.log

TKPROF: Release 11.2.0.4.0 - Development on Sat Jul 22 16:46:35 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@orasql ~]$

查看格式化后的跟踪文件

[oracle@orasql ~]$ vi /home/oracle/10046.log

TKPROF: Release 11.2.0.4.0 - Development on Sat Jul 22 16:46:35 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2199.trc
Sort options: prsela  exeela  fchela
********************************************************************************
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
********************************************************************************

begin
    for i in 1 .. 100000
    loop
        execute immediate
        'insert into t values ( '||i||')';
    end loop;
    commit;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      4.98       5.07          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.99       5.07          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       26.55         26.55
********************************************************************************