RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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.


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
Explain Plan Autotrace SQL Trace/10046
Query 1(Version A) Explain Plan_1a.txt Autotrace_1a.txt Trace_7.txt  |  Trace_7_OraSRP.html
Query 1(Version B) Explain Plan_1b.txt Autotrace_1b.txt Trace_7_2.txt  |  Trace_7_2_OraSRP.html
Query 2(Version A) Explain Plan_2a.txt Autotrace_2a.txt Trace_8.txt  |  Trace_8_OraSRP.html
Query 2(Version B) Explain Plan_2b.txt Autotrace_2b.txt Trace_8_2.txt  |  Trace_8_2_OraSRP.html
Query 3(Version A) Explain Plan_3a.txt Autotrace_3a.txt Trace_9.txt  |  Trace_9_OraSRP.html
Query 3(Version B) Explain Plan_3b.txt Autotrace_3b.txt Trace_9_2.txt  |  Trace_9_2_OraSRP.html

Table 4. Comparison Results of RunStats Reverse Test
Query 1A vs. Query 1B RunStats_1.txt
Query 1B vs. Query 1A RunStats_1_2.txt
Query 2A vs. Query 2B RunStats_2.txt
Query 2B vs. Query 2A RunStats_2_2.txt
Query 3A vs. Query 3B RunStats3.txt
Query 3B vs. Query 3A RunStats3_2.txt

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date