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


 

HTML Text

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:

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.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

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.