problems with moving from 8.1.7 to 9.2.0.4

We have been moving our web based applications to 9.2.0.4 on Linux with good end results except for one which has us stumped.

This application is a front end to a data warehouse while the others are primarily local data. It was originally under Oracle7 as was the warehouse. There have been numerous updates of both the application and the warehouse that we have been able to work out. Until now we were on solaris while the warehouse has been and is still under AIX.

The warehouse moved to 9.2.0.4 with no problems for this application so we didn't expect problems when we moved to 9.2.0.4. The application is all PL/SQL using the database toolkit. Because of applications using html db, we are using the 10g http server.

The problem is the following: if the application completes at all, it is too slow for our users. However, it has done various other things. One is to stay on the parameter page and after some time display "done" on the status line which is apparently caused by a time out on the part of Apache. Other times it runs so long that the token has expired and we get a message to that effect. At one point there was an attempt to run from the application server that we had been using for 8i and that gave an invalid cursor error and trying the current setup at that point gave the same error.

Stripping out the main query to run from SQL/Plus doesn't provide any clues as it runs faster than it does under 8i. The problem does seem to either be coming from this main query or the web display as a minimal version of this report - just passing parameters directly rather than getting all of the possible cross listings and not providing any of the extra things that the application has - behaves as the full application.

We tried to run an extended SQL trace and found that we could not get the trace turned on within the application (insufficent privileges although the user that the application runs as has no problem with the command directly in SQL/Plus) and turning it on from another session we were not sure where in the application we were. We did get that for this main query waiting on the warehouse was where the time was going and frequenly it was not bringing back any rows.

Has anyone encountered anything like this?

Any ideas where we can look for relavent information? Several of us have tried searching google and Metalink without turning up anything of help. Either we still can't specify the problem correctly or there is nothing to find. I'm inclined to believe it is the specification since we feel we don't have it narrowed down enough for a TAR.

Any help greatly appreciated.

                Pat

--
http://www.freelists.org/webpage/oracle-l