How to trace sessions in Oracle Database

当Oracle数据库出现性能问题时，我们通常可以利用一些性能诊断工具来跟踪SQL的执行情况，进而根据输出的跟踪文件来了解和分析数据库内部的一些操作过程和统计信息，找出性能瓶颈。

针对不同的需求场景，有不同的诊断工具，或者同一种工具有不同的用法。这里就汇总下一些常用的性能诊断工具和使用场景。

跟踪文件

Oracle跟踪文件分为三种类型

  • 一种是后台报警日志文件，记录数据库在启动、关闭和运行期间后台进程的活动情况,如表空间创建、回滚段创建、某些alter命令、日志切换、错误消息等。在数据库出现故障时，应首先查看该文件，但文件中的信息与任何错误状态没有必然的联系。后台报警日志文件保存BACKGROUND_DUMP_DEST参数指定的目录中。
  • 另一种类型是DBWR、LGWR、SMON等后台进程创建的后台跟踪文件。后台跟踪文件根据后台进程运行情况产生，后台跟踪文件也保存在BACKGROUND_DUMP_DEST参数指定的目录中。
  • 还有一种类型是由连接到Oracle的用户进程(Server Processes)生成的用户跟踪文件。这些文件仅在用户会话期间遇到错误时产生。此外，用户可以通过执行oracle跟踪事件(如10046事件）来生成该类文件，用户跟踪文件保存在USER_DUMP_DEST参数指定的目录中。

命名规则

用户进程跟踪文件的命名规则

[oracle_sid]_ora_[server_process_id]_[trace_id].trc

  • oracle_sid，可以简单理解为数据库实例id，erpprod为prod；
  • server_process_id，oracle内部标示进程的id，可以通过查询v$session的spid来确定；
  • trace_id可由 tracefile_identifier参数指定，通过查询v$process的traceid来确定，默认为空。

存放路径

  • 由参数 user_dump_dest指定。
  • 在SQL*Plus中可以通过show user_dump_dest；查询； 或执行 SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';来确定。

初始化参数

为了在跟踪文件中更好的收集数据库性能诊断信息，必须先调整以下两个参数：

  • TIMED_STATISTICS 用于收集与时间有关的信息，主要从操作系统请求时间而引起的时间开销，若需要显示这些信息，需设置为TRUE。
  • MAX_DUMP_FILE_SIZE 指定跟踪文件的最大大小；如果跟踪文件很大或者不确定，需设置为足够的大小，或者UNLIMITED。

跟踪当前会话

1.使用 SQL_TRACE

在SQL*Plus中使用SQL_TRACE，跟踪当前会话

-- Start tracing the current session
ALTER SESSION SET SQL_TRACE = TRUE ;

-- execute your SQL to be traced --

-- Stop tracing the current session
ALTER SESSION SET SQL_TRACE = FALSE;

对实例上所有的SQL做跟踪

-- Start tracing the whole instance
ALTER DATABASE SET SQL_TRACE = TRUE ;

-- execute your SQL to be traced --

-- Stop tracing the whole instance
ALTER SESSION SET SQL_TRACE = FALSE;

注：

  • 在session级别设置，只对当前session进行跟踪，在实例级别，会对实例上所有的SQL做跟踪，这种方式跟踪的SQL太多，代价是非常大的，所有很少用。
  • sql_trace也可以在初始化文件里面设置。

2.10046事件

10046 事件主要用来跟踪SQL语句，它并不是ORACLE官方提供给用户的命令，在官方文档上也找不到事件的说明信息。但是用的却比较多，因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。

-- Start tracing the current session
alter session set events '10046 trace name context forever, level 12';

-- execute your SQL to be traced --

--Stop tracing the current session
alter session set events '10046 trace name context off';

10046事件级别

根据要收集信息的详细程度，10046事件分为了很多不同的级别，使用整数定义，常见的如下：

  • Level 1： 等同于SQL_TRACE 的功能
  • Level 4： 在Level 1的基础上增加收集绑定变量的信息
  • Level 8： 在Level 1 的基础上增加等待事件的信息
  • Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

一般来说，使用Level 12就可以收集到比较详细的诊断信息，包括绑定变量，等待事件等。

使用示例

初始参数设置

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;

启动10046跟踪事件

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

-- execute your SQL to be traced --

在当前会话中，停用10046跟踪事件，有以下两种方法：

1)退出当前会话

