1.如何使用sql跟踪

在oracle 10g以前的版本中我们我们只能通过dbms_session包中的set_sql_trace过程、以及dbms_system包中的set_sql_trace_in_session过程在等级1激活sql跟踪，但在多数情形下我们要完全分解响应时间来分析瓶颈到底在那里，使用前面说的方法，远远不够，所以我们介绍在10g后一种新的激活sql跟踪的方法，那就是dbms_monitor包。

2.dbms_monitor 概要

The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering.

3.Summary of DBMS_MONITOR Subprograms

dbms_monitor 提供了多种方法来打开/关闭会话/实例/客户端/组件/库级别的sql跟踪，以下是他的用法：

Subprogram	Description
CLIENT_ID_STAT_DISABLE Procedure	Disables statistic gathering previously enabled for a given Client Identifier
CLIENT_ID_STAT_ENABLE Procedure	Enables statistic gathering for a given Client Identifier
CLIENT_ID_TRACE_DISABLE Procedure	Disables the trace previously enabled for a given Client Identifier globally for the database
CLIENT_ID_TRACE_ENABLE Procedure	Enables the trace for a given Client Identifier globally for the database
DATABASE_TRACE_DISABLE Procedure	Disables SQL trace for the whole database or a specific instance
DATABASE_TRACE_ENABLE Procedure	Enables SQL trace for the whole database or a specific instance
SERV_MOD_ACT_STAT_DISABLE Procedure	Disables statistic gathering enabled for a given combination of Service Name, MODULE and ACTION
SERV_MOD_ACT_STAT_ENABLE Procedure	Enables statistic gathering for a given combination of Service Name, MODULE and ACTION
SERV_MOD_ACT_TRACE_DISABLE Procedure	Disables the trace for ALL enabled instances for a or a given combination of Service Name, MODULE and ACTION name globally
SERV_MOD_ACT_TRACE_ENABLE Procedure	Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified
SESSION_TRACE_DISABLE Procedure	Disables the previously enabled trace for a given database session identifier (SID) on the local instance
SESSION_TRACE_ENABLE Procedure	Enables the trace for a given database session identifier (SID) on the local instance

4.演示

下面我们通过一个例子来演示下dbms_monitor的用法：

4.1 查询会话sid为140的sql跟踪状态

	SQL> select SID,SERIAL#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session where sid=140;

       SID SERIAL# SQL_TRAC SQL_T SQL_T

---------- ---------- -------- ----- -----

       140 2 DISABLED FALSE FALSE

4.2 使用dbms_monitor来打开会话sid为140的sql跟踪

		SQL> exec dbms_monitor.session_trace_enable(140,2);

PL/SQL procedure successfully completed.

4.3 在当前会话中查看，sql_trace已为启用，并且等待事件为true

				SQL> select SID,SERIAL#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS

  2 from v$session

  3 where sid in (select sid from v$mystat where rownum=1);

       SID SERIAL# 
SQL_TRAC SQL_T SQL_T

---------- ---------- -------- ----- 
-----

       140 2 ENABLED TRUE FALSE

4.4 关闭会话跟踪

				SQL> exec dbms_monitor.session_trace_disable(140,2);

PL/SQL procedure successfully completed.

SQL> select SID,SERIAL#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session where sid=140;

       SID SERIAL# SQL_TRAC SQL_T SQL_T

---------- ---------- -------- ----- -----

       140 2 DISABLED FALSE FALSE

5.使用tkprof 或是trcsess来查看当前的trace文件

			[oracle@db10g1 
udump]$ tkprof dbq_ora_6668.trc sql_trace140.txt
		

ITPub博客

DBMS_MONITOR跟踪SQL

1.如何使用sql跟踪

2.dbms_monitor 概要

3.Summary of DBMS_MONITOR Subprograms

4.演示

5.使用tkprof 或是trcsess来查看当前的trace文件

最新文章