SQL_TRACE使用案例

发布时间：2020-07-30 11:31:29 作者：lanniaode
来源：网络阅读：640

SQL_TRACE主要是为了追踪SQL的执行过程，分析SQL的性能，资源消耗情况。

它可以查看SQL是如何操作处理数据、在执行过程中产生了哪些等待事件，查看sql在执行过程中的资源消耗、可以查看sql的递归语句，是sql的实际执行计划。

（1）开启跟踪

跟踪自己的会话很简单

SQL>alter session set tracefile_identifier='mysession'; ---给当前会话一个标示，便于查找跟踪生成的文件

Alter session set sql_trace= true;
或者
exec dbms_session.set_sql_trace(true);

如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false);
整个数据库系统跟踪

alter system set sql_trace=true; ---在11g中已经不推荐使用

（2）执行sql语句

SQl >select * from SCOTT.DEPT; --当前会话执行的语句

或者如果跟踪别人的会话则等待别的会话执行sql语句

（3）关闭跟踪

关闭跟踪自己的会话
Alter session set sql_trace= false;
或者
exec dbms_session.set_sql_trace(false);

如果跟踪别人的会话，需要调用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
关闭整个数据库系统跟踪

alter system set sql_trace= false; ---在11g中已经不推荐使用
（4）查找跟踪文件

在$ORACLE_BASE/admin/sid/udump下找到跟踪文件，比如前面设置标示符为'mysession'的trace文件

或者用脚本查看跟踪文件位置（当前会话）：

select d.value||'\'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc'trace_file_name from

(select p.spid from v$mystat m, v$session s,v$process p wherem.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,

(select t.instance from v$thread t,v$parameter v where v.name ='thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,

(select value from v$parameter where name = 'user_dump_dest') d;

（5）格式化跟踪文件

tkprof 跟踪文件名输出文件名

（6）案例1（当前会话跟踪）

【示例】：

SQL> alter session set tracefile_identifier='mysession';

会话已更改。

SQL> Alter session set sql_trace= true;

会话已更改。

SQL> select * from SCOTT.DEPT;

DEPTNO DNAME LOC

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

50 ACCOUNTINGcs NEW YORK

60 RESEARCHcs DALLAS

70 SALEScs CHICAGO

80 OPERATIONScs BOSTON

51 ACCOUNTINGcscs NEW YORK

61 RESEARCHcscs DALLAS

71 SALEScscs CHICAGO

81 OPERATIONScscs BOSTON

11 ACCOUNTINGcs NEW YORK

21 RESEARCHcs DALLAS

31 SALEScs CHICAGO

DEPTNO DNAME LOC

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

41 OPERATIONScs BOSTON

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

已选择16行。

SQL> Alter session set sql_trace= false;

会话已更改。

SQL> select d.value||'\'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.tr

c' trace_file_name from(select p.spid from v$mystat m, v$session s,v$process p w

here m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p, (select t.inst

ance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.

thread# = to_number(v.value))) i, (select value from v$parameter where name = 'u

ser_dump_dest') d;

TRACE_FILE_NAME

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

e:\app\yan\diag\rdbms\orcl\orcl\trace\orcl_ora_1584.trc

进入命令行：

C:\Users\yan>e:

E:\>cd E:\app\yan\diag\rdbms\orcl\orcl\trace

由于刚才用了标示符，所以跟踪文件多了mysessio字符串：

E:\app\yan\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_1584_mysession.trc orcl_or

a_1584_mysession.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期三 3月 26 15:47:50 2014

案例2（跟踪别人的会话）

【示例】：

当前会话执行：

SQL> select distinct sid from v$mystat;

SID

----------

134

另一个会话执行：

SQL> select distinct sid from v$mystat;

SID

----------

141

当前会话执行：

SQL> select sid,serial# from v$session where sid in (141,134);

SID SERIAL#

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

134 7471

141 14317

SQL> execute sys.dbms_system.set_sql_trace_in_session(141,14317,true);

PL/SQL 过程已成功完成。

另一个会话执行：

SQL> select count(*) from SCOTT.t1;

COUNT(*)

----------

72564

当前会话执行：

SQL> execute sys.dbms_system.set_sql_trace_in_session(141,14317,false);

PL/SQL 过程已成功完成。

进入命令行：

C:\Users\yan>e:

E:\>cd E:\app\yan\diag\rdbms\orcl\orcl\trace

由于刚才用了标示符，所以跟踪文件多了mysessio字符串：

E:\app\yan\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_12224.trc orcl_or

a_12224.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期三 3月 26 15:47:50 2014

SQL_TRACE使用案例

相关阅读