Comparing Oracle Query Performance for Faster Applications : Page 3
Determining which queries will provide the best performance for a given application often comes down to a simple question: Does Query X perform better than Query Y? Learn the tools and methods that can help you compare query response times in Oracle.
by Shirish Joshi
Jan 30, 2009
Page 3 of 4
Test Protocol and Example Results
When I ran the tests for this example, I used the following protocol for the utilities:
Explain Plan: All the queries were run three times, and I selected the third output for comparison.
Autotrace: All the queries were run three times, and I selected the third output for comparison.
RunStats: If Query A and Query B were being compared, I ran Query A followed by Query B prior to the comparison. Then I ran the two in the same order and compared the results.
I also carried out a reverse test. Prior to comparison, I ran Query B followed by Query A. Later, I ran Query B followed by Query A and compared the results.
SQL Trace/10046: I ran the queries twice and then turned the tracing on for the third execution. I compared the third executions.
I performed no database restarts or flushing of any memory/cache for any of the tests. Table 3 presents the results from the tests I ran, and Table 4 presents the results of the reverse test I ran with RunStats.
Table 3. Comparison Results for Query Tests by Utility