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

[Rosalyn Bush]

We are having trouble migrating from 7.3.4 to 8.0.6. The application running does fine under 7.3.4 with respect to performance. As soon as we go to 8.0.6 we have performance issues in loading data to the database. Anyone having problems with this? If so, what did you do?

[Steve Slade]

Rosalyn,

We have upgrade from 7.3.4 to Oracel 8.0.5.2.2, under 10.20 and have found no problems. Can you give some more information with regards to what you are experiencing? I am assuming that you are refering to SQL_LOADER in your initial statement.

[CHRIS_ANORUO]

You should have a minimum of 1Gb RAM, 3 processors (PA 8200 CPU), SWAPSPACE , above 2.5Gb. Kernel parameters retuned to accommodate the upgrade. See attached documents.

[Rosalyn Bush]

Steve:
We are not upgrading to Oracle 8.0.5, but going to 8.0.6. This is on a K-class machine by HP. We currently have 8.0.6 running on a smaller K-class machine with only 2 cpu's compared to the larger one with 6 cpu's. We get slow response on the smaller K-class machine due to memory constraints. But on the larger one, which we expected to see some improvements, we got none. We have also tried splitting the database off onto another even larger machine (N-class), and we still have the performance problems. Thanks for your offer to help.

[Rosalyn Bush]

Chris:
I forwarded your attached notes to our DBA's. I believe for the most part that they have reviewed these things....I think that somewhere we have a pointer that is pointing to the wrong Oracle Home directory or something like that that may be causing the access to the database problems. They just bought Quest monitoring tools, but I don't know how helpful that has been. Do you have a suggestion for some monitoring tools that may give us better insight as to the performance degradation? Thanks for your help.

[Rosalyn Bush]

Steve:
I wasn't referring to SQL_Loader, in my ignorance of these things, it may be using it in the application. I am guessing that the database access is having trouble because it uses a version of SQLPLUS to access it. As far as technical knowledge of these things, I have none...our DBA's are struggling to find out why we are having these problems. We have taken benchmarks on our smaller machine on 7.3.4 and 8.0.6 and very little difference shows up in our load to the database. However, when we did it on the larger machine, 7.3.4 zips right along, and 8.0.6 loads at a snails pace. We think it may be pointing to the wrong version of Oracle or SQL.
Thanks for your help.

[Alan Riggs]

We just completed an upgrade to 8.0.6 in our development environment and will be upgrading production this weekend.

We have not seen any performance degredation to date (K580 6CPU systems). I will pass this on to our DBA, though, and post again if we see any similar issues.

[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 I/O bottleneck, that will make the system slower than it was before.

[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 I/O bottleneck, that will make the system slower than it was before.

[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 I/O bottleneck, that will make the system slower than it was before.

[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