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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








When to use Oracle 10046 Trace

Oracle Tips by Robert Freeman

Note: Here are related Oracle 10046 trace file notes:
Shortcomings of the 10046 Event
So, the 10046 event has some very interesting sounding information in it, doesn't it? We can track the execution of a whole session, find out what the bind variables being used are, we can track and aggregate wait times and so on. So what is the problem? Let's just use it all the time.

Wrong. First of all, interpretation of a 10046 trace file can take time, even if you run it through TKPROF and take the canned results it gives you. Culling through a 10046 with hundreds of SQL statements, and the related recursive SQL, parse, execute, fetch, wait and stat records is not a fun thing. While we provide a little tool at the end of this paper to try to help you do so, it's still not something that you want to be doing every day.
So, if we don't use the 10046 all the time, when do we use it? Here is my point of view. If you are a consultant coming in to fix a system that has a number of global performance problems, the first thing to do is to actually look over the database/instance from a top down approach.
Look at the memory allocations, look at parameters like db_file_multiblock_read_count and quickly sanity check them. Most experienced DBAs can quickly tell if a parameter is set completely out of whack. I've had a few engagements where one parameter was totally and completely set wrong and it just almost immobilized the entire system (for example, databases setup using the old default Oracle init.ora files with the small or medium SGA settings in place). This is something that you can quickly do as a DBA, and reap some potential low cost rewards for your client.
After checking the database, run a few statspack (or AWR if I'm running Oracle Database 10g, any reference to Statspack should also include AWR)  reports. I like to run them during normal load, and during reported problem times. I'll generally run snaps 15 minutes apart for an hour or two. Statspack provides a wealth of information that can, again, point to some immediate problems that stick out like a sore thumb.
It might be that materialized view that takes 45 minutes to refresh (and was originally scheduled to run every 15 minutes because at one time it only took an hour to run). You might find some pitifully bad SQL statements that clearly need help. Latching issues, IO contention, are all easily detected by statspack without having to generate 10046 trace files, and in a fairly short amount of time. Also, when reviewing statspack, look at the system statistics. How many rows are gotten through index lookups vs. full table scans? If there is an imbalance, you might need to look at indexing opportunities.
10046 traces can be troublesome in environments that use connection pooling. Particularly when a single user experience can float over several connections. I've seen application code that does database disconnect/connect operations while the user is still active, or even during batch processing operations. This makes finding the problem very hard, 10046 or no. 
Related to connection pooling issues are issues with RAC clusters where sessions will switch between different nodes of the cluster during the same process. Also, I've had issues when a single record flows through a system very quickly, and it is passed from process to process rapidly. Each process creates a new connection, to a new node if you are running RAC. This makes 10046 tracing difficult.
As you might expect, 10046 tracing can have performance impacts, and depending on the load profile of the system, and the number of sessions you are tracing, you can cause serious problems. This is, perhaps, the Oracle interpretation of Heisenbergs Uncertainty principle. The more we try to figure out what our system is doing, the more we really can't tell because we start to impact the system itself with our observation.

The Ion tool is the easiest way to analyze Oracle load profiles and Ion allows you to spot hidden performance trends.

When do I start running 10046 events then? I run them when a process is having problems that can be identified, and if I can control which session (or sessions) need to be traced without impacting other things. When users can clearly point to processes that need help, and I cant clearly detect wayward SQL via other means (statspack, v$sqlarea, etc) I'll start running 10046 traces.
Click here to read next section
Click here to read previous 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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.