Migration of Oracle 7.3.4 database under HPUX 10.2 to Oracle 8.0.6 database.

[Dave Chamberlin]

I would suggest two things - first try to identify the performance bottleneck using top or glance, etc. GlancePlus (gpm) is especially useful for this. How much RAM do you have? If there is free memory and the system crawls, you may have an IO problem. If you are doing a lot of swapping, you may need more RAM. You should also use SAM to look at your kernel parameters - there will be differences between 8.x and 7.x. Search the forums for "kernel" should lead you to posts covering that topic.
Second thing - are there a lot of batch jobs running in the database? Going to a box with more CPUs is not a guarantee that the database will respond better to users. In fact batch jobs may be running so well, that they are causing a IO bottleneck, that will make the system slower than it was before.

[Dave Chamberlin]

oops - sorry about the multiple replies - the phrase I (slash) O caused an error in posting my reply, which I retried a several times before suspecting that the slash might get interpreted by the mailer.

[Rosalyn Bush]

Dave:
Thanks for your suggestions. I will forward them on to our Unix guys and DBA's.
We'll see what they say.

[Steve Slade]

Rosalyn,

The other angle to take on this problem is at the database level - which I guess is probably already covered by your DBAs - you mention benchmarking.

Sort of things I would suggest are :

1. Confirm you init.ora parameters - have you accidentally turned on features that you may not need. (e.g timed statistics, mts)

2. Have you run utlbstat utlestat reports to see if there any database configuration issues.

3. Have you looked at v$waitstat, during the load process to see any contention - this should not be introduced by going from 7 to 8.

4. Have you changed your optimiser mode - rule based to cost based - but with no DB stats?

5. Have you changed the structure of any of your tables to partitioned, and maybe the indexes need addressing.

These are all general things, which probably your DBA's have covered. There other things that could be done at the Oracle level, however, more information needs to be given with regards to the database, and how it is accessed by the application.

[Rosalyn Bush]

Steve:
I think that our DBA's have done 1 and 2 in your suggestions. Not sure about 3,4,5. I will pass this on to them to consider. We have to be careful with some of the settings as the application requires certain ones. The indexes we reorg weekly, and I don't believe we can change the structure of the databases. We have exhausted a lot of suggestions from HP, Oracle, and the Vendor for our application. We are running benchmarks now and plan to do a test split of application and database on our production box and the the super server on which the database will reside.
Thanks again for your help.
Rosalyn