Oracle Session Tracing Part V

Friday Jan 21st 2005 by James Koopmann

Part five of this series will re-acquaint you with Oracle's tracing mechanism. Read on to learn about creating trace files for TKPROF.

Part five in our series will get closer to what we have, for years, experienced as Oracle's tracing mechanism. Read On and get re-acquainted with creating trace files for TKPROF.

For many years, DBAs have been getting low level tracing information about sessions through the creating and reading of Oracle generated trace files. These trace files can be generated against a full system load or individual sessions. The types of information generated by these traces in Table 1 clearly show why they have been used for many years. There just was not an easy way to get this information from within Oracle's internal views. While Oracle 10g has provided quite a few mechanisms to trace the session by querying internal views, the generation of trace files is still around and still quite viable. In addition, Oracle 10g allows us to generate trace files and analyze them with added functionality. This article will focus on how we can generate trace files in Oracle 10g.

Table 1

Laundry list of information gathered by tracing

  • SQL Statements
  • Explain Plans
  • Parse, execute, and fetch counts
  • CPU and elapsed times
  • Physical reads and logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback
  • Wait event data
  • Row operations
  • logical, physical and elapsed times I/O types

Creating a Trace

Old Method

It used to be that if you wanted to create a trace file you had to enable it at the session level using the DBM_SESSION.SQL_TRACE procedure or through the ALTER SESSION SET SQL_TRACE command. While these accomplished the task, we will quickly see they are very limited in scope compared with the new method. Not only are they limited in scope, their usage is quite limited. In order to use these two statements you need to issue them from the connected session. That means that if you wanted to generate a trace file, your application would need to be modified to issue these commands. Many programmers got around this by reading a control structure during execution to see if they should turn on trace. Moreover, if someone wanted to trace an application he would just flip a flag in the control structure. Below are two examples, just in case you wish to try these methods or have a need.

SQL> exec dbms_session.set_sql_trace(true);

New Method

Using DBMS_MONITOR Package to generate trace files

In Part III of this series, we discussed how to generate trace files with the DBMS_MONITOR package. Revisit Table 2 and look at the procedure calls required to generate a trace file. Be sure to look at the description box and example given as it signifies the method you can trace against (by CLIENT_IDENTIFIER, SERVICE_NAME, MODULE, ACTION or SID). In Oracle 10g, we have been given the DBMS_MONITOR package. This package allows us to interact and control the tracing and statistics gathering of sessions through a PL/SQL interface, which I think, is much easier than the old method of DBMS_SESSION and ALTER SESSION commands. In addition, these methods in table 2 give you more flexibility against what you can trace. You are now able to do fine grain or group tracing across sessions giving you, the DBA, much needed control. Also, remember from Part III that there is the audit ability of the tracing through the Oracle DBA_ENABLED_TRACES view to show you what tracing you have enabled. These new methods also give the DBA the ability to enable trace for sessions and those applications do not need to issue the command themselves.

Table 2

Click for full table

Where is my trace file

When you enable traces, Oracle dumps this information out to a system file in a destination on disk referred to as USER_DUMP_DEST. This is an actual parameter within Oracle and you will need to know what it is set to if you ever want to view the contents of the trace file you just generated. There are a couple of ways that you can determine this parameter given below.

SQL > show parameter user_dump_dest
SQL > select value from v$parameter where name = 'user_dump_dest';

When you go searching for your trace file in the USER_DUMP_DEST area you may quickly find that it is loaded with other users' trace files. To make matters worse Oracle generates a somewhat cryptic name for your trace files that makes it somewhat hard for you to find them. You can modify the name of the trace file by adding a TRACEFILE_IDENTIFIER to it. You can do this by an ALTER SESSION command such as what is below.

SQL > alter session set TRACEFILE_IDENTIFIER = 'something_here';

While we can get a lot of real-time session information through Oracle internal views, the Oracle trace is still a valuable tool for extracting statistics for a single session or across a grouping of sessions by the added functionality given through the DBMS_MONITOR package. After generating trace files, we can now use the TKPROF or new Oracle 10g trcsess utility to extract and read the collected information in a readable form. However, that is reserved for the next and final article of this series.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site