Trace Files Page
Note: Available for Oracle 10.2 and later.
Oracle trace files are generated by the Oracle SQL_TRACE facility. They contain:
- The SQL statements executed by traced Oracle sessions.
- The resource requirements of those statements.
Trace files can provide all the information provided by the EXPLAIN PLAN command, plus details of CPU and I/O requirements, and even the number of rows processed by each step in the SQL execution plan.
To open the Trace Files page
- Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Analyze Trace | Trace Files.
Note: Except for the Trace Files page, all sub-pages of Analyze Trace are disabled until a row is selected on the Trace File Details grid. Most sub-pages remain disabled until the file is analyzed.
To analyze a trace file
Right click that row on the Trace File Details grid and select Analyze Trace File. Analyze Trace Settings Dialog
Trace File Details Grid
For Oracle 12.1 and above, Spotlight obtains this information by querying v$diag_dest; the row matching the name Diag Trace returns the new path to the user trace files. For earlier versions of Oracle, trace files are stored in the location specified by the Oracle configuration parameter USER_DUMP_DEST.
| Column | Description |
|---|---|
|
Trace ID |
The internal Spotlight ID for an analyzed trace file, or for a scheduled analysis. (Files that have not been analyzed do not have a specified ID; the value displayed is -99.) Note: Click on the trace file in this grid to open the SQL Analyze Trace Activity Graph. To populate this graph with data, remember to analyze the trace file. If the trace file has not been analyzed yet, right-click its row and select Analyze Trace File. Analyze Trace Settings Dialog |
|
Name |
The name of an Oracle trace file. Usually this takes the form header_process-id.trc (for example, ora_12345.trc). |
|
Size (KB) |
The size of the trace file. |
|
Modified |
The last time the trace file was modified. |
|
Last Analyzed |
The last time the trace file was analyzed. |
|
Load Status |
The current status of the analysis. |
|
Lines Loaded |
The number of lines in the trace file. |
|
SQL Statement Count |
The number of times a SQL statement was parsed, executed, or fetched. |
|
Comment |
The comments (if any) that you add when you choose to analyze the trace file. |
|
Elapsed Load Time (ms) |
The time taken to analyze the trace file. |
|
Error Line No |
For failed analyses, the line in the trace file that contains the error. |
|
Error Text |
The text of the error that caused the failure. |
|
Oracle Version |
The Oracle version for the specified trace file. |
Note: When Spotlight returns the contents of the table, it does so in stages, with the details for analyzed files — returned from SQL*Net — being displayed more quickly than the details for files not yet analyzed, which are returned by the operating system.