跟踪oracle的执行

不念不忘少年蓝@ 2022-04-01 09:08 234阅读 0赞

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