Maintaining Database for Vendor Applications

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.

  1. 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.
  2. DB Maintenance instructions in the maintenance documentation that say “Disable cost based optimization” and “rebuild indexes frequently”. DBAs read this as “Clueless vendor”.
  3. 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.
  4. 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?
  5. 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.
  6. 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?
  7. 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.

Advertisements

4 Comments on “Maintaining Database for Vendor Applications”

  1. Freek says:

    8. the end users must use the same oracle user to login. And this user must have the dba role.

    9. select the data in a certain order and then reorder the data in the application. If the end-user wants to change the order of the data, then query the db again (and re-order the data in the application again).

    real world examples I’m affraid 😦

  2. prodlife says:

    Application user that *must* have DBA role is a new low point 😦 I’ve never seen anything this bad.

    #9 is very familiar. I believe I made this mistake myself in my first year as a developer. Thankfully, I worked in a team with good code reviews.

  3. Chris says:

    some others I’ve come accross do not…

    10. Remove the swear words from comments in stored procedures, making your app run and attempting to wring decent performance out of it is sould destroying and the occasional really sarky comments in the code keeps us amused.

    11. Avoid any advanced features of the RDBMS as this will prevent database independance so avoid stored procedures, analytic functions, the cost based optimiser in fact anything that got added to the database after 7.3.4 is suspect and is probably just marketing fluff.

    12. Make sure your application cannot be installed alongside anything else, even if only storing 30mb of data make sure your documentation required a very specific version, patchset and parameters. Extra marks are awarded for requiring several underscore parameters to be set but best not to explain why.

    13. Use bind variables, memory is cheap and so are CPU’s, if the customer cannot afford 16 cpu’s and a 10GB library cache how are they going to afford your support fees?.

    🙂

  4. prodlife says:

    Chris,

    I completely agree on all points, but especially #10!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s