DBMS_MONITOR包跟踪
当前数据库跟踪
采用登录触发器跟踪
总结
参考
正文
作为Oracle官方自带的一种基本性能诊断工具，SQL Trace可以用来评估当前正在运行的SQL语句的效率，同时为该语句生成统计信息等，并保存这些信息到指定路径下的跟踪文件(trace)当中。SQL Trace会将一条SQL语句或者PL/SQL包执行过程全部输出到跟踪文件(trace)当中，可以通过分析跟踪文件(trace)来分析SQL语句的执行效率并进行性能诊断与优化。
通常来说trace文件的内容不易于理解与阅读，Oracle官方还提供了工具tkprof对trace文件进行格式化处理，本文不讨论tkprof工具的使用。之前在工作中也有使用过SQL Trace进行SQL语句的跟踪诊断，但都没有进行完整的总结，本文就SQL Trace工具的使用进行梳理。
环境准备操作系统(OS)：CentOS Linux release 7.5.1804 (Core)
数据库版本(Oracle Database)：Oracle Database 11g R2(11.2.0.4.0)
同时配置了示例SCHEMA和解锁了用户SCOTT、HR。
跟踪方式开启SQL Trace有如下几种场景与方式：
当前会话开启跟踪本会话；
当前会话开启跟踪其他会话；
使用DBMS_MONITOR包来开启跟踪；
根据登录触发器来开启跟踪。
当前会话跟踪最简单的方法是在SQL*PLUS当中执行如下语句：
开启：
-- 配置trace文件的标识符便于寻找定位 SYS@dbabd> alter session set tracefile_identifier = dbabd; -- 开启当前会话跟踪 SYS@dbabd> alter session set sql_trace = true;通过运行以上语句就开启了当前会话的跟踪，trace文件位于 $ORACLE_BASE/diag/rdbms/dbabd/dbabd/trace目录下：
$ pwd /data/app/oracle/diag/rdbms/dbabd/dbabd/trace $ ls *DBABD* dbabd_ora_27978_DBABD.trc dbabd_ora_27978_DBABD.trm以上得到trace文件当中并不包含绑定变量值，也不包含等待事件信息，如果需要这些信息，可以使用DBMS_SESSION包来开启跟踪，前提是用户必须有ALTER SESSION权限，否则会报ORA-01031: insufficient privileges错误。关于DBMS_SESSION包的用法可以参考官方文档：DBMS_SESSION
如果事先没有配置trace文件的标识符，则可以通过以下语句进行定位：
定位：
-- 语句 select tracefile from v$session s join v$process p on p.addr = s.paddr where s.audsid = USERENV('SESSIONID'); -- 结果 SYS@dbabd> select tracefile 2 from v$session s 3 join v$process p 4 on p.addr = s.paddr 5 where s.audsid = USERENV('SESSIONID'); TRACEFILE ---------------------------------------------------------------------------------------------------- /data/app/oracle/diag/rdbms/dbabd/dbabd/trace/dbabd_ora_27978_DBABD.trc有时需要确认当前会话是否开启了跟踪，可以使用如下语句查询：
查询：
-- 语句 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.audsid = USERENV('SESSIONID'); -- 结果 SYS@dbabd> select s.sql_trace, 2 s.sql_trace_waits, 3 s.sql_trace_binds, 4 traceid, 5 tracefile 6 from v$session s 7 join v$process p 8 on p.addr = s.paddr 9 where s.audsid = USERENV('SESSIONID'); SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS TRACEID TRACEFILE ---------- --------------- --------------- ---------- ------------------------------------------------------------ ENABLED FALSE FALSE DBABD /data/app/oracle/diag/rdbms/dbabd/dbabd/trace/dbabd_ora_2797 8_DBABD.trc如果需要关闭当前会话跟踪，则执行如下语句：
关闭
SYS@dbabd> alter session set sql_trace = false; 其他会话跟踪如果是通过当前会话开启对其他会话的跟踪，一般都由DBA通过SYS用户操作，可以使用DBMS_SYSTEM包当中一个存储过程SET_SQL_TRACE_IN_SESSION，前提是需要获取到会话的SID和SERIAL#。
查看DBMS_SYSTEM包当中的结构
SYS@dbabd> desc dbms_system …………省略………… PROCEDURE SET_EV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SI BINARY_INTEGER IN SE BINARY_INTEGER IN EV BINARY_INTEGER IN LE BINARY_INTEGER IN NM VARCHAR2 IN …………省略………… PROCEDURE SET_SQL_TRACE_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN SQL_TRACE BOOLEAN IN