Shulou(Shulou.com)06/01 Report--

SQL_TRACE is mainly used to trace SQL execution process, analyze SQL performance and resource consumption.

It can view how SQL operates to process data, what waiting events are generated during execution, view the resource consumption of SQL during execution, view the recursive statements of SQL, and is the actual execution plan of SQL.

(1) Start tracking

Tracking your own conversations is easy.

SQL>alter session set tracefile_identifier='myssion';---Gives an identifier to the current session to facilitate tracing generated files

Alter session set sql_trace= true;

or

exec dbms_session.set_sql_trace(true);

To track someone else's conversation, you need to invoke a package

exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false);

Whole database system tracking

alter system set sql_trace=true; ---deprecated in 11g

(2) Execution of SQL statements

SQl >select * from SCOTT.DEPT; --statements executed in the current session

Or if you're tracking someone else's session, wait for another session to execute sql statements

(3) Turn off tracking

Turn off tracking your own sessions

Alter session set sql_trace= false;

or

exec dbms_session.set_sql_trace(false);

To track someone else's conversation, you need to invoke a package

exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

Shut down the entire database system trace

alter system set sql_trace= false; ---deprecated in 11g

(4) Find tracking files

Find trace files under $ORACLE_BASE/admin/sid/udump, such as the trace file previously set to the identifier 'myssion'.

Or use scripts to view trace file locations (current session):

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) Format trace files

tkprof trace filename output filename

(6) Case 1 (current session tracking)

Example:

SQL> alter session set tracefile_identifier='mysession';

Session has changed.

SQL> Alter session set sql_trace= true;

Session has changed.

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 lines have been selected.

SQL> Alter session set sql_trace= false;

Session has changed.

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

Go to Command Line:

C:\Users\yan>e:

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

Because of the identifier just used, the trace file has more myssio strings:

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 Wednesday March 26 15:47:50 2014

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

Case 2 (Tracking someone else's conversation)

Example:

Current Session Execution:

SQL> select distinct sid from v$mystat;

SID

----------

134

Another session execution:

SQL> select distinct sid from v$mystat;

SID

----------

141

Current Session Execution:

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 procedure completed successfully.

Another session execution:

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

COUNT(*)

----------

72564

Current Session Execution:

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

PL/SQL procedure completed successfully.

Go to Command Line:

C:\Users\yan>e:

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

Because of the identifier just used, the trace file has more myssio strings:

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 Wednesday March 26 15:47:50 2014

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.