Oracle getConnection slow

Question

In a Java project, I am using an ojdbc6 jar

    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.1.0</version>
        <scope>compile</scope>
    </dependenc>

The first time for a day I run, dataSource.getConnection() is fast. Second time is usually okay. The next few times take around 45 seconds. After that, it takes multiple minutes. Once I have the FIRST connection of a given application run, any new connections I get are very very fast. This delay is only getting the FIRST connection for a given run.

What is making getting my first connection so slow?

I am watching netstat and don't see any connection hanging after a successful run. Have tried several different connection pools (DBCP, C3PO) with no luck. Debugging through source code, the delay is 100% on the line of org.springframework.jdbc.datasource.DataSourceUtils:

Connection con = dataSource.getConnection();

Any ideas?

Edited For More Details

1) I am using a connection pool (DBCP or C3PO) which saves connections for future use. When I talk about getting a new connection, I mean while the first connection is in use.. I need to go to the DB and get a NEW connection. Of course I can return and get my same connection from the connection pool over and over again. But getting a second at the same time is also fast.

2) I do not know how many connections my DB lets me be logged in with. Any idea where this property is in oracle?

Solution 1

Was due to java using /dev/random instead of /dev/urandom to make a ssh connection with the database.... switching to /dev/urandom fixed this

Solution 2

2 questions.

First, after you get the first connection, what are you doing with it? Are you closing it correctly (INCLUDING all resources you opened with it, like Statement and ResultSet)?

Second, how many connections does your DB allow you for the credentials you logged in with?

The reason I ask these questions is that the time delays may be the amount of time it takes for the DB to clean up after you (because you may not have done it right) and free up a connection to give you. Much of this could just be timeouts before it forcefully releases resources.

Solution 3

Are you exceeding your max active connections in your connection pool and have to wait for your program to release old connections?

Comments

[lkdg]

Probably this article How to check the maximum number of allowed connections to an Oracle database? is interesting for you

[bwawok]

I run my programs right after another. It seems to be closing everything out properly, but not totally sure how to know.

[Ray]

If you use DBCP, you can print datasource.getNumActive() (you need to cast it to BasicDataSource first) before you try to get another connection again.

[Ryan]

It's worth mentioning that you can use the JVM argument -Djava.security.egd=file:/dev/./urandom to accomplish this. Notice the extra '.' in the argument. See stackoverflow.com/questions/137212/….