| |
 |
|
Types of Oracle trace events
Oracle Tips by
Robert Freeman |
Note: Here are related
Oracle 10046 trace file notes:
Using
Oracle Event traces
Oracle events are designed to cause the Oracle kernel to act in ways
that are not consistent with the default operation of the Oracle
database. Events are used for debugging, to avoid bugs, and to trace
Oracle database activity. In this paper, we will review the Oracle
10046 event in particular, and see how it can be useful in
performance tuning efforts.
We will begin the paper with a discussion on Oracle database events
in general and then we will discuss the 10046 event in more detail.
We will discuss the value of the 10046 event when tuning Oracle
databases and then we will look at interpreting the 10046 event
output. Finally, we will provide you with an elementary way of
loading a 10046 trace file into your database using external tables,
so you can process the data in your 10046 and review the results.
Note: This document uses Oracle Database 10g for all examples. The
10046 trace file contents differ greatly between different versions
of Oracle.
Disclaimer
The purpose of this white paper and the attendant presentation is
not to be the be all/end all reference to 10046 tracing. There are
some great books documented at the end of this paper, that do this
just fine. The idea here is that I want to get you to thinking about
what a 10046 trace file can do for you, and maybe too, what it can
not do for you. Then, I present a method of actually loading the
10046 trace files you generate into the database so you can
construct your own queries, and play with the data generated in
these very powerful trace events.
All information in this paper is believed to be accurate. I assume no liability, responsibility
and offer no warranty, expressed or implied, for any of the content
contained in this paper nor any of your actions related to the
contents of this paper. Test this stuff on a production database, or
any database that is sensitive to things like total complete loss,
and you are on your own.
Oracle Events
Oracle events are assigned a unique number that identifies the
event. Oracle events used to be the mysterious feared genie that we
never touched unless Oracle instructed us too, lest the genie pop
out of the bottle and decide to grant us only one wish, that of a
corrupted database.
Over time, a number of Oracle experts out in the field experimented
with these events, and discovered that they were not all of the
dangerous, mysterious variety. Indeed, the genie was not even in his
bottle in many cases.
These people deserve the credit for much of the public information
available for the 10046 event, and the information they worked hard
to make public is in large part responsible for the paper you see
before you. As always, information is gleaned from many places, and
in this paper you will find a few of my own ideas to boot.
There are four different kinds of Oracle event types. These are:
? Process Trace events - The 10053 and 10046 events are trace
events. These trace certain system operations continuously until the
tracing is canceled. The results are written to a trace files.
?
Events that change database behavior ? These events are
designed to modify the behavior of the database. An example is event
10262 that causes the database to stop reporting memory smaller than
a defined size.
?
Immediate - These events dump diagnostic information. This
information is dumped as soon as the event is executed. These
includes things like system state dumps and dumps of file headers.
?
On error events - These events are not unlike immediate
events, in that they dump information immediately. This time though,
execution of the event is triggered by an error event. The result is
a dump file that can be used to discover the cause of the error.
As a DBA you probably have used at least one event in your past.
The 10046 Trace Event
The 10046 event allows you to track, at a very fine level, the
execution of a given set of SQL statements. The 10046 event can be
set at both the session level (for tracing of a specific session) or
at a database level. It can be set either via a parameter file
setting, or it can be set dynamically for the database, for your
session or even for another session.
The 10046 even can also be set to cause the trace output to be
generated with differing levels of detail. For example, level 1
tracing is equivalent to the output you get when enabling the
SQL_TRACE facility within Oracle (ALTER
SESSION SET sql_trace=TRUE).
The real bang from the 10046 trace results is in enabling the higher
levels of detail. With these higher levels of detail you can see
wait related information and bind variable related information. The
following table lists the typically used event levels present in the
10046 trace event in Oracle Database 10g:
|
10046 Trace Level
|
Description
|
|
1
|
Basic trace level. Like the standard SQL_TRACE trace file.
Provides statistics for parse, execute, fetch, commit and
rollback database calls.
|
|
4
|
Displays bind variables
|
|
8
|
Displays wait statistics
|
|
12
|
Displays wait statistics and bind variables
|
So, there can be no doubt that the higher levels of information can
be quite useful in one's tuning efforts. Wouldn't it be nice to see
how long your SQL statement waits for disk reads, or how long it
waits on the redo allocation latch? Wouldn't it be nice to know
exactly what it is that makes your month-end processing take so
long?
The problem (as you will soon see) is that these
trace files are quite full of information, and interpretation of
these files is perhaps as much fun as mowing over a hive full of
hornets. In this presentation we will try to give you some ideas on
using the Oracle database to get at the information stored in these
trace files. Before we can actually look at trace files, we need to
create them. Let's look at how to enable the 10046 trace event in
Oracle next.
Also
note that there are 3rd party tools to
format 10046 trace events
to make them more readable.
Click here to read
next section
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
|
and include the URL for the page.
