I had an interesting day at work yesterday. We’ve run into major performance problems when upgrading a number of Production databases from 8i to 9i. How could that happen, when we’ve performed regression tests to ensure that the applications perform as well as or better than they did on 8i?<\/p>\n
Probably because we didn’t!<\/p>\n
In the technical world that most readers of this blog are likely to inhabit, that’s unforgiveable. In the business world that some of us also inhabit, there are many reasons why this might happen. Not why it should<\/span> happen, just why it does. In this particular case, it seems that the only production-sized environment that we could have tested in and the only people who could have helped with the testing were both employed on the business’ ‘hot’ project (more important than live business systems …. sigh). So we took the risk. (Well, I say ‘we’, but I was unaware of this until I got dragged in when the doo-dah hit the fan)<\/p>\n So now we were in trouble and what could we do? We started looking at query execution plans that used views, of views, of views … (you get the picture). Reading 100+ line execution plans under pressure isn’t enjoyable. We looked at session-modifiable parameters and login triggers. We discussed a downgrade but the problem with that was that the application suffering the problem was a monthly reporting extract from a critical production OLTP database into a lower-priority reporting database. (Although it was important enough that this was a big problem.) The critical OLTP stuff was running perfectly.<\/p>\n In situations like this, you’re already compromised and desperate so you have to think of creative solutions. In the end, we created a copy of the database by mounting the BCV volumes (mirrors, effectively) on another server and opened it with optimizer_features_enable = 8.1.7<\/p>\n In this case, it did the job.<\/p>\n Now we have to work out why this job is running more slowly in 9i and fix it properly. You know, like you might do during a test phase 😉 The whole situation is exasperated by the fact that we generate our System Statistics when the machine is deadly quiet. Result – no System Stats to speak of. Personally I think this might be key, but the only way to find out will be …<\/p>\n TEST! TEST! TEST!<\/p>\n Hopefully I’ve been clear that optimizer_features_enable wasn’t the best answer to this problem (did I mention pre-implementation testing?) but I was quite pleased we came up with a temporary fix for a very sticky problem.<\/p>\n","protected":false},"excerpt":{"rendered":"