Re: JDBC over OCI -- degenerative performance & stability

Hey Toby,

Thanks for the response. My code always creates a new PreparedStatement and closes it every time. However, I don't close the ResultSets because I thought that by closing the Statement, it was implied that you closed the ResultSet!

I will try explicitly closing the ResultSet and see if this helps. Thanks!

Mike.

Toby Piper wrote:

> 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 Mon May 01 2000 - 00:00:00 CDT