User Trace Files

User trace files, as the name implies, contain information pertaining to any error conditions triggered by a command in an individual user’s session. User trace files can also help the DBA to optimize the performance of SQL statements by producing statistics for each SQL statement in a user session. The location for user trace files is specified by the system parameter USER_DUMP_DEST.

user trace file

A text file that contains information pertaining to any error conditions triggered by a command in an individual user’s session or SQL statement information for the purposes of tuning and optimization. User trace files are stored in the directory specified by the system parameter USER_DUMP_ DEST.

Enabling Tracing

The users in the HR department want to optimize some of their queries, so they decide to use user trace files to save the statistics in the USER_DUMP_DEST directory. The first step is to turn on tracing:

alter session set sql_trace = true; Session altered.

One of the users in the HR department runs a typical query joining the EMPLOYEES and the DEPARTMENTS table, then immediately turns off the tracing:

select employee_id emp_id, last_name, first_name,    department_id dept_id, department_name from hr.employees join hr.departments    using(department_id);   EMP_ID LAST_NAME   FIRST_NAME  DEPT_ID DEPARTMENT_NAME -------- ----------- ----------- ------- ---------------      100 King        Steven           90 Executive      101 Kochhar     Neena            90 Executive      102 De Haan     Lex              90 Executive ...      205 Higgins     Shelley         110 Accounting      206 Gietz       William         110 Accounting 106 rows selected. alter session set sql_trace = false; Session altered.

Locating the User Trace Files

Janice, the DBA, has agreed to help out the HR department by analyzing the user trace file. First, she needs to find out where the user trace file is stored:

show parameter user_dump_dest; NAME                  TYPE      VALUE --------------------- --------- -------------------------- user_dump_dest        string    d:\oracle\admin\or92\udump

From an operating system command-line session, Janice locates the trace file:

D:\>cd oracle\admin\or92\udump D:\ORACLE\ADMIN\OR92\UDUMP>dir  Volume in drive D is DAT  Volume Serial Number is 2C2D-238A  Directory of D:\ORACLE\ADMIN\OR92\UDUMP 11/16/2002  10:48 PM    <DIR>          . 11/16/2002  10:48 PM    <DIR>          .. 11/16/2002  02:14 PM               740 or92_ora_180.trc 11/10/2002  02:36 PM               609 or92_ora_18660.trc 11/07/2002  09:11 PM               628 or92_ora_2076.trc 11/10/2002  02:33 PM               609 or92_ora_21776.trc 11/16/2002  10:38 PM             1,247 or92_ora_2348.trc 11/07/2002  08:59 PM               713 or92_ora_2788.trc 11/07/2002  09:13 PM               743 or92_ora_2924.trc 11/07/2002  09:05 PM               713 or92_ora_3160.trc 11/07/2002  08:59 PM               629 or92_ora_3224.trc 11/07/2002  08:50 PM               628 or92_ora_3344.trc 11/07/2002  08:50 PM               712 or92_ora_3784.trc 11/07/2002  09:05 PM               629 or92_ora_4060.trc 11/16/2002  02:09 PM               633 or92_ora_56600.trc 11/16/2002  10:49 PM             2,522 or92_ora_5996.trc               14 File(s)         11,755 bytes                2 Dir(s)  40,448,329,216 bytes free

Which trace file is the right one? Janice must join the V$PROCESS and V$SESSION dynamic performance views to retrieve the operating system process number, which Oracle uses in the trace filename:

select spid from v$process v, v$session s    where v.addr = s.paddr and s.username = ‘HR’; SPID ------------ 5996 1 row selected. 

Given the operating system process number of 5996, Janice knows that she needs to analyze the user trace file or92_ora_5996.trc. However, when she opens this trace file in Notepad, it is not very readable:

Converting the Trace File

To convert the trace file into something more readable, Janice uses the Oracle utility TKPROF:

D:\ORACLE\ADMIN\OR92\UDUMP>tkprof or92_ora_5996.trc      or92_ora_5996.txt TKPROF: Release 9.2.0.1.0 - Production      on Sat Nov 16 23:24:55 2002 Copyright (c) 1982, 2002, Oracle Corporation.       All rights reserved. D:\ORACLE\ADMIN\OR92\UDUMP>

TKPROF

An Oracle utility that reformats a user trace file containing SQL statement statistics into a readable format.

Janice reviews the file or92_ora_5996.txt and finds that the output is much easier to interpret. A sample of the output is shown below.

Using statistics from the trace file such as CPU time and elapsed time can help Janice focus on which of the HR department’s SQL statements need tuning.

Tip

Oracle provides two websites that can assist the DBA when trouble strikes. Metalink, Oracle’s trouble reporting site at http://metalink.oracle.com, is a subscription service that allows DBAs to submit problem reports (either online or by phone) and search the knowledge base of all other problems submitted to Oracle support staff. Oracle’s technology network, http://technet.oracle.com, is a free service, although user registration is required to access the site. Technet contains searchable product documentation, trial versions of most of Oracle’s software, discussion forums, sample code, white papers, and more.