一 跟踪当前会话
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 2024Copyright (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: 0BEGIN 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 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------Disk file operations I/O 1 0.00 0.00SQL*Net message to client 1 0.00 0.00SQL*Net message from client 1 4.69 4.69
********************************************************************************SQL ID: 520mkxqpf15q8 Plan Hash: 308129442select 1
fromdualcall 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 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1Rows (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.00SQL*Net message to client 2 0.00 0.00SQL*Net message from client 2 5.13 5.13
********************************************************************************SQL ID: fcx2hxwbg8rzz Plan Hash: 0BEGIN 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 1Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall 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 3Misses in library cache during parse: 0Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------Disk file operations I/O 2 0.00 0.00SQL*Net message to client 3 0.00 0.00SQL*Net message from client 3 5.13 9.83OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall 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 0Misses in library cache during parse: 03 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: default1 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.二 跟踪其他会话
2.1 查看要追踪的SID,SERIAL#
#在要被追踪的会话里执行
示例：
SQL> select distinct userenv('sid') from v$mystat;
USERENV('SID')
--------------
372
SQL> select distinct sid,serial# from v$session where sid=372;
SID SERIAL#
---------- ----------
372 58441
2.2 开启跟踪
#跟踪源端sid为372,serial#为58441的会话
BEGIN
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => 372
, serial_num => 58441
,waits => true
, binds => false);
END;
/
2.3 执行sql
#示例
SQL> update scott.t1 set name='bbb' where id=1;
1 row updated.
SQL> select * from scott.t1 where id=1;
ID NAME
---------- ------------------------------
1 bbb
2.4 取消跟踪
BEGIN
DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id => 372
, serial_num => 58441);
END;
/
2.5 查看追踪
2.5.1 查看追踪文件路径
SELECT S.SID,S.SERIAL#,P.TRACEFILE FROM V$SESSION S ,V$PROCESS P WHERE P.ADDR=S.PADDR AND S.SID=372;
2.5.2 格式化追踪文件
#将该trace文件格式化下，输出到outputfile.txt文件下：
tkprof /u01/app/oracle/diag/rdbms/emdata/emdata/trace/emdata_ora_26173.trc outputfile.txt
2.5.3 查看追踪文件
less outputfile.txt
可以看到执行的两条sql都能看到了：
