Oracle执行语句跟踪(1)——使用sql trace实现语句追踪

朴灿烈づ我的快乐病毒、 2022-05-10 13:10 500阅读 0赞

1.安装dbms\_support包

SQL> @?/rdbms/admin/dbmssupp.sql
    
    Package created.
    
    
    Package body created.
    
    SQL> grant execute on dbms_support to dairy;

2.找到需要跟踪的会话

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
    
    Session altered.
    
    SQL> select sid,serial#,logon_time from v$session where audsid=userenv('sessionid');
    
           SID    SERIAL# LOGON_TIME
    ---------- ---------- -------------------
          2845     1649 2018-10-18 09:50:40

3.使用start\_trace\_in\_session进行跟踪

SQL> exec dbms_support.start_trace_in_session(2845,1649,TRUE,TRUE);
    
    PL/SQL procedure successfully completed.

4.开始跟踪后，在客户端进行对应的操作。

5.使用stop\_trace\_in\_session结束跟踪  
.

SQL> exec dbms_support.stop_trace_in_session(2845,1649);
    
    PL/SQL procedure successfully completed

6.使用以下的语句获得trace文件的位置。

SELECT    d.VALUE
           || '\'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
           || p.spid
           || '.trc'
               trace_file_name
      FROM (SELECT p.spid
              FROM v$mystat m, v$session s, v$process p
             WHERE m.statistic# = 1 AND s.SID = &SID AND p.addr = s.paddr) p,
           (SELECT t.INSTANCE
              FROM v$thread t, v$parameter v
             WHERE     v.NAME = 'thread'
                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
           (SELECT VALUE
              FROM v$parameter
             WHERE NAME = 'user_dump_dest') d
            
    SQL> SELECT    d.VALUE
           || '\'
           || LOWER (RTRIM (i.INSTANCE, CHR (0)))
           || '_ora_'
           || p.spid
           || '.trc'
               trace_file_name
      FROM (SELECT p.spid
              FROM v$mystat m, v$session s, v$process p
             WHERE m.statisti  2    3    4    5    6    7    8    9   10  c# = 1 AND s.SID = '2845' AND p.addr = s.paddr) p,
           (SELECT t.INSTANCE
              FROM v$thread t, v$parameter v
             WHERE     v.NAME = 'thread'
                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
           (SELECT VALUE
             11   12   13   14   15   16    FROM v$parameter
             WHERE NAME = 'user_dump_dest') d 17  ;
    
    TRACE_FILE_NAME
    --------------------------------------------------------------------------------
    /home/u01/app/oracle/diag/rdbms/foidndb/foidndb/trace\foidndb_ora_19143.trc

7.使用TKPROF将trace文件转换成为执行计划解析的文本。

[oracle@foidndb db_1]$ tkprof /home/u01/app/oracle/diag/rdbms/foidndb/foidndb/trace/foidndb_ora_19143.trc /u01/test.txt