Don’t focus on cost of execution plan

I don’t focus on the Oracle optimizer’s cost of a particular execution plan when I’m tuning a query and I’m worried that many of the developers and DBAs that I’ve talked with about query tuning are too focused on lowering the cost of a plan when attempting to tune a query.

I hear comments like this all the time when talking about making a change X to improve the performance of a query.  “I changed X and the cost in the explain plan was so much lower.”  X could be adding an index or hint or making a parameter change, etc.

I just cringe inside when I hear this and I hope I am gracious but all the time I’m filled with fear that the person I’m talking to is missing a key concept when it comes to Oracle query tuning.  The concept is just that in many cases the optimizer’s estimated cost is far off from reality.  So, making a change and seeing the cost of the plan go down really doesn’t mean much.  It could directly correlate to corresponding improvement in the query run time or it could be just the opposite.  It is kind of like the buffer cache hit ratio.  Sometimes this ratio really means something and sometimes it doesn’t.

Instead of focusing on the cost I focus on the plan itself.  Based on my investigation of the tables in the query and how many rows will be accessed from each I’ve come up with an idea of a plan that should be better than the one I’m improving.  So, my question about a proposed change X becomes “Does change X cause the plan to change to the one I determined to be better?”

I attempted to lay out this approach in my Intro to SQL Tuning presentation.  For me query tuning is kind of like programming.  I’m figuring out the best way to really do the steps of the plan based on my own study of the existing tables.  Then I just have to figure out what change to make to get the optimizer to run the query my way.  This is a time consuming approach but I would only spend the time on queries that really need it.  Who has time to tune every query?

So, my recommended approach to query tuning is to figure out a good plan on your own and then to figure out how to get the database to run it your way.  I don’t recommend focusing on what the cost of the new plan is compared with the original plan.

– Bobby

 

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Don’t focus on cost of execution plan

  1. Pingback: Why Doesn't my Hint Work | Oracle Hints

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.