User Trace File - Oracle DBA

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:

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:


Enabling Tracing

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
Locating the User Trace Files

From a Linux operating system command-line session, Janice attempts to locate the trace file:

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:

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:

Locating-the-User-Trace-Files

Converting the Trace File

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

PE: OK to rebreak here too?

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

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

Converting the Trace File

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics