如何收集用来诊断性能问题的10046 Trace

最新推荐文章于 2021-04-03 20:53:00 发布

wluckdog

最新推荐文章于 2021-04-03 20:53:00 发布

阅读量650

点赞数

分类专栏： oracle基础

oracle基础专栏收录该内容

114 篇文章 1 订阅

订阅专栏

文档 ID 1523462.1

Trace文件的位置

11g R1以上:

从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。
可以用以下命令，获取DIAGNOSTIC_DEST的位置：

SQL> show parameter diagnostic_dest

10046事件说明
10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.
10046事件可以设置以下四个级别:
1 - 启用标准的SQL_TRACE功能,等价于sql_trace
4 - Level 1 加上绑定值(bind values)
8 - Level 1 + 等待事件跟踪
12 - Level 1 + Level 4 + Level 8
类似sql_trace，10046事件可以在全局设置，也可以在session级设置。

在Session级打开trace

适用于SQL语句可以在新的session创建后再运行。
在session级收集10046 trace：

 
  alter session set tracefile_identifier='10046'; 
 
 alter session set timed_statistics = true;
 alter session set statistics_level=all;
 alter session set max_dump_file_size = unlimited;
 
 alter session set events '10046 trace name context forever,level 12';
  
  -- 执行需要被trace的SQL -- 
  
 select * from dual;
 exit;

如果不退出当前session, 可以用以下命令关闭trace:

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

注意，如果session没有被彻底地关闭并且跟踪被停止了，某些重要的trace信息的可能会丢失。

注意：这里我们将"statistics_level"设置为all，这是因为有可能这个参数在系统级不是默认值"TYPICAL"（比如 BASIC）。为了收集性能相关问题的信息我们需要打开某个级别的statistics。我们推荐在 session 级将这个参数设置成 ALL 以便于收集更多的信息，尽管这不是必须的。

跟踪一个已经开始的进程

如果需要跟踪一个已经存在session，可以用 oradebug连接到session上，并发起10046 trace。

首先，用某种方法找到需要被跟踪的session.

例如，在SQL*Plus里，找出目标session的OS的进程ID(spid):

select p. PID,p. SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID 是操作系统的进程标识符（os pid）
PID 是Oracle的进程标识符(ora pid)
一旦找到OS PID，就可以用以下命令初始化跟踪：

假设需要被跟踪的OSPID是9834。

以sysdba的身份登录到SQL*Plus并执行下面的命令：

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
记得把例子中的'9834' 替换成真实的os pid。

注：也可以通过oradebug使用 'setorapid'命令连接到一个session。

下面的例中，使用 PID （Oracle进程标识符）(而不是SPID), oradebug命令将被改为：

 
  connect / as sysdba
 oradebug setorapid 9834
 oradebug unlimit
 oradebug event 10046 trace name context forever,level 12 
 

记得把例子中的9834替换成真实的ora pid。

跟踪过程完成以后，关闭oradebug跟踪：

   oradebug event 10046 trace name context off 
 

跟踪其他用户：

在很多时候我们需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION
来完成

SET_SQL_TRACE_IN_SESSION过程序要提供三个参数:

SQL> desc dbms_system
...
PROCEDURE SET_SQL_TRACE_IN_SESSION
 Argument Name                     Type                    In/Out Default?
 ------------------------------           -----------------------   ------ --------
 SID                               NUMBER                  IN
 SERIAL#                          NUMBER                  IN
 SQL_TRACE                        BOOLEAN                 IN
...

通过v$session我们可以获得sid、serial#等信息:

获得进程信息，选择需要跟踪的进程:

SQL> select sid,serial#,username from v$session
  2  where username is not null;

       SID    SERIAL#  USERNAME
---------- ---------- ------------------------------
         8       2041  SYS
         9        437  EYGLE

设置跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,true)

PL/SQL procedure successfully completed.

....
可以等候片刻，跟踪session执行任务,捕获sql操作...
....

停止跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,false)

PL/SQL procedure successfully completed.

对其他用户session设置
通过DBMS_SYSTEM.SET_EV系统包来实现:

