Capturing response-time based SQL statistics using 10046 tracing is a well-documented and accepted best-practice for finding SQL that is contributing to slow application performance. However, reading the raw 10046 trace files can be a challenge, so a formatting tool like the open source OraSRP utility is very helpful.
You can download the OraSRP utility from the author’s website. The current release is 3.2.2 and is available for Windows, Linux, and Macintosh platforms.
Here’s sample output of a 10046 trace file formatted using the OraSRP utility. This example file contains information for two functionally equivalent SQL statements. The first SQL statement (with the worse response time) uses an OR condition in the WHERE clause, and the second SQL statement replaces that OR condition with a UNION to achieve the same result, only faster.
Compare the output from the two queries in terms of logical and physical I/O, total elapsed response time, execution plans, etc., and you will see that the formatted trace file clearly shows that the version of the SQL statement that uses the UNION in place of the OR has a much better response time, performs far fewer I/Os, and places less overhead on the CPU.
