oracle会话追踪

一跟踪当前会话

1.1 查看当前会话的SID,SERIAL#

#在当前会话里执行

示例：

SQL> select distinct userenv('sid') from v$mystat;

USERENV('SID')

--------------

1945

SQL> select distinct sid,serial# from v$session where sid=1945;

SID SERIAL#

---------- ----------

1945 42786

1.2 开启跟踪

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE;

1.3 执行sql

select 1 from dual;

1.4 关闭跟踪

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;

1.5 查看跟踪

1.5.1 查看追踪文件路径

SELECT S.SID,S.SERIAL#,P.TRACEFILE FROM V$SESSION S ,V$PROCESS P WHERE P.ADDR=S.PADDR AND S.SID=1945;

SID SERIAL# TRACEFILE

---------- ---------- ----------------------------------------------------------------------

1945 42786 /u01/app/oracle/diag/rdbms/emdata/emdata/trace/emdata_ora_180907.trc

1.5.2 格式化追踪文件

#将该trace文件格式化下，输出到outputfile.txt文件下：

tkprof /u01/app/oracle/diag/rdbms/emdata/emdata/trace/emdata_ora_180907.trc outputfile.txt

1.5.3 查看追踪文件

less outputfile.txt

可以看到该会话执行过的sql，sql_id，PLAN HASH，用户名，等待事件等信息。

输出示例：


TKPROF: Release 19.0.0.0.0 - Development on Thu Nov 21 14:37:42 2024

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: /u01/app/oracle/diag/rdbms/emdata/emdata/trace/emdata_ora_180907.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 9mu7b960ubtuf Plan Hash: 0

BEGIN DBMS_MONITOR.SESSION_TRACE_ENABLE; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        4.69          4.69
********************************************************************************

SQL ID: 520mkxqpf15q8 Plan Hash: 308129442

select 1 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=9 us starts=1 cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        5.13          5.13
********************************************************************************

SQL ID: fcx2hxwbg8rzz Plan Hash: 0

BEGIN DBMS_MONITOR.SESSION_TRACE_DISABLE; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           2
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          0          0           3

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        5.13          9.83


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    3  user  SQL statements in session.
    0  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/emdata/emdata/trace/emdata_ora_180907.trc
Trace file compatibility: 12.2.0.0
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
      60  lines in trace file.
       9  elapsed seconds in trace file.