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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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

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