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:

ALTER SESSION SET SQL_TRACE = TRUE;

DBMS_SESSION.SET_SQL_TRACE(TRUE);

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

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);

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.

users_trace_files.sql

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 c.name = 'user_dump_dest'
and d.name = 'db_name'
and b.username is not null;

USERNAME TRACE_FILE
---------- --------------------------------------------------------
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 c.name = 'user_dump_dest'
and d.name = 'db_name';

TRACE FILE
---------------------------------------------------------------
C:\Oracle\admin\ORCL92\udump\ORCL92_ora_03164.trc

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: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.