Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational