Maintaining Database for Vendor ApplicationsPosted: January 8, 2008
I made few new year resolutions about being a kinder, gentler person, so I’ll say this as nicely as possible:
Vendors sometimes make their DB related software very challenging to maintain.
I’ve spent the last three days trying to run some very routine maintenance on a schema that contains data for a specific vendor application. The original time estimate was 2 hours. I was about to publish the name of the software and the company and warn you to do you best to avoid it, but I ran into similar issues last month with different vendor software, and three month ago it was yet another one, so I won’t point fingers. If you have to maintain a database for vendor application, you are probably screwed.
Few challenges that are common to vendor applications: If you are a vendor – do your best to avoid these traps.
- No instructions on how to configure the application if you need to move the schema from one DB to another. This is a common task. Really.
- DB Maintenance instructions in the maintenance documentation that say “Disable cost based optimization” and “rebuild indexes frequently”. DBAs read this as “Clueless vendor”.
- No built in data purging policy or good way to clean old data from schema. We can’t let the application grow as much as it wants. Unfortunately, disk space is still expensive.
- Do a “select * from huge_table” on many trivial operations. Or more than once, on the same huge table, during the same trivial operation. You do realize that this makes the application slow?
- Scary bugs in DB maintenance utilities. I realize that bugs are hard to avoid, but when an aggregation utility fails to aggregate data, it should not delete the data it just failed to aggregate. Especially not by deleting tiny batches of the data and committing after each batch, making it impossible to use flashback for recovery and forcing a full fledged recovery process.
- Maintenance utilities that do very strange things. I started the vendor’s data purging utility this morning. 6 hours later it is still running (22% complete), and yet to run a single query. What the hell is it doing?
- Undocumented maintenance utilities – Maintenance instructions tell me to run “db reorganization tool” regularly. I have no clue what this will actually do on my DB, and as a responsible DBA I simply can’t run this on production.
As a general word of advice – Dev managers of DB intensive software would do well to read Chapter 10 of “Oracle Insights: Tales of the Oak Table”. Its called “Design Disasters” written by Jonathan Lewis. It contains lots of other traps to avoid that I did not mention here because Jonathan Lewis already explained them very well indeed.