SQL> desc dbms_system
...
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

...

其中的参数SI、SE来自v$session视图:
查询获得需要跟踪的session信息:
SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 EYGLE

执行跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle');

PL/SQL procedure successfully completed.

结束跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle');

PL/SQL procedure successfully completed.

SQL> select

  2    d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  3  from
  4    ( select p.spid
  5      from sys.v$mystat m,sys.v$session s,sys.v$process p
  6      where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  7    ( select t.instance from sys.v$thread  t,sys.v$parameter  v
  8      where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
  9    ( select value from sys.v$parameter where name = 'user_dump_dest') d

实例层的跟踪

注意: 在实例层设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响。

这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪。

系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候。
在这种情况下，可以打开系统层跟踪一小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息。

用以下命令打开系统层的跟踪：

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

用以下命令关闭在所有session中的10046跟踪：

   alter system set events '10046 trace name context off'; 
 

初始化参数设置

设置以下参数并重新启动实例后，实例上所有的session都会打开跟踪。

   event="10046 trace name context forever,level 12" 
 

移除这个参数并且重启实例，或者使用下面的alter system命令可以关闭跟踪。

   alter system set events '10046 trace name context off'; 
 

通过logon trigger设置跟踪

有的时候当需要跟踪某个特定用户的操作时，可以使用logon trigger来打开跟踪
下面是一个例子:

   CREATE OR REPLACE TRIGGER SYS.set_trace 
  
 AFTER LOGON ON DATABASE 
  
 WHEN (USER like '&USERNAME') 
  
 DECLARE 
  
 lcommand varchar(200); 
  
 BEGIN 
  
 EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger'''; 
  
 EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';  
  
 EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED'; 
  
 EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'''; 
  
 END set_trace; 
  
 /

为了能打开跟踪session, 执行trigger的用户需要被显式地授予'alter session' 权限. 例如，

 
  grant alter session to <USERNAME> ; 
 

用SQLT收集trace

SQLTXPLAIN的Xecute方法生成的诊断文件中会包含10046 trace。正如XECUTE这个名字所示，SQLT会执行被分析的SQL语句，然后生成一个诊断文件集（包括10046 trace）。详见：

Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)

用这种方法生成的trace文件会被包含在SQLT输出包中，格式为：

   sqlt_s 
  12345_10046_execute.trc 
 

获取跟踪文件
以上生成的跟踪文件位于user_dump_dest目录中，位置及文件名可以通过以下SQL查询获得:

SQL> select

      d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
   from
      ( select p.spid
       from sys.v$mystat m,sys.v$session s,sys.v$process p
       where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
     ( select t.instance from sys.v$thread  t,sys.v$parameter  v
       where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
     ( select value from sys.v$parameter where name = 'user_dump_dest') d

用DBMS_MONITOR进行跟踪

DBMS_MONITOR包提供几个打开跟踪的方法。详细请见：

Note:293661.1 Tracing Enhancements In 10g Using DBMS_MONITOR

Oracle Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)
Part Number B14258-02
Chapter 60 DBMS_MONITOR
其它特定场景下打开跟踪的方法

Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples

Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)

Note:1102801.1 How to Get 10046 Trace for Parallel Query
Note:242374.1 Tracing PX session with a 10046 event or sql_trace

Note:258418.1 Getting 10046 Trace for Export and Import

如果您运行的是PL/SQL存储过程或包，那么可以通过使用PL/SQL profiler判断PL/SQL运行期间的时间消耗的具体信息。

下面的文档介绍了PL/SQL profiler的使用方法：

Note:243755.1 Implementing and Using the PL/SQL Profiler
Trace文件解析

Note:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
Note:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Note:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

NOTE:41634.1 - TKProf Basic Overview
NOTE:32951.1 - TKProf Interpretation (9i and below)
NOTE:760786.1 - TKProf Interpretation (9i and above)
NOTE:214106.1 - Using TKProf to compare actual and predicted row counts

Note:199083.1 * Master Note: SQL Query Performance Overview
Note:398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions