Trace Analyzer

Trace analyzer is used to interpret raw trace files. It is similar to tkprof but its output is more readable than tkprof. It generates a report with details: time summary, call summary (parse, execute, fetch), identification of top SQL, row source plan, explain plan, CBO statistics, wait events, values of bind variables, I/O summary per schema object, latches, hot blocks, etc.

I like it for its ability of giving values for bind variables used in any SQL statement.

For a given raw SQL Trace generated by EVENT 10046 it provides:

1. Trace identification including actual start and completion time, host name, instance, size, RDBMS version, etc.
2. SQL statements count, user and internal, total and unique.
3. Time summary: elapsed, cpu, non-idle wait events, idle wait events, for user (recursive and non-recursive) and for internal.
4. Call summary for user and internal, with elapsed, cpu, logical reads, physical reads and similar performance details.
5. Summary of wait events, classified by non-idle and idle for user and for internal (if EVENT 10046 level 8 or 12 was used generating trace).
6. Top 20 SQL statements in terms of importance for SQL tuning analysis.
7. List of all unique SQL statements with one-line performance summary per statement.
8. Gaps of no trace activity found on file.
9. List of transactions found (commits and rollbacks).
10. Oracle errors if any.
11. I/O core waits including schema objects affected (tables, indexes, partitions), when traced with level 8 or 12.
12. Top 5 hot blocks, indicating the schema objects (level 8 or 12).
13. Latch wait summary, by name and specific parameters (level 8 or 12).
14. Non-default initialization parameters.

For every SQL statement included in the trace, it includes:

1. Cursor header with SQL statement text, hash value, length, line on trace, depth, user, etc.
2. Oracle errors if any.
3. Call summary (parse, execute and fetch totals).
4. Non-idle and idle wait events (if traced with level 8 or 12).
5. I/O and latch waits summary (if level 8 or 12).
6. First execution and top 10 for particular SQL statement.
7. List of bind variables values for first and top 10 executions if trace was generated using EVENT 10046 level 4 or 12.
8. Cumulative row source plan for all executions of SQL statement.
9. Detailed explain plan if Trace Analyzer is executed on same instance where trace was generated, and if SQL statement made the Top 20 list.
10. Table, index and partition details including row count, CBO statistics and indexed columns if the SQL statement generated an explain plan.

To download the installation script and usage visit http://www.metalink.oracle.com.