oracle / tomcat performance problem

[Mark Durant]

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

 

[Andrew Thompson]

Yesterday, though, I "upgraded" to
WinXP SP2 (I know ... I know), and while I'm not positive that it's
the culprit ...
... 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. ....
So I'm not entirely sure where to go from here.

I do not think SP2 has anything to do with this.

The quickest way to rule it out, is to intall a
browser like Firefox and test it with that.

If FF is as slow (or in the same ball-park) look elsewhere.

Just to check two things, though.

Is it pure HTML being returned to the client?

Is there any Javascript, or other applets or plug-ins
within the page?

[ BTW - X-Post retained for the moment, but please
consider directing follow-ups to one of the 3 groups. ]

--
Andrew Thompson
http://www.PhySci.org/codes/ Web & IT Help
http://www.PhySci.org/ Open-source software suite
http://www.1point1C.org/ Science & Technology
http://www.lensescapes.com/ Images that escape the mundane

 

[Sudsy]

Mark Durant wrote:

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.

<snip>

That would lead me to believe that there's some funny business happening
"under the covers" with the DataSource used by Tomcat. It might be some-
thing as simple as the ConnectionPoolDataSource believing that the
connections are not open and constantly repopulating the pool. This
would then tax the RDBMS engine, possibly causing the performance impact
you see when running queries from sqlplus if Tomcat is running.
I'd start with checking the Tomcat logs for database activity.
I run 8i with Tomcat on a Linux platform, FWIW.

 

[Mark Durant]

Hi Andrew,
Thanks for the reply. Feedback inserted below....

I do not think SP2 has anything to do with this.

My main reason for thinking so was that there are some noted problems
with the Windows Firewall being switched on and blocking some ports that
Oracle uses. I have cleared oracle.exe and tnslsnr.exe in that regard,
btw.

The quickest way to rule it out, is to intall a
browser like Firefox and test it with that.

If FF is as slow (or in the same ball-park) look elsewhere.

I've tested in IE and Firefox ... again, though, I only pointed at SP2
b/c of the firewall issues. I was hoping that someone might have
stumbled upon something blocking communication between Oracle and Tomcat
or somesuch....

Just to check two things, though.

Is it pure HTML being returned to the client?

Is there any Javascript, or other applets or plug-ins
within the page?

It's pure HTML.

[ BTW - X-Post retained for the moment, but please
consider directing follow-ups to one of the 3 groups. ]

I cross-posted b/c of users from each of these three groups possibly
encountering the interaction problem. I'll be glad to direct only to
one if the problem starts resolving itself to one ... or if no one else
replies.

Thanks!
Mark

 

[Andrew Thompson]

[ BTW - X-Post retained for the moment, but please
consider directing follow-ups to one of the 3 groups. ]

I cross-posted b/c of users from each of these three groups possibly
encountering the interaction problem. I'll be glad to direct only to
one if the problem starts resolving itself to one ... or if no one else
replies.

Cool. Normally I 'rant and rail' at people who cross-post,
but I think it is justified in this instance.

Well. Given your page is pure HTML and using FF did not
reveal anything interesting, I am out of ideas myself.

I suggest you look into Sudsy's suggestion.. he's one of
the server-side guru's that I turn to in cases like this.

Hope you find the solution.

--
Andrew Thompson
http://www.PhySci.org/codes/ Web & IT Help
http://www.PhySci.org/ Open-source software suite
http://www.1point1C.org/ Science & Technology
http://www.lensescapes.com/ Images that escape the mundane

 

[Mark Durant]

Mark Durant wrote:


<snip>

That would lead me to believe that there's some funny business happening
"under the covers" with the DataSource used by Tomcat. It might be some-
thing as simple as the ConnectionPoolDataSource believing that the
connections are not open and constantly repopulating the pool. This
would then tax the RDBMS engine, possibly causing the performance impact
you see when running queries from sqlplus if Tomcat is running.
I'd start with checking the Tomcat logs for database activity.
I run 8i with Tomcat on a Linux platform, FWIW.

Thanks, Sudsy! I'll check that out tomorrow, and will report back with
results....

Mark

 

[Mark Durant]

Thanks, Sudsy! I'll check that out tomorrow, and will report back with
results....

Mark

Hi folks,
It wound up taking longer than I expected to get back to this, and I'm
ashamed to report, after spending many hours zeroing in on it, that
the problem was entirely programmer error. I won't bore with the
details, but I'll offer apologies to WinXP SP2, Oracle, Tomcat, and
any other party(/ies) I slandered in the process of debugging.

Thanks for the suggestions regardless....

Mark