Weird issue hidden by outlines…

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 🙂



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…

Can you answer this question…

I’ve just been asked a question on mass loading of images into the database (Optimizing BLOBs for SQL*Loader) and to be perfectly honest I’m not sure what the best solution is.

I’ve worked on a number of projects where images are stored in the database, but in all cases the images have been loaded on a one-by-one basis as they arrived. Apart from exports and imports between schemas I can’t remember ever doing mass loads of images into the database.

Anyone got any suggestions, preferably based on experience of this?



Ong Bak

I watched a martial arts movie called Ong Bak last night that stared a guy called Tony Jaa (Panom Yeerum). The film was low budget and the English dubbing was terrible, but the fighting sequences were amazing.

This guy is a serious talent. He’s a Muay Thai guy, so there is plenty of shin kicks, shin blocks elbow strikes, but there is also plenty of flashy movie stuff. It’s especially amazing when you consider that the fighting scenes are done without CGI or wire-work.

I always go on about how Jet Li is the most complete martial artist I’ve seen, but I think Tony Jaa can do some stuff that would make Jet Li weep! If he’s managed and promoted correctly I think he could eclipse Bruce Lee! Did I really say that?



First night in a tent…

Last night was the first night I’ve ever spent in a tent. I’ve never really liked the thought of it so I’ve never tried it before, but I was invited to friend’s 40th birthday party and a night in a tent was kinda mandatory.

I woke up early and was packing away my tent as some of my friends were going to bed. I get the feeling I’ll remember more about the evening than several of them 🙂

The verdict? I enjoyed putting the tent up and taking it down, even though it was raining and windy on both occasions. As for the sleeping thing, I didn’t dislike the experience, but it’s not something I’m desperate to repeat either. I guess the circumstances will dictate whether I ever venture into a tent again…

I’m off on holiday tomorrow, strictly no tents, so I guess things will be a little quiet unless I stumble on an internet cafe.



Life before these features existed…

Some Oracle features are so useful you quickly get to a point where you can’t remember what life was like before them. Three that spring to mind today are the DBA_SCHEDULER_JOB_RUN_DETAILS view, the DBMS_MONITOR package and the DBMS_METADATA package.

When using the new 10g scheduler, the DBA_SCHEDULER_JOB_RUN_DETAILS view provides a history of previous job runs. It’s cool to query this and see how the run duration of particular jobs varies during the course of the day, or between days, allowing you to do trend analysis over time. It’s not rocket science to store this information in a table yourself, but it’s nice that it happens straight out of the box.

The DBMS_MONITOR package is part of the end-to-end tracing available in 10g. It allows you to initiate tracing for one or more sessions, where the sessions are identified using combinations of criteria including: service, module, action, client_identifier or just the plain old sid. The really cool thing about this is that it doesn’t matter how many sessions match the criteria, or which RAC nodes they are running on, they all get traced. You can then consolidate all the trace files into a single file using the trcsess utility so you can use TKPROF as normal. This is way cool!

The DBMS_METADATA package was introduced in Oracle9i to allow you to extract DDL or XML definitions of database objects. Most tools like PL/SQL Developer and TOAD allow you to do this easily, but from the server it was always a pain. I used to spend ages writing and maintaining scripts to do this, and invariably they didn’t work for some features. This package threw all that out of the window. Today a friend asked me how he could dump out a schema creation script and I was in the middle of explaining about DBMS_METADATA when I remembered he works on 8i. Bummer…

I use these things all the time so the thought of moving back to 8i or 9i fills me with dread!

Someone mentioned recently that Oracle 11(xyz) is scheduled for release in November next year. That sounds a little early, but if it’s true I wonder how long it will be before I’m saying, “I’d rather quit than work with Oracle 10g!” 🙂



An encounter with the law…

Last night I finished Karate and went out to meet some friends at a bar. I parked my car in the bar’s car park and spent the rest of the evening there. When it came time to leave we decided to go back to one guy’s house just round the corner, literally 200 meters away. I didn’t want to leave my car in the car park at closing time so I drove it out of the car park, turned left on to the main road and instantly turned right at some traffic lights and that’s when I noticed a police car behind me with it’s lights flashing. I pulled up, right outside my mates house, got out of the car and walked towards the police car (we’re allowed to do that in the UK 🙂 , at which point a Woman Police Constable (WPC) got out of the car and this is the conversation I had:

Me: Hi dude! (not the best opening line on reflection…)
WPC: Dude?
Me: Is it OK to park on these double yellow lines while we do this?
WPC: That’s OK for now. What’s your name?
Me: Tim Hall.
WPC: What?
Me: Tim Hall.

She wrote down my name and the registration of the car.

WPC: Are you the registered owner of this car?
Me: Yes.
WPC: Do you know why I’ve pulled you over?
Me: I don’t have a clue, but I’m guessing you’re going to tell me that I wasn’t allowed to turn right at that junction.
WPC: That’s right!
Me: If that’s the case why isn’t there a sign?
WPC: There is, do you want to see it?
Me: Yes. It’s not that I don’t believe you, but I can’t believe I missed it if it was there.

We walk back to the junction, passing my friends who burst out laughing when they see I’ve been pulled over by the police.

WPC: See that sign?
Me: OK. Your right. Fair enough.

We walk back to the car. My friends are standing on the other side of the road watching the events, all still laughing and obviously drunk.

WPC: Have you had a drink tonight?
Me: No.
WPC: Not one?
Me: No. I don’t drink. Do you want to test me?
WPC: No, that’s OK.
Mat (a friend): Do you want to come inside for a cup of tea love?
WPC: Do you live round here?
Me: Yes, I live at …
WPC: I guess you normally turn left at that junction then.
Me: Yes.
WPC: Well, you did an illegal right turn and you drove through a read light!
Me: Wait a minute, there was no way I went through a red light.
WPC: OK, it just turned amber, but amber means stop unless it’s dangerous to do so.
Me: Fair play, but give me a break on that one.

A male officer gets out of the car.

Me: Hi dude! (Not a great line the second time round either!)
MPC: Smiles, but says nothing.
WPC: OK, be careful in future and have a good night.
Me: Aren’t you going to give me a ticket for the right turn?
WPC: No.
Me: Nice one. Cheers. Have a good one.

Both police officers get back in the car and drive off, I wave as they go by and pull my car into my friends drive. When I get in the house nobody can believe I didn’t get a ticket, or at least a caution!

Am I the luckiest guy alive or what?

I taught a couple of Yoga classes today and recounted the story at the end of the lessons because my reaction to the situation interested me. I’ve been driving cars for about 19 years and in that time I’ve never had a parking ticket, speeding fine or any sort of legal issue along those lines. When the police car pulled me over I wasn’t sure how I should feel about the situation. I knew I didn’t want a ticket, but I also knew there was nothing I could do to stop it, so I just thought, “relax and go with the flow!”, which I did. The whole encounter was very pleasant. Although I knew I didn’t have any control over the situation, the lack of control didn’t phase me. Maybe the calm nature of the encounter helped me avoid the ticket, maybe they just had “bigger fish to fry” on a Friday night at closing time.

One of the ladies at Yoga has recently had stomach cancer and today was here first session back since an extensive operation to remove it. At the end of the lesson she came to me and said, “Your right. Sometimes you just have to admit to yourself that you can’t control everything that happens in your life. Once you understand that, things are much easier to cope with.”

A valuable lesson. I hope I remember it next time something happens to me.