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…
Hi Tim,
I have the same concerns.
I wonder if the new 10g “SQL Profiles” might work, since they have the net effect of changing the default execution plan?
Tim
Birmingham and Leeds must be some kind of parallel universe! I too have just used an outline in production for the first time. Same kind of problem: execution plan always fine in SQL*Plus but when the third-party app (using OCI) executes the statement we get a full table scan. I’m managing to replicate the problem with some Pro*C code. Have you read MetaLink note 273635.1? I think this might help you determine the root cause. It seems that the OCI v8 (as used by thick JDBC) peeks at bind variables whereas the thin driver doesn’t. In my case I think we’re seeing something similar. In my case the full table scan is actually the sensible plan, but because the app closes the cursor after a single fetch it benefits from an index range scan despite the bind variable always being assigned the value of 1 and the statement being:
SELECT column containing a sequential value
FROM table
WHERE column containing a sequential value >= :b1
ORDER BY column containing a sequential value;
The app is trying to get the next sequential value you see, and doesn’t use sequences because it’s (altogether now) “database independent”.
Hope that note helps you out; I understand the head scratching this phenomenon causes. Once I get to a definite cause (and can get rid of the outline) for my scenario I’ll update you via this blog entry.
Austin
Amendment to my previous post: I meant fast full index scan and no full table scan
Thanks for the heads-up, I’ll take a look at that note on Monday.
I guess I can take comfort in the fact I’m not going mad and somebody else is seeing this sort of behaviour also.
The interesting part is that you’re having troubles with OCI, that “correctly” peeks … so there’s a bind variable peeking problem underlying, the thin drivers are just hiding it by not peeking 🙂
Watch out for old statistics if that’s the case, in my experience problems with bind variable peeking are almost always a result of statistics “not fresh enough”.
There are other explanations anyway – are you perhaps using cursor_sharing=force or similar ?
Hi.
The stats are fresh and we’re not using cursor sharing. Thanks for the input though.
Cheers
Tim…
Watch out for old statistics if that’s the case, in my experience problems with bind variable peeking are almost always a result of statistics “not fresh enough”.
or the data distribution is very skewed and you get the plan that works for ‘the other case’.
or the data distribution is very skewed and you get the plan that works for ‘the other case’.
Absolutely; in this case, the first statement parsed should run fine (since the plan will be tailored to its bind value) and so some of the other statements that have the “same” selectivity of the first one.
In this case, the solution is to avoid collecting histograms … that should mimic the non-peeking thin drivers behaviour, that run fine.
“The stats are fresh and we’re not using cursor sharing”
Ditto.
One thing that puzzles me is why SQL*Plus doesn’t appear to do the peeking thing. I’d kind of assumed that SQL*Plus would be an OCI app.
This post has been removed by the author.
sqlplus peeks – there are various examples on asktom, for example look at the first part of this followup:
http://tinyurl.com/a6hvm
(btw the followup above is discussing the topic of this “thread” – interesting to know that 10g thin drivers peek, as Tom demonstrates – the issue is for 9i-ones).
It’s EXPLAIN PLAN (or AUTOTRACE) that doesn’t peek, since it ignores the values of bind variables and assumes that they are always VARCHAR2 btw)
hth!
“It’s EXPLAIN PLAN (or AUTOTRACE) that doesn’t peek, since it ignores the values of bind variables and assumes that they are always VARCHAR2 btw)”
Thanks Alberto, that helps. I think we can also add “create outline” to that list. Executing “create outline for …” gave me the plan I wanted the outline to have.
I was looking at the “SQL Profiles” thing mentioned by Don and I guess it would probably work, but I can’t get the automatic SQL tuning feature to offer a suggestion for this query. It always says the query is optimal and it can’t offer any suggestions, hence no profile. I guess if it saw the problem it may offer a profile that solved the problem 🙂