select * from dual;
--exit;

2)手动使跟踪失效

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

3.DBMS_SUPPORT

跟踪当前会话除了以上两种方式外，Oracle还提供了实用工具DBMS_SUPPORT包，如果没有需要手动到$ORACLE_HOME/rdbms/admin下安装。

主要用法如下：

--Start tracing the current session
exec sys.dbms_support.start_trace ;

-- execute your SQL to be traced --

--Stop tracing  the current session
exec sys.dbms_support.stop_trace ;

跟踪非当前会话

在性能诊断过程中，跟踪当前会话更多的是重现问题，以确认是否存在异常情况，但限制太多，而且很难模拟生产系统当时的运行情况。因此，Oracle也提供了丰富的工具盒方法来监控正在运行的会话，以更好的适应实际应用的需求。

以下汇总一些常用方的工具，在不同的数据库版本中，支持程度可能不一样。

1.使用dbms_system.set_bool_param_in_session

--Start tracing other session (28,226)
exec sys.dbms_system.set_bool_param_in_session(28, 226, 'sql_trace', TRUE);

-- execute SQL in that session to be traced --	

--Stop tracing other session (28,226)
exec sys.dbms_system.set_bool_param_in_session(28, 226, 'sql_trace', FALSE);

2.使用dbms_system.set_ev

--Start tracing other session (28,226)
exec dbms_system.set_ev(28, 226, 10046, 12, '');

-- execute SQL in that session to be traced --	

--Stop tracing other session (28,226)
exec dbms_system.set_ev(28, 226, 10046, 0, '');

3.使用dbms_system.set_sql_trace_in_session

-Start tracing other session (28,226)
exec dbms_system.set_sql_trace_in_session(1166,30993,TRUE);

-- execute SQL in that session to be traced --	

-Start tracing other session (28,226)
exec dbms_system.set_sql_trace_in_session(1166,30993,FALSE);

4.使用dbms_monitor

-Start tracing other session (28,226)
exec dbms_monitor.session_trace_enable(session_id=>28,serial_num=>226, waits=>true, binds=>true);

-- execute SQL in that session to be traced --	

-Start tracing other session (28,226)
exec dbms_monitor.session_trace_disable(session_id=>28,serial_num=>226);

5.使用oradebug

--Start tracing process with pid = os_process_pid
oradebug setospid os_process_pid
oradebug event 10046 trace name context forever, level 12;

-- execute SQL in that process to be traced --	

--Stop tracing process with pid = os_process_pid
oradebug event 10046 trace name context off ;

6.使用数据库触发器

除了以上方式，也可以结合数据库级别触发器，如logon/logoff，针对某一用户进行跟踪。

--Start tracing when logon database
create or replace trigger user_logon_t
after logon on database
begin
	if USER = 'username' then
	execute immediate
	'alter session set events ''10046 trace name context forever, level 8''';
	end if;
end;
/

-- execute SQL with that user to be traced */

--Stop tracing before logoff database
create or replace trigger user_logoff_t
before logoff on database
begin
	if USER = 'username' then
	execute immediate
	'alter session set events ''10046 trace name context off''';
	end if;
end;
/

TKPROF

以上跟踪会话过程中产生的跟踪文件阅读性不是太友好，很难阅读。于是Oracle提供了专门的格式化工具tkprof。

tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具，使用tkprof工具使用排序功能格式化输出，可以使阅读更加友好。

用法帮助

$ tkprof 
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    ... ...

使用示例

$ cd $ORACLE_USER_DUMP_DEST
$ tkprof explain=apps/password
trace = prod_ora_3199034.trc
output = prod_ora_3199034.out                           
 
TKPROF: Release 9.2.0.6.0 - Production on Mon Feb 25 15:58:26 2013
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
$ ll prod_ora_3199034*        
-rw-r--r--   1 prodora  dba          150112 Feb 25 15:58 prod_ora_3199034.out
-rw-r--r--   1 prodora  dba          745495 Feb 25 15:20 prod_ora_3199034.trc

关于tkprof更详细的说明参见tkprof使用

Reference

blog comments powered by Disqus

Published

21 March 2013

Categories

Tags

Github