I used a stored outline in a production environment for the first time yesterday. I can’t say I’ve ever seen the need to do this before, but this time it masked a pretty serious problem. Normally I try to let the optimizer do its own thing. I even avoid optimizer hints if I can, but in this case the stored outline was the only solution.
The issue was that an SQL statement generated by a finder method in a J2EE application was acting differently depending on JDBC driver being used to make the database connection. When the connection was made via the thin driver the SQL statement used the correct execution plan and worked real quick. When the OCI driver was used the same SQL statement was using a ridiculous index, causing it to trawl through loads of data, making it take around 4 seconds to complete. In this case the impact was massive as this same statement is executed many times throughout the system.
This begs two questions:
- Why is it happening?
- Is there a way to force a good execution plan until we can solve the real issue?
The answer to the first questions is still under investigation, but my answer to the second questions was to use optimizer hints! Nice idea, but it won’t work! Unfortunately the SQL is generated at runtime by the OC4J container so we have little influence over the actual statement produced. Then I thought *stored outlines*. A couple of minutes later I had created an outline for the query and both drivers were giving the same performance!
I knocked up a quick article on Stored Outlines and Plan Stability on my website.
In this case the stored outline saved my bacon by hiding a problem until we can resolve it, but they kinda scare me. When you add a hint to an SQL statement it’s there for the whole world to see. People will only know the outline exists if I tell them about it. It’s going to be really easy for everyone to forget about it, then get confused when another environment doesn’t react in the same way.
I guess I’ll put stored outlines in the same category as triggers. Useful at times, but avoid if possible because sooner or later they are going to screw you up 🙂
My list of things to do now includes:
- Find out why the same statement acts differently with the two drivers.
- Try to convince everyone that not every SQL problem we’ve ever had can be fixed using stored outlines.
- Convince the world that Java is a great language, but J2EE sucks. Runtime generation of SQL is certainly fuel for the fire 🙂
Cheers
Tim…
PS. The latest IOUG SELECT magazine has one of my articles in it. I even made the front page. If you’re bored turn to page 6 and check it out. Next stop Rolling Stone…