使用DBMS_MONITOR跟踪SQL

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,MODULEandACTION
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,MODULEandACTIONname globally
SERV_MOD_ACT_TRACE_ENABLE Procedure	Enables SQL tracing for a given combination of Service Name, MODULEandACTIONglobally unless aninstance_nameis 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的用法：

现在我们要对sid为140的会话打开跟踪

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

此时我们将使用dbms_monitor来打开会话sid为140的sql跟踪

SQL> exec dbms_monitor.session_trace_enable(140,2);
PL/SQL procedure successfully completed.

在当前会话中查看，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

关闭会话跟踪

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

接下来可以使用tkprof 或是trcsess来查看当前的trace文件

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