A good resource for optimizing JDBC connections?

Posted on 2003-11-21
Last Modified: 2013-12-10

We have a fairly complex enterprise application running WebLogic 7.0sp2 with an Oracle 9i on Solaris backend.  We are trying to find the ideal DB_CONN_MIN and DB_CONN_MAX settings, but really using trial and error to get there, this is very time consuming...

Can anyone give some pointers to either resources or formulas that can be used to do this a bit more scientifically?

Thanks for any help!
Question by:bgodden
  • 2
  • 2

Expert Comment

ID: 9812440
First of all , read JDBC performance tips here :

You want to set max number of connections equal to the maximum number of concurrent client connections required DB access through your application.
However, you have to make sure that your DB has enough memory to handle this number of connections.

In ideal sitiation , you would have set Initial DB connections = Max DB connections, however it can eat all your database memory if your max connections number is too high
So, you should ask DBA, how much memory you have available for JDBC connections and how much memory each JDBC connection utilize.
Based on information from DBA, you can calcutate Max number of connections you can set and Initial number of connections.
For example, If DBA says, that it's OK to utilize 1Gb of DB memory all the time and you know that each of your JDBC connections using 10 Mb of DB memory, then you can set Max conn. = Initial conn. = 1Gb/10Mb = 100.

If DBA says that he want to keep DB memory size utilized by Weblogic up to 1 Gb, , but in avarage 300 Mb, then set max connections to 100 and Init connections to 30.

After, you've set these settings, try to monitor number of connections using ( monitoring tab of JDBC connection in weblogic console ) for several days to make sure that initial maximum number of connections are sufficient and you don't need to change them.

If you see that maximum number of connections is less than you require for your app, then you have to negotiate with DBA to allow you to use more DB resources.

Also, it 's very important to make sure that application code is properly handling connections and closing them.

Also, if  


Author Comment

ID: 9812991
Excellent, this is exactly the help I'm looking for!  So I'm going to ask you a couple more question if you don't mind, and I will up the points each time.  

I don't have access to the weblogic console right now...  When I do go int there for JDBC monitoring, which key values on that page will tell me how many connections are being required by the app?

Also, your answer got truncated at the end, can you finish that thought?  Thanks!  -Brian

Accepted Solution

egorelik earned 300 total points
ID: 9813629
I'd say that key data in JDBC connections monitoring table are:

Connections—Number of physical database connections from this instance of the connection pool that are currently in use.

Connections High—Highest number of active database connections in this instance of the connection pool since the connection pool was instantiated. Active connections are connections in use by an application.

If you see that Connections or Connections High are consistently close to maximum number of connections, then you probably need to increase max number of connections.

If JDBC pool will reach max number of connections then server most probably will run out of threads and die, so sometimes you can also go through that bad experience, before you understand what number of max connections you really need . ::)).

You can also keep your eye on :

Wait Seconds High—Highest number of seconds that an application waited for a connection from this instance of the connection pool since the connection pool was instantiated.

Waiters High—Highest number of application requests concurrently waiting for a connection from this instance of the connection pool.

If any of these numbers are consistently more than 0, then you have to improve DB performance or JDBC performance or may be application code.

I also wanted to say that don't forget to use test table in JDBC pool settings to verify connection to DB all the time.

You can also check "Test reserved connections" and "Test released connections" check boxes to make sure that there are no any connections hanging after client closed the sesion and that connections are not broken, however, it can also slow down JDBC performance a bit.

Eugene Gorelik.


Author Comment

ID: 9813749
Thanks Eugene, this is a big help!!

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question