Re: Connection handling with jdbc.oracle.thin

From: Brad O'Hearne <cabodog_at_home.com>
Date: Thu, 25 Nov 1999 17:55:31 GMT
Message-ID: <nKe%3.30080$zd.333331@news1.alsv1.occa.home.com>

I am totally befuddled. The details: I am simply trying to open 10 connections in a single thread to an Oracle 8.05 database, for the purposes of building a connection pool. I am running the latest JDK1.1.8, which I downloaded and installed tonight. I am running the latest Oracle drivers (classes111.zip), which I also downloaded (specifically, I am using the thin driver). I am developing in IBM's Visual Age Professional 2.0. I am running on NT Server 4.0 Enterprise Edition. Both the program and Oracle are running on this same machine, hence there is no communication across the network whatsoever. All of my environment variables (CLASSPATH and PATH) are set correctly. I have a PentiumII 450 with 320MB memory. I have pared down my program so that there is nothing left but the attempt to make the connections (in fact, now I am not even assigning the connection to anything, I removed that too to try to speed it up -- no luck). I have run this from within the dev environment, and out of it from the command line, with virtually no difference in performance. Still, opening these connections is dog slow -- and from the responses I have gotten from others, they can perform this task in at least half the time. Here is my code below:

public static void main (String args []) throws SQLException {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); long l;
for (int i=1; i <= 10; i++)
{
  l = System.currentTimeMillis();
  DriverManager.getConnection("jdbc:oracle:thin:@MACHINE1:1521:DB1", "username", "password");
  /* yes I am 100% positive the above line does open a connection */   System.out.println(System.currentTimeMillis() - l); }

}

Here is the output:

952
892
890
890
933
880
963
881
912
905

Nearly a second per connection that I want to open. 10 seconds just to run that code above? That just seems atrocious. Does anyone have any clue as to why these might be taking so long? Is there any specific Oracle nuance that affects connection time? Could anyone run this code above against your Oracle database so that I could get some idea of exactly HOW slow this is? Thanks!

Anyone? Bueller? Anyone? Thanks in advance.

BradO

"Steve Hall" <stephenhallANTISPAM_at_earthlink.net> wrote in message
news:aqim3ss49qisdgfj1qpd3dntlmjv8der54_at_4ax.com...

> On 22 Nov 1999 19:01:34 GMT, jorgland_at_sol.wohnheim.uni-ulm.de (Joerg
> <snip>
> .
> .
> .

>


> >There is no exception thrown and no error log. After some debugging


> >it seems that <statement>.executeQuery() never returns and that this


> >is not a query or RDBMS problem but a timeout on the network connection

> >to the oracle server. I read lots of documentation but I did not
> >find anything about such a timeout. Neither
<Connection>.setLoginTimeout()
> >nor <Statement>.setQueryTimeout() show any effect on this behaviour.
> >
> >Did anyone ever experience this problem and does anyone have a
> >solution for that? One could say 'create the connection in doGet()
> >and close it there', but I don't want to do this if there's any
> >possibility to avoid it.
> >
> >Thanks in advance,
> >Joerg

> Hello,

> I'm seeing something very similar in a connection pool that I've
> constructed. We recycle connections to the connection pool after
> use and this works very well, but we see that occasionally a
> connection will "hang" and the calling thread will block
> indefinitely. Our regular pool-cleanup routines don't work with
> this "hung" connection and we sometimes have to kill the whole
> JVM to clean up.

> I have had confirmation from others on Usenet that setLoginTimeout()
> is not implemented, so that doesn't help. The odd thing is that
> with Solaris 2.6 and JDK 1.1.6, 1.1.7, 1.1.8 or 1.2.1 passing
> a bad connect string, bad JDBC user id or bad JDBC password causes
> the connection attempt to hang ( against Oracle 8.0.5 ) indefinitely.
> Interestingly, in the course of developing a workaround to this
> awful behavior, I ran some test code in IBM's Visual Age for Java,
> V2.0 and discovered that every condition I could throw at the
> getConnection() call generated an exception like I expected.

> The foregoing leads me to believe that there may be something
> odd going on with the VM. Visual Age's worked perfectly, but
> I can't exactly run that on my server !

> I'm re-writing my connection pool software to "age" connections and
> use worker threads to actually perform the connect / close operations
> to avoid these problems. Sure seems like a lot of work to
> deal with a driver/VM environment that won't throw exceptions !

> Regards,

> Stephen H

> Received on Thu Nov 25 1999 - 11:55:31 CST