Over the years I have presented to probably thousands of people. During that time I have had more than one person ask me “Mark, what is an explain plan”.
I reply, “That is the method that Oracle uses (or whatever database it may be) to determine the best way to return data that you are looking for with your query. This is known as Optimization”.
“The database is looking for the best path to take with the least resource usage to return your query results. Every query has a cost - CPU, memory, disk access, etc.”
Then after I see the stares I continue with “Everything you do in life has an explain plan. When you got up this morning to come into work you went out and started your car. This used some gas and oil. Then you started driving down the street. More gas and oil, and now there is transmission fluid and tires and brakes being used.”
“As you are driving you are figuring out the shortest route to get you to work. You want to take the most efficient path to work. The one that gets you there the fastest and uses the least amount of gas, oil, tires, brakes, etc. This is called Optimization”.
Then GPS came out on your phone and now you can change your route in real-time to make it even more efficient.
You are trying to cut down on cost. This is called Optimization.
Now think about everything you do in your life. Aren’t you trying to do things the most efficient way possible? Grocery shopping, mowing the grass (the most efficient way I have found for this task is to have someone else do it ;), going to the movie. Everything you do.
At work we are always trying to get our data back faster.
In life we are always trying to get to the coffee shop faster, have to beat that line in the drive-thru.
We are trying to get our burger faster by going to the drive-thru (I have had mixed results with this one, just ask Joe Pesci).
So why would a database be any different?
Most optimizers do a pretty good job of optimization but there are ways that we can influence the optimizer. Hints can cause a different path to be taken, indexes can affect the optimization path, statistics on the tables, etc., etc.
One of the biggest ways, but usually the most difficult way, to help the database optimizer it to rewrite the query so it performs better.
Groan.
Not as easy as getting a cup of coffee in the drive-thru but the rewards are better for your application/business/blood pressure over the long run, and, we can help!
By using the Quest SQL Optimizer we can take your query and generate rewrites for it. You can then execute them and generate alternative queries and see how they compare to the original query.
There are multiple ways we can do this and I won’t get into all of them here but you can find more information on the SQL Optimizer page on ToadWorld.com. For now just know that we can help you get your queries optimized and help make your life a little easier, even if I can’t speed up the line at the coffee shop.
-Mark
