oracle / tomcat performance problem

Hi folks,
Up until yesterday, I had Oracle 9i running happily with Tomcat 4.1, analyzing data from a 2 million+ set of records and reporting those results inside of some JSP pages. Yesterday, though, I "upgraded" to WinXP SP2 (I know ... I know), and while I'm not positive that it's the culprit (I was also out of the office last week, and something else may have snuck in that I'm not seeing), the queries that were generating my reports in seconds are now taking upwards of five minutes to run. I did try uninstalling SP2, but still had the problems. Since I don't have much choice but to keep the SP2 upgrade -- for reasons that I won't go into here -- I had to put it back.

If I run the queries in SQL*Plus with Tomcat still running, I get much better performance than my JSP pages, but it's still fairly slow. If I shut down Tomcat altogether, the queries fly like they're supposed to in SQL*Plus. As an example, a report that's taking five minutes via the JSP pages takes about 10-15 seconds in SQL*Plus with Tomcat running, and one second or less with Tomcat shut down.

So I'm not entirely sure where to go from here. In no particular order, I've also checked my indexes, made sure that the queries are following expected execution plans, tried some more basic queries via JSP pages (still extremely slow), switched between OCI8 and thin JDBC drivers, upgraded to Tomcat 5.0 (same problem), and watched both my CPU and RAM utilization during processing (rarely above 25% on either, and never above 75% CPU). Tomcat and Oracle logs don't seem to show anything unusual, either, though I'm not terribly adept at parsing through them and may be missing something.

Any suggestions are appreciated!

Thanks,
Mark Received on Tue Oct 05 2004 - 12:33:21 CDT