Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement

WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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.



Shirish Joshi is a senior developer at Persistent Systems Ltd. in Pune, India.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

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