Regular readers of this blog ( hello Mum) will have noticed that I’ve been a bit quiet lately.
Having tired of my life of leisure, I now have a proper job and have consequently been a little bit busy.
I have though, had time to reflect on the question of Oracle database upgrades and why they are never ever standard.
“We’ve decided to upgrade the database”. Those words are bound to strike equal amounts of anticipation and abject terror into the heart of any DBA…or at least, any DBA who has gone through an upgrade before.
To be fair, it’s not the upgrade itself that is going to cause the problem, it’s all of the misconceptions that come with it.
The CIO/IT Manager/Project Manager is almost bound at some point to come out with one of the following gems :
“We don’t need to test, the application code will still work in exactly the same way”
“We need to upgrade/move our servers/Application/Operating System anyway so we can do it all at the same time to minimize downtime.”
“According to the salesman Oracle can now make the tea, so it must be true.”
My personal favourite is from a person who shall remain nameless :
“We want to be fast-followers. X team is upgrading their database in July so we should be done by June”.
SQL – an Inconvenient Truth
The thing that makes a database upgrade different every time is simply the application(s) that run on top of it.
Whatever the technology – Java, PHP, Oracle Forms – ultimately communication between the application and the database will resolve itself down to some SQL running on the database.
SQL is a declarative language. Essentially, you tell the database what information you want and the database works out the most efficient way to get it.
In Oracle’s case, this is done via the medium of the good old Cost Based Optimizer ( CBO).
With every new release, Oracle make changes to the CBO. For most applications, the vast majority of the code therein will be unaffected, or may even run faster. However, there’s always that 1 or 2 percent of queries that will suddenly run like the three-legged donkey I always seem to get in the Office Grand National Sweep.
The trick is working out which queries will be thus affected and what the knock-on effect will be on performance across your application.
New “Features”
Unless you’re on a site that likes to be on the leading edge, it’s likely you’re going to be jumping a couple of versions ( e.g. from 10gR3 to 11gR2). If this is the case it’s a really good idea to head to Tahiti. No, you’re not off on holiday, you’re going to Oracle’s documentation site to check out the New Features documents for all of the releases between the one you’re on and the one you’re going to.
This should give you a pretty good idea of
- cool new stuff Oracle have introduced
- cool new stuff that Oracle have introduced but which now works properly
- stuff that’s likely to break.
Testing, testing, testing
No problem really, you simply need to run your comprehensive suite of automated functional tests. Load test, Regression test, Performance test and you’ll still be able to get to the pub at lunchtime.
What’s that ? You haven’t got a suite of automated tests ? Oh.
As the Corporate world seems to be ruled by individuals for whom patience is something that happens to other people, we’re probably not going to get the time to build a comprehensive suite of tests from scratch.
The only thing we can do is :
- identify the most heavily used queries now
- identify and batch jobs that will really cause problems if they overrun
- look for anything else that looks as if it could be problematic
One example that springs to mind for the last item I mentioned was itself a hangover from a previous upgrade.
Moving from 7.3 to 8.1.7 (with a new application front-end, servers and operating system, just for fun), the Support Team came across a number of queries which had run happily under the RBO in 7.3 but were playing up under 8.1.7.
Their solution was usually to simply apply a RULE hint.
Moving from 8.1.7 to 10gR2, which no longer supported the RBO, these queries were at least worthy of some love and attention.
By doing this, you can narrow your testing down from the several million lines of code in your application to a few key programs.
Obviously, this does increase the risk that you’ll miss something that will cause havoc once you go live, but some testing is a whole heap better than none whatsoever.
Production Like Environment
What you really do need is a test environment that is as close as possible to Production. What you’re likely to get is an old PC stuck under someone’s desk. OK, so that’s an exaggeration (most of the time). Look, if you want objectivity go to the BBC site !
In that case, load testing is going to be of limited value ( although depending on the application, you may still want to give it a go). What you can do however, is to use your test environment to take a baseline of the performance of your target programs on the “current” Oracle version, then install the new version and do a comparison.
It’s always worth setting up a test environment, if only so you can spot the code that no longer compiles under the new version. In 11g for example, there are some fun and games with ANSI SQL under certain conditions.
Big Bang
When some of the more thrusting managers talk about “Big Bang” releases, they tend to ignore the fact that a large explosion tends to result in a certain amount of debris and the need for a bit of tidying up afterward.
Upgrading the database represents a pretty fundemental change to your application. There will be glitches here and there.
Given this, it’s probably better if you don’t change the Operating System and/or servers at the same time as tracking down issues may become ever so slightly more complicated.
This is something that most IT Managers eventually understand if you explain it to them in a calm, gentle manner…possibly whilst holding a heavy blunt object.
Upgrade project – your options
Next time you hear those fateful words, think about…
- Changing the database on it’s own, separately from other infrastructure changes
- testing as much as possible
…or just look for a job where they’re already on the next version so you don’t have to go through it at all.
Share this:
The Anti-Kyte 