文档课题：跟踪session执行的sql语句之dbms_system.set_sql_trace_in_session工具.
数据库：oracle 11.2.0.4
1、理论概念
DBMS_SYSTEM是ORACLE提供的一个非常强大的跟踪工具.
2、实验操作
2.1、准备工作
[oracle@leo-oel150 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 6 20:58:27 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl150> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl150/users01.dbf
/u01/app/oracle/oradata/orcl150/undotbs01.dbf
/u01/app/oracle/oradata/orcl150/sysaux01.dbf
/u01/app/oracle/oradata/orcl150/system01.dbf
/tmp/FY_REC_DATA.DAT
/tmp/FY_RST_DATA.DAT

6 rows selected.

SYS@orcl150> create tablespace tbs_tmis datafile '/u01/app/oracle/oradata/orcl150/tmis01.dbf' size 10m autoextend on;

Tablespace created.

SYS@orcl150> create user tmis identified by tmis default tablespace tbs_tmis;

User created.

SYS@orcl150> grant dba to tmis;

Grant succeeded.

SYS@orcl150> conn tmis/tmis;
Connected.
TMIS@orcl150> select * from tab;

no rows selected

TMIS@orcl150> create table test1(id number primary key,xid varchar2(14),xtype varchar2(1),xstatus number not null);

Table created.

TMIS@orcl150> create index idx_test1_xid on test1(xid);

Index created.

TMIS@orcl150> insert into test1 values (1,'1','Y',0);

1 row created.

TMIS@orcl150> insert into test1 values(2,'2','Y',1);

1 row created.

TMIS@orcl150> insert into test1 values(3,'3','N',0);

1 row created.

TMIS@orcl150> insert into test1 values (4,'4','N',1);

1 row created.

TMIS@orcl150> insert into test1 values (5,'5','N',0);

1 row created.

TMIS@orcl150> commit;

Commit complete.
TMIS@orcl150> select * from test1;

        ID XID            X    XSTATUS
---------- -------------- - ----------
         1 1              Y          0
         2 2              Y          1
         3 3              N          0
         4 4              N          1
         5 5              N          0
2.2、开始测试
Session 1：
SQL> select sid from v$mystat where rownum<2;

       SID
----------
         5

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

       SID    SERIAL#
---------- ----------
         5         19
Session 2：
--查到具体session信息后，开始进行跟踪。
SYS@orcl150> exec dbms_system.set_sql_trace_in_session(5,19,true);

PL/SQL procedure successfully completed.

Session 1：
在session 1中进行一些操作.
SQL> select xstatus,id,xid,xtype
  2    from test1
  3   where xid=2
  4     and xstatus>0;

   XSTATUS         ID XID            XTYPE
---------- ---------- -------------- -----
         1          2 2              Y
Session 2
关闭跟踪
SYS@orcl150> exec dbms_system.set_sql_trace_in_session(5,19,false);

PL/SQL procedure successfully completed.
--查session 1对应的进程.
SYS@orcl150> select spid from v$process where addr=(select paddr from v$session where sid=5 and serial#=19); 

SPID
------------------------
93098
--查得具体trace文件，使用tkprof进行格式化.
[oracle@leo-oel150 trace]$ ll | grep orcl150_ora_93098    
-rw-r----- 1 oracle oinstall  29010 Feb  6 23:28 orcl150_ora_93098.trc
-rw-r----- 1 oracle oinstall    237 Feb  6 23:28 orcl150_ora_93098.trm
[oracle@leo-oel150 trace]$ tkprof orcl150_ora_93098.trc /home/oracle/orcl150_ora_93098.txt
[oracle@leo-oel150 ~]$ cat orcl150_ora_93098.txt
……
select xstatus,id,xid,xtype
  from test1
 where xid=2
   and xstatus>0

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        1      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          0           1

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

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS FULL TEST1 (cr=6 pr=0 pw=0 time=153 us cost=3 size=9 card=1)
……

参考网址：https://blog.csdn.net/congjiaax703023/article/details/100486188