跟踪oracle的执行
1.在当前会话中启用跟踪
(a)SQL> alter session set sql_trace=true;
启用跟踪后,跟踪文件保存在user_dump_dest(用户进程)或background_dump_dest(后台进程)目录下
(b)使用10046事件
全局设定:
参数文件中指定: event="10046 trace name context forever,level 12"
或者
SQL> alter system set events '10046 trace name context forever, level 12';
SQL> alter system set events '10046 trace name context off';
注意:系统级别启用sql_trace,会产生大量trace文件,很容易耗尽磁盘空间,因此一般设置会话级别,并且及时关闭。
当前session设定:
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> 执行sql
SQL> alter session set events '10046 trace name context off';
(c)dbms_session包:只能跟踪当前会话,不能指定会话。
dbms_session.session_trace_enable
Syntax
DBMS_SESSION.SESSION_TRACE_ENABLE(
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL); --plan_stat 11g才有的
waits Specifies if wait information is to be traced
binds Specifies if bind information is to be traced
plan_stat Frequency at which we dump row source statistics. Value should be 'NEVER',
'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.
启用跟踪
SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true,plan_stat=>'ALL_EXECUTIONS');
关闭跟踪
SQL> exec DBMS_SESSION.SESSION_TRACE_DISABLE;
dbms_session.SET_SQL_TRACE也可以启用跟踪
SET_SQL_TRACE Procedure
This procedure turns tracing on or off. It is equivalent to the following SQL statement:
ALTER SESSION SET SQL_TRACE ...
Syntax
DBMS_SESSION.SET_SQL_TRACE ( sql_trace boolean);
sql_trace TRUE turns tracing on, FALSE turns tracing off
跟踪其他会话:
dbms_monitor包:10g提供,功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪。oracle官方支持。
SESSION_TRACE_ENABLE Procedure
This procedure enables a SQL trace for the given Session ID on the local instance
Syntax
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
Parameters
Parameter Description
session_id Client Identifier for which SQL trace is enabled. If omitted (or NULL), the user's own session is assumed.
serial_num Serial number for this session. If omitted (or NULL), only the session ID is used to determine a session.
waits If TRUE, wait information is present in the trace
binds If TRUE, bind information is present in the trace
plan_stat Frequency at which we dump row source statistics. Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'.
Examples
To enable tracing for a client with a given client session ID:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);
Either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5);
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5, NULL);
traces the session with session ID of 5, while either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE();
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL);
traces the current user session. Also,
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL, TRUE, TRUE);
traces the current user session including waits and binds. The same can be also expressed
using keyword syntax:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>TRUE);
识别跟踪文件:
用以下查询查询当前会话的跟踪文件
select s.sql_trace,s.sql_trace_waits,s.sql_trace_binds,traceid,tracefile
from v$session s join v$process p on (p.addr=s.paddr)
where audsid=userenv('sessionid')
如果要查询其他会话知道会话的sid和serial#就可以用上面的查询,写一个sql
select s.sql_trace,s.sql_trace_waits,s.sql_trace_binds,traceid,tracefile
from v$session s join v$process p on (p.addr=s.paddr)
where s.sid=&sid and s.serial#=&serial;
格式化跟踪文件
tkprof格式化跟踪文件用法如下:
C:\>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
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
也可以用跟踪分析器TRCANLZR这个工具展现出来的信息更强大比tkprof内容丰富(参见oracle的支持文档224270.1)