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