1、SQL_TRACE

通过设置 SQL_TRACE 可以启用或禁用 SQL 跟踪工具，设置 SQL_TRACE 为 true 可以收集信息用于性能优化或问题诊断；

特别注意：

全局启用 SQL 跟踪可能会对性能产生严重影响。 可以使用 ALTER SESSION 跟踪特定会话。

Oracle 已 经 不 在 推 荐 使 用 SQL_TRACE 参 数 。 建 议 改 用DBMS_MONITOR 和DBMS_SESSION 软件包， 保留 SQL_TRACE 仅用于向后兼容。

在当前 session 级别启用和停止 SQL_TRACE 方式如下：

alter session set SQL_TRACE=true;

执行相关语句

alter session set SQL_TRACE=false;

select value from v$diag_info where name = ‘Default Trace File’;

特定用户开启 SQL_TRACE 跟踪如下：

通过调用 DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION 包实现

查询 v$session 视图获取进程信息

select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

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

97 2217 SYS

然后对相应的会话启动 SQL_TRACE 跟踪

exec dbms_system.set_sql_trace_in_session(97,2217,true);

应用执行一段时间后，关闭 SQL_TRACE

exec dbms_system.set_sql_trace_in_session(97,2217,false)

查询系统对应 session trace 文件select value from v$diag_info where name = ‘Default Trace File’;

SQL_TRACE 还能对特定 SQL 启用跟踪：

alter system set events ‘sql_trace [sql:5qcyrymp65fak] bind=true,wait=true’;

alter system set events ‘sql_trace [sql:5qcyrymp65fak] off’;

对输出内容使用 tkprof 进行格式化

2、10046 trace

10046 事件 Oracle 提供的内部跟踪事件，是对 SQL_TRACE 的增强，通过10046 可以知道 Oracle 内部执行的 SQL_TRACE 的跟踪操作。

10046事件是SQL_TRACE的扩展，有效的跟踪级别有：

level 0：SQL_TRACE=FASLE

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。

level 4：1级+绑定变量

level 8：4级+等待事件

level 12：4级+8级

其中，level 1相当于打开了sql_trace

从 11G 开始新增了以下两个级别跟踪：

16 - 为每次 SQL 执行生成 stat 信息输出[plan_stat = all_executions]

32 - 比 level 1 少执行计划，不转储执执行统计信息[plan_stat = adaptive]

通过 session 级别跟踪：

alter session set events=‘10046 trace name context forever,level 12’;

alter session set events=‘10046 trace name context off’;

oradebug setmypid

oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

oradebug TRACEFILE_NAME

……

oradebug EVENT 10046 trace name context off

开启会话 SQL 跟踪

alter session set events ‘SQL_trace wait=true’;

alter session set events ‘SQL_trace off’;

对特定 session 启用跟踪：

通过调用 DBMS_SYSTEM. SET_EV 包实现

查询 v$session 视图获取进程信息

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

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

30 19678 SYS

执行跟踪

exec dbms_system.SET_EV(30,37754,10046,12,‘’);

结束跟踪

exec dbms_system.SET_EV(30,37754,10046,0,‘’);

查询系统对应 session trace 文件

select value from v$diag_info where name = ‘Default Trace File’;

10046跟踪结果，使用tkprof来格式化

$ tkprof /u01/oracle/diag/rdbms/neal/neal/trace/neal_ora_18691.trc /tmp/10046.trc

3、10053 trace

当一个 SQL 出现性能问题时，可以使用 SQL_TRACE 或者 10046 事件来跟踪 SQL，通过生成的 trace 来了解 SQL 的执行过程。 我们在查看一条 SQL 的执行计划的时候，只能看到 CBO 最终告诉我们的执行计划结果，但是不知道 CBO 是根据什么来做的。 如果遇到了执行计划异常，可以借助 Oracle 10053 事件进行跟踪。10053 事件是 oracle 提供的用于跟踪 sql 语句成本计算的内部事件，它能记载 CBO 模式下 oracle 优化器如何计算 sql 成本，生成相应的执行计划。

注意：10053 只对 CBO 有效，而且如果一个 sql 语句已经解析过，就不会产生新的 trace信息

10053事件存在两个级别：

Level 2：2级是1级的一个子集，它包含以下内容：

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Join Methods Considered (NL/MS/HA)

Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：

Parameters used by the optimizer

Index statistics

通过 session 级别跟踪：

ALTER SESSION SET EVENTS=‘10053 trace name context forever, level 1’;

ALTER SESSION SET EVENTS=‘10053 trace name context forever, level 2’;

执行相关 sql

ALTER SESSION SET EVENTS ‘10053 trace name context off’;

对特定 session 启用跟踪：

通过调用 DBMS_SYSTEM. SET_EV 包实现

查询 v$session 视图获取进程信息

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

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

125 25 SYS

执行跟踪

exec dbms_system.SET_EV(125,25,10053,1,‘’);

结束跟踪

exec dbms_system.SET_EV(125,25,10053,0,‘’);

查询系统对应 session trace 文件

select value from v$diag_info where name = ‘Default Trace File’;

4、HANGANALYZE

单机

sqlplus / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug hanganalyze 3

wait 120 seconds…

SQL> oradebug hanganalyze 3

SQL> oradebug tracefile_name

SQL> exit

rac:

sqlplus / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug -g all hanganalyze 3

wait 120 seconds…

SQL> oradebug -g all hanganalyze 3

SQL> oradebug tracefile_name

SQL> exit

当数据库 hang 住了,无法正常登录数据库时,我们可以使用 prelim 参数

sqlplus -prelim /nolog

connect / as sysdba

oradebug setmypid

oradebug unlimit

oradebug hanganalyze 3

oradebug dump systemstate 266

oradebug tracefile_name

需要注意：

1）如果sqlplus / as sysdba无法正常登录，通过sqlplus -prelim / as sysdba进行登录

2）在11.2 以上版本，不能使用-prelim 搜集HangAnalysis 信息，会报错。

结果分析：

State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[134]/1/135/1/0xf27dd190/21691/SINGLE_NODE/
[198]/1/199/895/0xf2894610/6802/LEAF/ ====>堵塞者
[388]/2/141/209/0xf27cac50/8132/SINGLE_NODE/
[445]/2/198/1741/0xf28976f0/10653/NLEAF/[198] =====》被堵塞者

5、sql_monitor跟踪

sql中加hit方式：

select /*+ MONITOR / from doc_detail where id=1;

event中对指定的sql_id加monitor

ALTER SYSTEM SET EVENTS ‘sql_monitor [sql: ava3w33nz6jt5] force=true’;

获取sql_monitor

CPU或IO执行超过5秒的sql会自动加入sql monitor或者使用hint /+ monitor/

查询是否已经记录到monitor监控表中

select sql_id,sql_text from v$sql_monitor where sql_text=‘select * from doc_detail where id=1’;

客户端输出html文件

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(

sql_id => ‘ava3w33nz6jt5’,

report_level => ‘ALL’,

TYPE => ‘active’)

FROM DUAL;

或者后台sqlplus / as sysdba输出：

set echo off

set linesize 10000 pages 6000

set long 20000000

set longchunksize 20000000

set trimout on trims on term off head off

spool 5tkanxszj5a12.html

select

DBMS_SQLTUNE.REPORT_SQL_MONITOR(

sql_id=>‘5tkanxszj5a12’, report_level=>‘ALL’,type=>‘ACTIVE’) as report

from dual;

spool off