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 概要
3.Summary of DBMS_MONITOR Subprograms
Subprogram | Description |
---|---|
Disables statistic gathering previously enabled for a given Client Identifier | |
Enables statistic gathering for a given Client Identifier | |
Disables the trace previously enabled for a given Client Identifier globally for the database | |
Enables the trace for a given Client Identifier globally for the database | |
Disables SQL trace for the whole database or a specific instance | |
Enables SQL trace for the whole database or a specific instance | |
Disables statistic gathering enabled for a given combination of Service Name,MODULEandACTION | |
Enables statistic gathering for a given combination of Service Name, MODULE and ACTION | |
Disables the trace for ALL enabled instances for a or a given combination of Service Name,MODULEandACTIONname globally | |
Enables SQL tracing for a given combination of Service Name, MODULEandACTIONglobally unless aninstance_nameis specified | |
Disables the previously enabled trace for a given database session identifier (SID) on the local instance | |
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