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

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