ThatJeffSmith

Driving Blind?

I travel quite frequently for my job. Number one on my list of ‘must have things’ on the road is a Navigation device. This used to be my trusty Garmin, but now thanks to my new Android device, I have Google Maps.

There have been times and places where I don’t have the device available – either the GPS satellites cannot be acquired or i’m in D.C. where the accuracy of the positioning seems artificially off. When this happens, I am essentially driving blind.

Sure I know where I want to go, and I know the general direction to take. Sometimes I see a landmark that confirms I am going in the right direction. And, most importantly, I almost always get to my final destination. However, I am pretty sure I never get there in the most efficient manner. In the few times I have thought ahead, I will have picked up a road map of the area. When the GPS is not available, I can always pull over, pick up the map, and carve out my route.

Wow, that was a long setup!

Writing SQL checks your Database can’t cache?
Sorry for the horrible pun, but I could not help myself.

I wonder how developers, and to a lesser extent, business analysts manage to write production-ready SQL without knowing how to read an Execution Plan. When I present to mixed groups, a show of hands generally reveals that only about 10-15% of the audience considers themselves to be execution plan literate. This is like driving without your road map.

How can this be? Well, for one, that used to be me. Back in the day I considered it a ‘win’ if I got data back instead of an error message, and a monumental win if the data that came back looked ‘right.’ When someone is knew to relational databases, set theory, SQL, and the data model of their application, the small victories feel like very big ones.

However, if you have been working with a relational database for several years, or if your SQL is going into a report or application that others users are going to be relying on, you really need to push yourself into learning how to read an execution or access plan.

If you do not know how the server is putting your data together, you are not going to be able to recognize situations where the ‘map’ to your final destination is taking your query/data across all the toll roads and railroad crossings in the database.

When you start to curse ‘Oracle’ or ‘IBM’ or ‘Microsoft’ for making you wait, take a step back and ask yourself if your question/query was written in such a way that you have made it very hard for the database to answer. If you cannot read the plan for your query, then you are putting yourself at the mercy of the database.

Start Learning Today!
No one is asking you to become an expert overnight. But you need to start taking baby steps and pushing yourself in directions you do not feel comfortable in. If you are not willing to do that, you are not going to be able to grow. When you start to feel stupid, then remember that you are in good company, and that there are many people out there willing to help. Do not be afraid to ask!

Every org has that one ‘smart guy’ or ‘smart gal’ that everyone looks up to. These people are not unapproachable. If they are, you have not found the ‘smart’ person on your team. If you are on a team of one or a few, go to the Internet! And do not forget the #sqlhelp and #oraclehelp tags on Twitter.

Links

  • How to read an execution plan
  • My new navigation device
  • Is SQL Optimization an Unsolvable Problem?
  • My White Paper on Understanding Oracle Explain Plans
  • #sqlhelp #oraclehelp via Twitter