By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.
Comparing Oracle Query Performance for Faster Applications : Page 4
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.
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
