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.
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:
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
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:
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.
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.
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.