 |
|
Generating the SQL Trace
File
Oracle Tips by
Burleson
|
Once the sql_trace=true command is set
with the alter session command, you will get a trace file for
all SQL commands issued by your session. This trace file is known as a
level-1 trace. You can also issue commands to cause Oracle to create a
super-detailed level-4 trace file where additional detail is required.
Oracle will generate trace files for every
session where the value of sql_trace=true and write them to the
user_dump_dest destination. These file are stored in the form
$ORACLE_SID_ora_nnn.trc in the trace directory, where
nnn is a sequential number. Note that if you are not in the UNIX
dba group, the generated files may not be accessible. If this is the
case, you need to go to the DBA to change the UNIX permissions on the
trace files before you can use TKPROF to format them. It is a good
idea to note the date/time you create the trace file in case others
are also creating trace files, since the identification is by a number
in the filename.
Advanced SQL Trace File Generation
Oracle also provides a facility for getting a
super-detailed trace file called a level-4 trace. This is generally
only done at the request of Oracle Technical Support, but advanced SQL
tuning professionals can sometimes find useful information in a
level-4 trace file.
Generating a level-4 trace file requires that
you know the SID and SERIAL# for the session to trace. You can use the
following query to get the SID and SERIAL# for the session you wish to
trace.
session.sql
rem
session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
ttitle "dbname Database|UNIX/Oracle Sessions";
set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
spool off;
Here is a sample of the output from session.sql.
Note that it shows the OS PID, the SID, the SERIAL#, and the
originating client:
SQL> @session
Sun Apr 01 page 1
UNIX/Oracle Sessions
PID SID SER# BOX USERNAME OS_USER PROGRAM
---- --- ----- ------ ---------- --------
------------------------------
7330 13 11967 sting OPS$ORACLE oracle sqlplus@sting (TNS V1-V3)
8214 10 1261 taz READER root rdbqry_ora@taz (TNS V1-V3)
Once you know the SID, you can issue the following
command to get a level-4 trace file for a user. In this example, we
will trace the session for SID 10:
Connect
system as sysdba;
ORADEBUG SETOSPID 10;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
You can also use the dbms_support package to
generate a level-4 trace file. To use the dbms_support package,
you must specify the SID and SERIAL#:
EXEC
DBMS_SUPPORT.START_TRACE_IN_SESSION(10,1261,waits=>false, binds=>true)
The dbms_support package also has a 'stop'
command to turn off the level-4 trace.
EXEC
DBMS_SUPPORT.STOP_TRACE_IN_SESSION(<SID>, <SERIAL#>)
Remember, these trace files are internal dumps, and they
will not be readable until they are formatted for visual display. This
is where we use the TKPROF utility.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
