Performance Tuning - Question 086

When a deadlock shutdown is detected by Oracle, where is the trace file generated?

A. SQL_TRACE

B. TRACE_DEST

C. USER_DUMP_DEST

D. CORE_DUMP_DEST

E. BACKGROUND_DUMP_DEST

Answer: C

Explanation:

Oracle creates a user trace file when processing errors in a user’s Server Process. A deadlock error will be shown in a user trace file which is located in the USER_DUMP_DEST. While events such as deadlocks automatically generate trace files, full scale tracing of user sessions does not occur unless the user or DBA requests it.

Incorrect Answers

A: SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.

B: The TRACE_DEST parameter does not specify the location of this file.

D: CORE_DUMP_DEST should point to the directory where core dumps from the Oracle server will be placed. A core dump is a memory image of the Oracle shadow process produced when an unexpected, unrecoverable or invalid condition occurs. Note that Oracle should always try to write a trace file before producing a core dump.

E: BACKGROUND_DUMP_DEST specifies the pathname (directory or disc) where debugging trace files for the background processes (LGWR, DBWn, and so on) are written during Oracle operations.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 40-41

Chapter 2: Sources of Tuning Information