Analyze raw trace files with SQL Developer

Since version 1.5 the Oracle SQL Developer has the capability to view and analyze raw trace files in an interactive way. In this blog post, I will demonstrate how to use this very unknown feature.

Open Trace File

To analyze a trace file, download it from the database server to the client where Oracle SQL Developer is running. Afterward, start Oracle SQL Developer and open the trace file using File > Open or drag and drop the trace file directly into the Oracle SQL Developer window. In my example, I will analyze the trace file hr_selects.trc.

A new tab is opened for the trace file. It provides four different sub tabs.

  • Tree View
  • Statistics View
  • List View
  • History

Most of the tabs provide interactive filter and sort options. For example you can search the trace file for specific SQL statements or sort the executed SQL statements by its elapsed time or used CPU.

Tree View

This tab shows an overview of the executed SQL statements in chronological order. No further information is provided.

Tree View of an opened trace file in Oracle SQL Developer

Statistics View

This view provides a quick overview of the runtimes of each executed statement. Per default, internal SQL statements are excluded (select Include Sys if you want to see them).

Statistics View of an opened trace file in Oracle SQL Developer

List View

This view includes the most information for each statement. To see all statements change the filter from Recursive to All. If the trace file includes wait events and execution plan details, they are shown in the view as well.

List View of an opened trace file in Oracle SQL Developer

History

This tab provides access to the contents of the trace files. It is also possible to manipulate the trace file.

History tab of an opened trace file in Oracle SQL Developer

Summary

Compared with the command line tool tkprof, analyzing a trace file using Oracle SQL Developer is much more convenient. It allows quick filtering and sorting of the content without the need to analyze the file again.

More information about thie feature can be found in the Oracle SQL Developer User’s Guide.

Leave a Reply

Your email address will not be published.