SQL_TRACE使用案例

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

Copyright (c) 1982, 2009, Oracle and/or its  affiliates.  All rights reserved.

当前会话执行：

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

Copyright (c) 1982, 2009, Oracle and/or its  affiliates.  All rights reserved.