Oracle Trace

Understanding Oracle Trace Utility - Oracle Database 10g

Overview:

Some times the situation demands more information about an event. Example if we take a case of some error coming in our database because of some application. In this case its the job of developer to drill down into the application and get the exact queries those are failing. I have seen many situations where we get ORA-0600 error or ORA-07445 errors. In such situation, if one wants to debug the issue, then he need to know the exact query which is failing so that he should be able to reproduce the issue from command prompt and another thing is when he can reproduce the issue form command prompt, then he can get detailed level of information about the running query by setting the tracing levels.

This post is all about setting the tracing at session level (either own session or some other session) and getting detailed information about the query. I will show you, what are the different levels of tracing events that are present and what are the various levels of tracing.

I will discuss 2 types of tracing here

1. SQL tracing

2. Event based tracing

SQL Tracing:

Session Level:

One of the simplest method to trace SQL is to use “alter session set SQL_TRACE=true” command and oracle will generate the trace file for the SQL statment that you run in that session.

System Level:

Also you can set the SQL trace at system level using “alter system set SQL_TRACE=true” command. But be careful about this, because this is going to generate huge trace file and especially when the number of users are large, this will degrade database performance.

The pre-reqs for enabling the SQL trace either at session level or at system level is ‘TIMED_STATISTICS’ parameter should be set to “TRUE” and “STATISTICS_LEVEL” should be set minimum to “TYPICAL”. It can be set to “ALL” as well, which is the higest level of tracing information. While setting the statistics level to ALL, you have to be careful.

Important *** If possible do not set the statistics level to all at system level, set only at session level, else the database performance will be impacted as database has to produce a very detail level of tracing. Also do not set SQL trace at system level, set only at session level. Setting SQL trace at system level will generate huge trace file and database performance will degrade.

For other sessions:

You can also set SQL_TRACE for some other session as well using DBMS_SYSTEM package.

procedure DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION will enable you to do this.

login as sysdba and execute

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(,,true) - for enabling the tracing

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(,,false) - for disabling the tracing

Event Based Tracing

Session Level:

Event level tracing is a way to create detail information about some kind of event. Not what exactly we mean by event.

“An event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality.”

Some events are used by support analysts and developers to force certain conditions to occur for testing purposes.

For example the event 10046 will enable SQL statement timings. So setting this event tells oracle kernel to through timing information about SQLs in its trace files. Like wise we have several events.

The details of all the events and actions are provided in $ORACLE_HOME/rdbms/mesg/oraus.msg file.

For each of these events mentioned in oraus.msg file, we can set the level. These are the level of information that kernel should put in the trace files. Below are the various trace levels present in Oracle tracing utility.

* Level 0 = No statistics generated

* Level 1 = standard trace output including parsing, executes and fetches plus more.

* Level 2 = Same as level 1.

* Level 4 = Same as level 1 but includes bind information

* Level 8 = Same as level 1 but includes wait’s information

* Level 12 = Same as level 1 but includes binds and waits

Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.

There are many ways in which we can enable event level tracing. Below are some of the methods to do that.

Method 1: Setting the trace for event 10046 level 8

It is better to make a new connection before you start a trace each time, if not information will be appended if any trace has already been started.

alter session set max_dump_file_size=unlimited;

alter session SET timed_statistics = true;

alter session set STATISTICS_LEVEL = ALL ;

We should be able to distinctly identify our trace file from other trace files. For that we can set tracefile_identifier parameter.

Example:

alter session set tracefile_identifier = index_trace;

So setting this will set the name of our trace files as %index_trace.trc

A simple example of analyzing the index by setting the trace event 10236 is shown below. Event no 10236 is - dump redo on object no. or block type mismatch errors 1410/8103.

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session SET timed_statistics = true;

Session altered.

SQL> alter session set STATISTICS_LEVEL = ALL ;

Session altered.

SQL> alter session set tracefile_identifier = index_trace;

Session altered.

SQL> alter session set events ‘10236 trace name context forever, level 8′;

Session altered.

SQL> analyze index msc.test_idx validate structure online;

analyze index msc.test_idx validate structure online

*

ERROR at line 1:

ORA-01410: invalid ROWID

This is to check some kind of corruption.

SQL> ALTER SESSION SET EVENTS ‘10236 trace name context off’;

Session altered.

Method 2: Using DBMS_SYSTEM package.

Another way to set the event trace is to use the package DBMS_SYSTEM as given below.

exec DBMS_SYSTEM.SET_EV(SID,SESSION#,EVENT#,EVENT LEVEL,”);

Example: exec DBMS_SYSTEM.SET_EV(10,20,10046,8,”);

This will set the tracing for any session.

Method 3: Using DBMS_SUPPORT package.

Another method to set tracing is to use DBMS_SUPPORT package. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink.

Installaing the package first:

SQL> @?/rdbms/admin/dbmssupp.sql

Package created.

Package body created.

SQL>

Using DBMS_SUPPORT package for tracing.

exec DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL#,WAITS,BINDS);

Example: exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);

To stop tracing: exec dbms_support.stop_trace_in_session(10,20);

Start tracing in your own session:

exec dbms_support.start_trace(waits=>true,binds=>false);

Stop tracing in your session: exec dbms_support.stop_trace;

System Level:

If you want to set the event level tracing at system level, then we can either use alter system instead of alter session or we can use init.ora parameter to set the events.

Method 1: Using Alter System

SQL> alter system set events ‘10046 trace name context forever, level 1′;

System altered.

To turn off the tracing at system level again we can use the below command.

SQL> alter system set events ‘10046 trace name context off’;

System altered.

Method 2: Using init.ora parameter

init.ora parameter name is “event” and it can be set as given below. But be careful while setting up this as this will generate huge trace files.

event = “10046 trace name context forever, level 12″