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?
Probably because we didn’t!
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 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)
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.
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
In this case, it did the job.
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 …
TEST! TEST! TEST!
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.
I don’t think that is an inappropriate solution, and indeed it may give you clues as to what is going on, since if I remember correctly optimiser_features_enable is equivalent to setting a number of hidden parameters to their 817 defaults instead of the 9i defaults.
If you are doing system stats then I can’t see any reason to collect them at any time other than under load – though probably only once unless you modify the hardware.
But overall saying to Oracle behave like the previous release as much as possible until we get this sorted is much better than setting x, y or z parameter to an abnormal value until you hit a combination that makes a difference. Though it sounds like the same business reasons that meant you didn’t test may well mean that the workaround becomes permanent, which may be ok provided the test first unless you want surprises lesson isn’t learned.
Hi Niall,
I don’t think that is an inappropriate solution,
>> For where we were at the time, it was a great solution, but then we shouldn’t have been there in the first place.
and indeed it may give you clues as to what is going on, since if I remember correctly optimiser_features_enable is equivalent to setting a number of hidden parameters to their 817 defaults instead of the 9i defaults.
>> Yes and I think there are two directions that we’ll be crawling over from Monday until this is resolved. First, what parameters/features are different that could be relevant here. In Jonathan’s CBO book there’s a very useful table of the differences. There are many in between 8i and 9i and a few between 9i and 10g. For example _complex_view_merging is false in 8i and true in 9i. Given the number of views this job is using, that would be a place to start looking. But that’s only one example.
If you are doing system stats then I can’t see any reason to collect them at any time other than under load – though probably only once unless you modify the hardware.
>> The problem here is ‘standardisation’. The system stats job is run regularly at the same time on all servers which is obviously inappropriate if you understand system stats a little. I suspect this is not the only site where system stats are poorly understood, though.
But overall saying to Oracle behave like the previous release as much as possible until we get this sorted is much better than setting x, y or z parameter to an abnormal value until you hit a combination that makes a difference.
>> And that was the reason I suggested that, because ‘mucking around’ with parameters was exactly what was happening when the real change had been the upgrade, so let’s undo those bits of it that we can in a seperate environment and we can run the job there. Far from ideal, but there was a lot of time pressure here – there were financial issues if it didn’t run.
Though it sounds like the same business reasons that meant you didn’t test may well mean that the workaround becomes permanent, which may be ok provided the test first unless you want surprises lesson isn’t learned.
>> Not a snowball in hell’s chance, I reckon (but maybe I’m an optimist). If there’s something that focuses a business’ attention on the need for testing, it’s hitting problems 😉
Cheers
Ooops, I meant to say that the second area we’ll be working on is getting the right system stats populated.
Hi,
I am Amit. I am interested to know about the compatibility of Oracle 10g on a Pentium III machine. I got to know that its recommended requirement is PIV, but it can also be used on PIII. Can you tell me the problems we will face if we install 10g on a PIII?
Also, if Oracle 9i has better performance than 10g on a PIII machine. Please help.
Amit,
I really have no idea. Perhaps you could post this question on one of the many online Oracle forums?
Cheers,
Doug