Oracle Trace Files

In this post I will give an overview of Oracle trace files. The following are a collection of information from several books (especially [1]). Check out the References section at the end of this post.

Oracle trace files are text files that contain session information for the process that created
them. Trace files can be generated by the Oracle background processes, through the use of
trace events, or by user server processes. These trace files can contain useful information
for performance tuning and system troubleshooting.

Use ASH for troubleshooting a long running query

You wont believe it how useful ASH (Active Session History) can be.

Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :

[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!