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

	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

« Previous Page 1 234 Next Page »

0 Comments (click to add your comment)

Comment and Contribute