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);
SQL> ALTER 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
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.
