Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Utilities Tips

Analyzing tkprof Results

Step 2: Turn Tracing On

The next step in the process is to enable tracing. By default, tracing is disabled due to the burden (5-10%) it places on the database. Tracing can be defined at the session level:



A DBA may enable tracing for another user’s session by using the following statement:


The sid (Session ID) and serial# can be obtained from the v$session view. This package is owned by the SYS user and therefore the executor must be SYS or be granted the EXECUTE privilege by SYS user.

Once tracing is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific. Table 5.1 below contains the version naming conventions for foreground processes.

Supplied with this information, the DBA can construct a query that will return the trace file for a given session or for all sessions attached to the database. The query below (Users_Trace_Files.sql) will show the trace file name for each process.


column username format a10
column trace_file format a70
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and = 'user_dump_dest'
and = 'db_name'
and b.username is not null;

---------- --------------------------------------------------------
SYS C:\Oracle\admin\ORCL92\udump\ORCL92_ora_03164.trc
SCOTT C:\Oracle\admin\ORCL92\udump\ORCL92_ora_02264.trc
DAVE C:\Oracle\admin\ORCL92\udump\ORCL92_ora_03578.trc

Notice that the trace files are for each session and not for each named user. Given that the SYS user has two connections to the database, the commands for each session would be in separate trace files.

The query can be modified to return the file name for the currently connected session. My_trace_file.sql will return the file name for the current session.

select c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and = 'user_dump_dest'
and = 'db_name';


Both queries above generate the trace file names (with Oracle on Windows XP) that would exist if the session were to be traced. However, there is no indication in any v$ view that a session is currently being traced. The only way to really know if tracing is being performed is to inspect the file names and dates in user_dump_dest directory. For this reason, a DBA should not trace a session indefinitely, as it will continue to consume both performance resources and file system resources. If the user_dump_dest directory fills, the Oracle database will come to a screeching halt.

When the DBA determines that enough data has been gathered, the next step is to disable tracing


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.



Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.