Comparing Oracle Query Performance for Faster Applications : Page 4

Analysis of Test Results

For the Query 1A vs. Query 1B comparison, Query B is better:

• Explain Plan: Query B will cost about 25 percent of Query A.
• Autotrace: Query B will use one-fourth the number of Logical I/Os (consistent gets) as Query A will.
• SQL Trace/10046: Query B performed one-fourth the I/O (physical and logical) as did Query A. The disk and query columns in the output represent the physical and logical I/O. The execution time of Query B was also less than that of Query A.



• Based on RunStats: Query B used fewer latches than to Query A did, and therefore will scale better with more data. (Note: The numbers reported by the utility vary greatly, and it is not easy to equalize the effects of caching.)

For the Query 2A vs. Query 2B comparison, Query B is better:

• Explain Plan: Query A will cost about 80 percent of Query B.
• Autotrace: Query A will use about twice the number of logical I/Os (consistent gets) as will Query B.
• SQL Trace/10046: Query A performed twice the I/O (physical and logical) that Query A did. While the elapsed time of A is more than the elapsed time of B, A's CPU time is less than B's.
• Based on RunStats: Query A used more latches than Query B did, and therefore will not scale as well with more data. (Note: The numbers reported by the utility vary greatly, and it is not easy to equalize effects of caching.)

For the Query 3A vs. Query 3B, Query A is better than Query B:

• Based on Explain Plan: Query A will run at about 25 percent the cost of Query B.
• Based on Autotrace: Query A will perform 65 percent of the logical I/Os (consistent gets) that Query B will.
• Based on SQL Trace/10046: Query A performed about 65 percent of the I/O (physical and logical) that Query B did. The elapsed and CPU times of Query A are better than those of Query B.
• Based on RunStats: Query A used fewer latches than Query B did, and therefore will scale better with more data. (Note: The numbers reported by the utility vary greatly, and it is not easy to equalize effects of caching.)

Comparing the Utilities as Well

Given the right test data, SQL Trace/Event 10046 is the best comparison tool because it reports the actual execution statistics. Explain Plan and Autotrace are quick-and-dirty solutions that present estimates. RunStats results vary greatly, and the version used for the tests here (Tom Kyte's) cannot equalize the effects of caching. However, Connor McDonald has a version that captures multiple snapshots, which may alleviate these issues.

Previous Page