| |
 |
|
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. |
|
|
|
and include the URL for the page.
