Hey DBA, fix it, but don’t touch it!

Martin Berger posted an interesting tweet the other day.

“what’s the expectation a DBA should do when “something is slow” – but HW and SW is ok and DBA is not responsible (and must not manipulate) schema or statement?”

The thread includes some suggestions, but I want to come at it from a different angle…

I have a lot of sympathy for this situation. In my current role I look after a lot of databases that sit behind 3rd party applications. In many cases, that means we can’t change the code. We can’t play around with the contents of the schema, for fear of losing support. Our hands are, to a greater or lesser extent, tied. This presents an interesting problem because you’re damned if you do, and damned if you don’t.

Thought 1

My attitude to databases behind 3rd party applications might surprise you. I try to stick closely to what the application vendor recommends, even if I know it to be stupid. Why?

• Support (1) : As soon as you do something that isn’t in the vendor’s recommendations they are going to blame that for the problem. They’ll ask you to switch it all back and test it, which is just a delaying tactic most of the time and really annoying.
• Support (2) : You want the system to be as familiar as possible to the support staff and any consultants they send on site.
• Support (3) : I would say go for the vendor’s preferred platform, even if it’s not your preferred platform. You want to be on the same platform as the majority of their customers. If most of their customers are using Oracle on Windows, I’m going to consider it. If most of their customers are on SQL Server, that’s what I want. Unless you’re a massive customer, you are going to be at the bottom of the food chain if you marginalise yourself. Having lived through the death of Oracle on Tru64 and HP-UX, I want to be on the “main platform” for the application thank you very much!
• Sometimes their crap application expects the DB to be crap. I had one case where the vendor’s approach to gathering stats was extremely poor. I revised it to bring it into this century and the application died. Their bad code needed bad stats to work.

At this point I expect someone to say, “But Tim, you’re supposed to be good at this stuff. I would have expected more from you!” What’s my response? Walk a mile in my shoes. If you have hours to obsess over one system to make it perfect, great for you, just don’t come and work here! 🙂

Thought 2

When you buy a product it should be fit for purchase. Part of that is the vendor should be able to give adequate guidance on getting the most out of their system. Also, when you purchase a system, you should be doing your due diligence. Having written this, I understand it is a complete fiction. Why?

• Everything works great on PowerPoint. Procurement seems to be swayed heavily by the quality of the presentations, not the quality of the products.
• I don’t think many people really know what they are letting themselves in for until they are so far down the line, backing out would be too embarrassing an option.
• Sometimes, the best product on the market is absolutely terrible. I have one in mind, which my colleagues will be able to guess, where we had a choice of two products, both of which were absolutely terrible, so we picked the least terrible. In a meeting with our IT director I said, you’ve heard the expression, “You can’t polish a turd, but you can roll it in glitter!” This product is the turd inside that glitter!

Thought 3

Even when it’s not a 3rd party app, you can’t always fix it easily. As an application grows it gets significantly harder to refactor pieces of it. Sure, you can change that little bit, but what are the knock-on effects? If you have good regression tests, great. If not, it can be a risky endeavour. I mentioned in another post it took about six months to find some of the performance issues brought about by an upgrade from 11.2.0.4 to 12.1.0.2. It was stuff that had been missed during the testing and only ran once a year. The bigger the application, the easier it is for something to hide.

Even if you can refactor, do you have the time and resources to do it? Applications aren’t static. Sure, there is pressure to fix performance problems, but there is also pressure to add new functionality. What’s best for one group of people may not be good for others.

Thought 4

Martin’s tweet exemplifies the misunderstanding most managers (not him) have about databases. There’s rarely a magic button you can press that fixes performance problems. The vast majority of the time it comes down to bad database design and/or bad SQL. When you can’t change either, you don’t have much choice other than to wait for the next application patch/upgrade from the vendor that might fix it, or throw hardware at it on the DB layer or the App layer, depending where the problem is. {Insert “Run it on Exadata” comment here!} I really don’t think a lot of people outside the database world understand this!

Oracle has a lot of goodies that can be used to mitigate terrible applications without having to touch them directly. Update: I’m not trying to make out all these features don’t come with their own set of issues too. 🙂

• SQL Plan Management
• Materialized Views
• Query Result Cache
• PL/SQL Function Result Cache
• SQL Repair Advisor
• Approximate Query Processing
• Adaptive Query Optimization
• Extended Statistics
• _FIX_CONTROL
• DBMS_ADVANCED_REWRITE
• SQL Translation Framework

But the real solution is to do proper database design, write good SQL, and write good applications on top of that. Performance is a development issue, and we are all developers now. Yes, even you DBAs. 🙂

Anyway, just some thoughts on the situation Martin found himself in, and I find myself in all the time. 🙂

Cheers

Tim… (Chief Presser of Magic Buttons)

Author: Tim...

DBA, Developer, Author, Trainer. View all posts by Tim...