- The files that are created when an oracle background process encounters an exception are Trace files.
- Processes like dbwr, lgwr, pmon, smon create them.
- Trace files are also created by ORA-00600 error or due to some diagnostic dump events
- The SQL Trace facility provides performance information on individual SQL statements.
It generates the following statistics for each statement:
a. Parse, execute, and fetch counts.
b. CPU and elapsed times.
c. Physical reads and logical reads.
d. Number of rows processed.
e. Misses on the library cache.
f. Username under which each parse occurred.
g. Each commit and rollback.
- The SQL Trace facility can be enabled for a session or for an instance.
- When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
- The additional overhead of running the SQL Trace facility against an application with performance problems is normally insignificant compared with the inherent overhead caused by the application's inefficiency.