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. πŸ™‚

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.

8 thoughts on “Hey DBA, fix it, but don’t touch it!”

  1. Wait until you have to migrate JDE from AS400/DB2 to Oracle – yeah, they are soooo similar!
    And as soon as done and all tuned, your local cloud fanatic asks you how to move the lot -including 20 years of custom code! – to the PAAS cloud. Because it must be cheaper.
    Gartner says so and they know soooo much about all of IT because their prices are very high!!

  2. Noons: You always manage to bring it back to cloud. I think your hatred for cloud is a cover. You’re really a closet cloudy! πŸ™‚

  3. Tim,

    You touched a nerve with this. The sql profile (or “plan pinning”) can be a real PITA because AD think that is a “silver bullet” and a “real solution”. It always takes a lot of efforts,time and convincing to steer people in right direction and it is a wasted efforts almost always.

    Noons,

    How you always manage to read my mind…I will never know…. πŸ™‚

  4. Well, my desktop screen at work has little paper clouds glued all around it.
    And in the 11 years I’ve worked there, I have NEVER seen the main prod data centre.
    Cloudy enough for you? 😁

  5. Narendra : I’m not trying to make out any of these things are silver bullets, but when there is nothing else you can do they are worth considering. πŸ™‚ I put an update in the post in case people thought I was doing a sales pitch for them. πŸ™‚

  6. This is not restricted to just DBAs.

    And it becomes a political issue.

    I have fought many such battles and am now on a losing streak…

    Problem is that a lot of people are myopic… they don’t want to hear that this is a problem of their own making (where β€œown” might mean different things) so a) they already have preconceptions about the problem might be b) even if you could present all the information to show where the problem lies they already have a bias not to believe and c) everyone just wants to hear that someone else messed up on stats or forgot the fast=β€œtrue” parameter.

Leave a Reply

Your email address will not be published. Required fields are marked *