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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 

 
 

Create Oracle Trace file for 10046

Oracle Tips by Robert Freeman


Note: Here are related Oracle 10046 trace file notes:
Preliminaries - Before You Enable the 10046 Event

A 10046 trace creates an output dump file. Therefore, before you enable the 10046 trace event, you will need to set some database parameters that control the output of the dump file. These parameters include:
 
  • TIMED_STATISTICS ? Must be set to TRUE to get timing information in your trace files.
     
  • MAX_DUMP_FILE_SIZE ? Controls the maximum size of the trace file. For 10046 trace files, the default setting is generally too small.
     
  • USER_DUMP_DEST ? This is the location the 10046 trace file(s) are written to.
     
  • STATISTICS_LEVEL ? This should be set, at a minimum, to TYPICAL. When set to ALL, more information will be collected in the resulting trace files. All examples in this white paper are with STATISTICS_LEVEL set to TYPICAL.
The default value of this setting in Oracle Database 10g is unlimited, which can be a disaster because Oracle will allow you to fill up a file system if you are not careful. This can be bad news if your USER_DUMP_DEST is set to a location that does not deal well with being filled up. Locations such as ORACLE_HOME and root are not recommended locations to assign USER_DUMP_DEST to. Also, most Oracle platforms allow the MAX_DUMP_FILE_SIZE to be set to unlimited. Be careful if this is your setting! Here are some examples of setting these parameters with the alter session command:
 
ALTER SYSTEM SET timed_statistics=TRUE
 
By default only those who have access to the Oracle user account, or are a member of the Oracle DBA group, will be able to read the output of the 10046 trace event. If you should wish all users to have access to these files, you can set the hidden Oracle parameter _trace_files_public to TRUE.

Consider the security implications of this setting however, as this makes a great deal of information available to the entire user community.

 
Setting the 10046 Event in the Parameter File
 
Oracle offers a regular plethora of ways of setting the 10046 event. You can set the 10046 for the entire database via:
 
  • Changing the parameter file manually
  • Changing the SPFILE via the alter system command.
 
Also you can set the 10046 event for your session with the ALTER SESSION command. Finally, you can set the 10046 event for sessions other than your own using a number of different methods.
 
Let?s look at each of these in more detail.
 
Setting the 10046 Event in the Parameter File
 
If you are using manual parameter files, you can use the event parameter to set the 10046 event as seen in this example:
 
Event=?10046 trace name context forever, level 12?;
 
If there are multiple events in the pfile, they all should be in the same sequences, means if there is some other parameter set between the events, only one set of event specification will be taken.
 
If you use this method of setting the 10046 database event, you will need to shutdown and restart the database in order for it to take effect.
 
Warning: Setting the 10046 event for the entire database is not recommended. This can cause severe performance issues. I just know there will be at least one person who will ask if it?s possible to set the event for the whole database, so we are demonstrating that it can be done.

Setting the 10046 Event in the Parameter File
 
If you are using SPFILES then use the alter system command to add the event record as seen in this example:
ALTER SYSTEM SET
EVENT=?10046 trace name context forever, level 4?
SCOPE=spfile;
 
Note that Oracle does not support the scope=both or scope=memory parameters when setting the 10046 event in this manner. If you want to remove this setting from the SPFILE you can issue this command and then cycle the database:
 
ALTER SYSTEM SET EVENT=?? SCOPE=spfile;
 
Did I mention that setting the 10046 event for the entire database is not recommended and that this can cause severe performance issues?
 
Setting the 10046 Event Using the Alter Session Command
A more common way to set the 10046 event is to enable tracing for your specific user session. To do this you can use the alter session command as seen in this example:
 
ALTER SESSION SET
EVENTs=?10046 trace name context forever, level 12?;
 
Once you are done tracing, either exit your session to stop tracing or you can use the alter session command to stop its use as seen here:
 
ALTER SESSION SET EVENT=?10046 trace name context off?;
 
Setting the 10046 Event For a Session Other than Your Own
 
Most of the time, it?s not going to be your session you will want to trace. It will be the session of somebody who has called you and is complaining that things are taking too long. In this case, there are a couple of ways to enable tracing for another user session. I prefer to use ORADEBUG to enable 10046 tracing on other sessions. There are other methods (such as dbms_system.set_ev), but ORADEBUG seems the easiest to me, so it is the one I will cover in this paper.

Before we can enable tracing of another user?s system with ORADEBUG, we need to know some information about that user. We will cover how to find out the required user session information that you need first, and then we will look at how to use ORADEBUG to set the 10046 events in different sessions.

Finding the Oracle Session We Want To Trace

When we want to trace a user session, what we need is the SID and SERIAL# of that session. The SID is the Serial Identifier of that session, and is unique for each concurrently running session. A session is assigned to a SID when it connects to the database. That SID is released when the session disconnects from the database.
 
Since SID?s can be re-used by subsequent sessions, Oracle includes a SERIAL# column to make each session associated with a SID unique. Thus, if you query V$SESSION and see SID 100 and SERIAL# 101, and query it again and see SID 100 and SERIAL# 103, you know that the original SID 100 disconnected, and a new SID 100 connected to the database. The username may be the same, but the session is different.
 
To discover the SID and SERIAL# of a session, use the following SQL query:
 
SELECT
   a.sid,
   a.serial#,
   b.spid,
   b.pid,
   a.username,
   a.osuser,
   a.machine
FROM
   v$session a,
   v$process b
WHERE
   a.username IS NOT NULL
AND
   a.paddr=b.addr;
 
Note that I included several columns in this query. Sometimes, common user logins might be used in a database. Including the OSUSER and MACHINE columns might make identifying the correct session easier. Here is an example of some output for this command:
 
SID SERIAL# SPID PID USERNAME OSUSERMACHINE
--- ------- ---- --- -------- -------------
162  7 3072  13 SYS  Robert   ROBERTS1
144 36 1864 21 SCOTT Robert   ROBERTS2
146 29 1868 22 SCOTT Robert   ROBERTS3
 
Other V$SESSION columns might also help identify the correct session such as COMMAND, SERVER, TERMINAL, PROGRAM, LOGON_TIME, and LAST_CALL_ET. Also, if you are familiar with the system, joining V$SESSION to V$SQLAREA might also help identify the correct session.
 
Click here to read next section
Click here to read previous section
 
Also note that there are 3rd party tools to format 10046 trace events to make them more readable.
 
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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.