Oct 22, 2021
Trace Files- Files
Trace files are a source of debugging information. When the server encounters a problem, it generates a trace file full of diagnostic information.
When a developer executes DBMS_ MONITOR.SESSION_TRACE_ENABLE, the server generates a trace file full of performance-related information.
Trace files are available to us because Oracle is a heavily instrumented piece of software. By “instrumented,” I mean that the programmers who wrote the database kernel put in debugging code—lots and lots of it. And they left it in, on purpose.
I’ve met many developers who consider debugging code to be overhead—something that must be ripped out before an application goes into production in a vain attempt to squeeze every ounce of performance out of the code. Later, of course, they discover that their code has a bug or it isn’t running as fast as it should (which end users tend to call a bug as well; to an end user, poor performance is a bug).
At that point, they really wish that the debug code was still in the code (or had been in there if it never was), especially since you can’t drop debug code into the production system. You have to test any new code before putting it into a production environment, and that’s not something you do at the drop of a hat.
The Oracle database (and Application Server and Oracle applications and various tools such as Application Express (APEX)) is heavily instrumented. Signs of this instrumentation in the database are
•\ V$ views: Most V$ views contain “debug” information. V$WAITSTAT, V$SESSION_EVENT, and many others exist solely to let us know what is going on deep in the kernel.
•\ The AUDIT command: This command allows you to specify what events the database should record for later analysis.
•\ Resource Manager (DBMS_RESOURCE_MANAGER): This feature lets you micromanage resources (CPU, I/O, and the like) within the database. What makes a Resource Manager in the database possible is that it has access to all of the runtime statistics describing how the resources are being used.
•\ Oracle events: These enable you to ask Oracle to produce trace or diagnostic information as needed.
•\ DBMS_TRACE: This facility within the PL/SQL engine exhaustively records the call tree of stored procedures, exceptions raised, and errors encountered.
•\ Database event triggers: These triggers, such as ON SERVERERROR, allow you to monitor and log any condition you feel is “exceptional” or out of the ordinary. For example, you can log the SQL that was running when an “out of temp space” error was raised.
•\ DBMS_MONITOR: This is used to view the exact SQL, wait events, and other performance/behavior-related diagnostic information generated by running your application. The SQL Trace facility is also available in an extended fashion via the 10046 Oracle event.
Instrumentation is vital in application design and development, and the Oracle database becomes better instrumented with each release. Oracle 10g 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 11g took that further with options such as the Automatic Diagnostic Repository (ADR) and the SQL Performance Analyzer (SPA). Oracle 12c 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.
More Details