Re: JDBC over OCI -- degenerative performance & stability

This sounds like a problem that I have seen lots of comments on in the JavaSoft bug database.

If you re-use statements or resultsets you see this problem no matter what driver you are using. You need to close statements and resultsets - and some people have said they needed to close the connection periodically too - pretty much after each use.

I had this prolem in my code where I was reusing a prepared statement and clearing the parameters after each insert. After a number of inserts I would see it get slower and slower eating up CPU utilization until it would eventually crash - unless it finished first. By closing the statement each time and getting a new statement my performance stayed the same as at the start of the run and CPU utilization stayed around 6%.

Toby

Michael Grafton wrote in message <3904A142.7A7208D3_at_bigfoot.com>...
>Hey Folks --
>
>We have run into a degenerative condition running JDBC over Oracle OCI.
>At first, we were just getting worse & worse performance given the same
>number of connections in our pool. Now, the JVM has become very
>unstable and crashes with a mean time to failure of, oh, roughly 6
>hours.
>
>We have the following system setup on our load-balanced web tier:
>
>Solaris 2.7
>Apache 1.3.9
>JDK 1.1.6
>JServ 1.1b3
>Oracle Client 8.0.5
>
>The Oracle Server is running 8.1.5.
>
>We are running 2 servlet engines per web machine right now, with Jserv
>load-balancing across them. From our servlets we are making JDBC calls
>using the Oracle OCI JDBC driver. We have a fairly high number of
>transactions per second at a very consistent rate (roughly 6 a second
>per servlet engine, although we were doing more than that until the
>instability surfaced -- we had to turn some things off). The
>transactions are quite simple -- simple updates & inserts and 2-table
>joins (4-table joins at a lower frequency).
>
>The instability is showing up on the JVMs across several of our web
>boxes, even those that have just recently had the servlets installed.
>This is what confuses me the most; I have heard bad things about the
>oracle OCI driver, but why would a brand new installation show the same
>instability as one that has been running for weeks? That would seem to
>point to the DB server, but that box is quiet (relatively unloaded), and
>the DB instance has been bounced many times recently to no avail. What
>persistent server-side problem could possibly cause this instability in
>our client?
>
>I understand I am running a lot of older code, and am debating which
>upgrade is likely to help this problem (JDK1.1.7? JServ 1.1 final?
>Oracle client and/or server 8.1.6?). Trying the oracle thin driver or a
>third-party driver would probably prevent the core-dump, but I'm very
>keen on figuring out what component is to blame for the current
>problems...
>
>Thanks for any help, and would love to discuss further what I'm
>seeing...
>
>Michael A. Grafton
>Senior Software Engineer
>Live365.com
>***Check Out My Broadcasts***
>[music to code to]
>http://www.live365.com/cgi-bin/directory.cgi?autostart=djgrafton
>[kitchen-sink]
>http://www.live365.com/cgi-bin/directory.cgi?autostart=homie
>
Received on Thu Apr 27 2000 - 00:00:00 CDT