14.4. 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.
14.4.1. 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 and 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.14.4.2. 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 /u01/app/oracle/admin/ord/udump
From a Linux operating system command-line session, Janice attempts to locate the trace file:
[oracle@oltp udump]$ ls -l ord_ora*.trc -rw-r----- 1 oracle oinstall 748 May 30 10:43 ord_ora_11141.trc
-rw-r----- 1 oracle oinstall 748 May 18 20:37 ord_ora_11325.trc -rw-r----- 1 oracle oinstall 751 May 12 12:08 ord_ora_12283.trc -rw-r----- 1 oracle oinstall 97787 Jun 22 00:23 ord_ora_12299.trc -rw-r----- 1 oracle oinstall 7611 Jun 20 22:57 ord_ora_13863.trc -rw-r----- 1 oracle oinstall 1609855 Jun 22 00:58 ord_ora_13991.trc -rw-r----- 1 oracle oinstall 1426 Mar 14 13:15 ord_ora_14115.trc -rw-r----- 1 oracle oinstall 798 Jun 22 01:25 ord_ora_15082.trc -rw-r----- 1 oracle oinstall 1666 Jun 20 17:50 ord_ora_1531.trc -rw-r----- 1 oracle oinstall 1382 Mar 14 13:33 ord_ora_15338.trc -rw-r----- 1 oracle oinstall 630 Jun 22 01:25 ord_ora_15763.trc -rw-r----- 1 oracle oinstall 1031 Jun 22 01:26 ord_ora_15814.trc -rw-r----- 1 oracle oinstall 1665 Jun 22 01:30 ord_ora_16099.trc -rw-r----- 1 oracle oinstall 632 Mar 21 15:47 ord_ora_22552.trc -rw-r----- 1 oracle oinstall 750 Mar 13 20:54 ord_ora_23172.trc -rw-r----- 1 oracle oinstall 63729 Jun 23 20:21 ord_ora_23342.trc -rw-r----- 1 oracle oinstall 1668 Mar 10 21:34 ord_ora_31113.trc -rw-r----- 1 oracle oinstall 628 Jun 16 20:40 ord_ora_3113.trc -rw-r----- 1 oracle oinstall 1177 Mar 29 00:36 ord_ora_3325.trc -rw-r----- 1 oracle oinstall 628 Jun 19 19:33 ord_ora_3345.trc
-rw-r----- 1 oracle oinstall 976 Mar 21 21:36 ord_ora_4463.trc -rw-r----- 1 oracle oinstall 958 Jun 21 22:08 ord_ora_4474.trc -rw-r----- 1 oracle oinstall 1665 Mar 22 20:08 ord_ora_4529.trc -rw-r----- 1 oracle oinstall 1048 Mar 14 12:29 ord_ora_9813.trc -rw-r----- 1 oracle oinstall 39563 Jun 22 23:34 ord_ora_982.trc -rw-r----- 1 oracle oinstall 1407 Mar 14 12:51 ord_ora_9843.trc [oracle@oltp udump]$
Which trace file is the right one? The datestamp of each file helps to narrow down the search, but there could be multiple users creating trace files at the same time. 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 ------------ 23342 1 row selected.
Given the operating system process number of 23342, Janice knows that she needs to analyze the user trace file ord_ora_ 23342.trc. However, when she opens this trace file in Notepad, it is not very readable:
14.4.3. Converting the Trace File
To convert the trace file into something more readable, Janice uses the Oracle utility TKPROF:
TKPROF
An Oracle utility that reformats a user trace file containing SQL statement statistics into a readable format.
PE: OK to rebreak here too?
[oracle@oltp udump]$ tkprof ord_ora_23342.trc ord_ora_23342.txt TKPROF: Release 10.1.0.2.0 - Production on Wed Jun 23 21:04:24 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. [oracle@oltp udump]$
Janice reviews the file ord_ora_23342.txt and finds that the ou ut 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.
|
