Files - Expert Oracle Database Architecture - page 89

among others. Instrumentation is vital in application design and development, and the Oracle database becomes
better instrumented with each release. In fact, the amount of additional instrumentation in the database between
Oracle9 i Release 2 and Oracle 11 g , and now Oracle 12 c , is phenomenal. Oracle 10 g took code instrumentation in the
kernel to a whole new level with the introduction of the Automatic Workload Repository (AWR) and Active Session
History (ASH) features. Oracle 11 g took that further with options such as the Automatic Diagnostic Repository (ADR)
and the SQL Performance Analyzer (SPA). Oracle 12 c advanced even further with the addition of a DDL log to track all
DDL operations in a database (something that shouldn't be happening in many typical production databases day to
day) and the debug log to track exceptional conditions in the database.
In this section we're going to focus on the information you can find in various types of trace files. We'll cover what
they are, where they are stored, and what we can do with them.
There are two general types of trace files, and what we do with each kind is very different:

• Trace files you expected and want : These are, for example, the result of enabling DBMS_
MONITOR.SESSION_TRACE_ENABLE . They contain diagnostic information about your session and
will help you tune your application to optimize its performance and diagnose any bottlenecks
it is experiencing.

• Trace files you were not expecting but the server generated as the result of an ORA-00600
“Internal Error”, ORA-03113 “End of file on communication channel”, or ORA-07445 “Exception
Encountered” type of error. These traces contain diagnostic information that is most useful to
an Oracle Support analyst and, beyond showing where in our application the internal error
was raised, are of limited use to us.

Requested Trace Files
The trace files you typically expect to be generated as the result of enabling trace via DBMS_MONITOR ( ALTER SESSION
SET SQL_TRACE=TRUE in Oracle9 i Release 2 and earlier), or using the extended trace facility via the 10046 event, might
be as follows:

EODA@ORA12CR1> alter session set events
2 '10046 trace name context forever, level 12'
3 /
Session altered.

These trace files contain diagnostic and performance related information. They provide invaluable insights into
the inner workings of your database application. You will see these trace files more often than any other kind of trace
file in a normally operating database.

File Locations
Whether you use DBMS_MONITOR, SQL_TRACE or the extended trace facility, Oracle will start generating a trace file on
the database server machine in one of two locations:

• If you are using a dedicated server connection, the trace file will be generated in the directory

specified by the user_dump_dest parameter.
If you are using a shared server connection, the trace file will be generated in the directory

•

specified by the background_dump_dest parameter.
To see where the trace files will go, you can issue the show parameter dump_dest command from SQL*Plus,
query the V$PARAMETER view, use the routine we created earlier ( SCOTT.GET_PARAM ), or query the new V$DIAG_INFO
view. We'll demonstrate each in turn next.