跟踪oracle的执行

本文介绍如何在Oracle数据库中启用和禁用SQL跟踪,包括使用SQL命令、dbms_session包和dbms_monitor包的方法。此外,还介绍了如何通过tkprof工具解析跟踪文件。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

跟踪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)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值