In Oracle : Oracle Trace in the DB : enable/ disable, level

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Trace in the DB : enable/ disable, level

 

 Oracle Trace in the DB : enable/ disable, level

 

 

Enable tracing in the database help in database tuning (tested in Oracle database 10g). Here is a table which show the tracing levels and how the tracing could be enabled: 

     Level

How is enabled

1.1   session

(current session)

ALTER SESSION SET sql_trace = true;

 

ALTER SESSION SET TRACEFILE_IDENTIFIER=<TRACEID>;  

 

(See comment a).

1.2   session

(another session)

 

select sid, serial# from v$session where ...

SID        SERIAL#

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

11           13442

 

execute dbms_system.set_sql_trace_in_session (<SID>, <SERIAL#>, true);

 

2.0  database

a) modify init.ora by adding sql_trace = true; 

 

b) ALTER SYSTEM SET SQL_TRACE = TRUE SCOPE=MEMORY; 

(if spfile is used);

 


Comments:

a)      The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:

        INSTANCE is the name of the Oracle instance;

        PID is the operating system process ID (V$PROCESS.OSPID);

        TRACEID is a character string of your choosing.

 

b)      Don’t forget to Enable Timed Statistics before enabling tracing (is mandatory):

          ALTER SYSTEM SET TIMED_STATISTICS = TRUE

c)      The output trace file is written to user_dump_dest directory. 

        ( SHOW PARAMETERS user_dump_dest : to see the value of user_dump_dest initialization parameter.)

d)   The trace output is quite unreadable. Oracle provides a utility (TKProf) to interpret the output (trace) file.

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Trace in the DB : enable/ disable, level

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.