?
Solved

Pooled connection usage

Posted on 2003-03-18
17
Medium Priority
?
309 Views
Last Modified: 2012-08-14
I would like to know when to close pooled connection if my application is running all the time to update Oracle database?

I have a Java application that receives messages from the clients all the time.  Each message is a thread and each thread will open a db connection and update the Oracle database.  Since regular db connection is expensive.  I use Datasouce and pooled connection.  When each thread is completed, I close the connection.  I leave the pooled connection open.  This method causes the memory usage very high; worse than using regular connection (open/close connection).  Any suggestions about when to close pooled connection are appreciate.

Thanks,
cytello
0
Comment
Question by:cytello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 14

Expert Comment

by:kennethxu
ID: 8164082
when you close pooled connect, the connection is actually goes back to the pool. if you have statment and prepared statment open before close the connection, those statements will still be open. so try to close statments before close connection.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8164878
Which connection pool do you use?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8165455
ALWAYS Close in this order:

resultSet first,
then Statement (or PreparedStatement)
then Connection

ESPECIALLY with the Oracle thin drivers...

I left one ResultSet open with a big EJB app once, and it used to fall over randomly depending on how much usage that function got...

Now, I always close everything in that order in a finally block :-)
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:cytello
ID: 8169825
I uses OracleConnectionPoolDataSouce to open the connection.  Following are the code:

OracleConnectionPoolDataSource ocpds = new  OracleConnectionPoolDataSource();
ocpds.setDriverType("oci8");
ocpds.setServerName("dbserver");
ocpds.setNetworkProtocol("tcp");
ocpds.setDatabaseName("dbname");
ocpds.setPortNumber(1521);
ocpds.setUser("test");
ocpds.setPassword("test");
PooledConnection pc = ocpds.getPooledConnection();
con = pc.getConnection();

My app did close resultset first, Preparedstatement second, finally close connection.  I also found out it's needed to set connection variable to null otherwise it's still in the memory (garbage collection didn't work well).  My question is how frequent to close the connection since my app is running forever?
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8169888
no, database is not meant to be used that way! you shouldn't be creating datasource all the time.

check you server doc and create your database source at server level. then use jndi to lookup the datasource and then get connection.

BTW, which server are you using?
0
 

Author Comment

by:cytello
ID: 8169932
Sorry to mislead you.  I only create DataSouce and PooledConnection once.  I get the Connection, use it, then close it for every thread.  I never close the PooledConnection which causes the memory usage very high.

I use Oracle database server 8.1.7.3.2.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8170240
no, it's not right to directly use OracleConnectionPoolDataSource, it doesn't really implement pooling. it just used to create a connection that is suitable to be in a pool.
you need to use OracleConnectionCacheImpl
http://docs.jcu.edu.au/jdbc/oracle/jdbc/pool/OracleConnectionCacheImpl.html

set minimal and maximal. then use getConnection.

public class MyDataSource {
 private static DataSource ds = makeDataSource();

 private static DataSource makeDataSource() {
  OracleConnectionCacheImpl ocpds = new OracleConnectionCacheImpl();
  ocpds.setDriverType("oci8");
  ocpds.setServerName("dbserver");
  ocpds.setNetworkProtocol("tcp");
  ocpds.setDatabaseName("dbname");
  ocpds.setPortNumber(1521);
  ocpds.setUser("test");
  ocpds.setPassword("test");
  ocpds.setMinLimit(5);
  ocpds.setMaxLimit(20);
  return ocpds;
 }

 public static getDataSource() { return ds; }
}



....
con = MyDataSource.getConnection();
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8170246
sorry should be:
public static DataSource getDataSource() { return ds; }
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8170255
damn.
con = MyDataSource.getDataSource().getConnection();
0
 

Author Comment

by:cytello
ID: 8184597
kennethxu,
I have two questions:
(1)
I got the "cannot resolve symbol" while compiling MyDataSource.java.  Is this a CLASSPATH issue?

.\TP2DataSource.java:36: cannot resolve symbol
symbol  : variable ocpds
location: class MyDataSource
                return ocpds;
                       ^
(2) Method makeDataSource() creates ocpds which is an instance of OracleConnectionCacheImpl which has method getConnection().  Can we call .getConnection() from instance OracleConnectionCacheImpl rather than transfer to DataSource?
Thanks.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8185195
did you changed MyDataSource.java?

>> Can we call .getConnection() from instance OracleConnectionCacheImpl rather than transfer to DataSource?

yes, you can. but it is not a transfer, OracleConnectionCacheImpl is (implements) a DataSource.
0
 

Author Comment

by:cytello
ID: 8191147
I didn't change MyDataSource.java code.

How many db connections (sessions) is shown on the Oracle database server side if we establish the connection through OracleConnectionCacheImpl?  Since the .getConnection() is a logical connection rather than a physical connection, I guess we should only see one db connection on the db server.

Can I never close the physical connection since my application is going to run all the time?

Thanks for your help.
0
 

Author Comment

by:cytello
ID: 8192080
I didn't change MyDataSource.java code.

How many db connections (sessions) is shown on the Oracle database server side if we establish the connection through OracleConnectionCacheImpl?  Since the .getConnection() is a logical connection rather than a physical connection, I guess we should only see one db connection on the db server.

Can I never close the physical connection since my application is going to run all the time?

Thanks for your help.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8192785
>> I didn't change MyDataSource.java code.
then, why your error message if referring to TP2DataSource.java? you should always be using:
Connection con = MyDataSource.getDataSource().getConnection();
didn't you?

>> I guess we should only see one db connection on the db server.
if you see only ONE physical db connection, then what's the point of using connection pool?
you should see number of physical connections between minmal and maximal value depend of the load of you application.

>> Can I never close the physical connection since my application is going to run all the time?
yes, in fact, you should never close physical connection. simple close the logical connection and pool manager will handle the rest.
0
 

Author Comment

by:cytello
ID: 8196498
It's the typo in my previous message.  Following is the MyDataSource.java file.  Two qeustions:
(1) Do I need to import more class?
(2) Why I should always be using:
Connection con = MyDataSource.getDataSource().getConnection()?  Why not make the connection by using the instance of OracleConnectionCacheImpl?  I am confused.
 

import java.util.*;
import java.io.*;
import java.net.*;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;


public class MyDataSource
{
  private static OracleDataSource ds = makeDataSource();
  private static OracleDataSource makeDataSource()
  {
    try
    {
      OracleConnectionCacheImpl ocpds = new    OracleConnectionCacheImpl();
      ocpds.setDriverType("oci8");
      ocpds.setServerName("test");
      ocpds.setNetworkProtocol("tcp");
      ocpds.setDatabaseName("test");
      ocpds.setPortNumber(1521);
      ocpds.setUser("test");
      ocpds.setPassword("test");
      ocpds.setMinLimit(5);
      ocpds.setMaxLimit(10);
    }
    catch (Exception e)
    {
      System.out.print("fail to create ocpds, err: " + e.getMessage());
      ocpds = null;
    }
    return ocpds;
  }
     
  public static DataSource getDataSource()
  {
    return ds;
  }
}
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 400 total points
ID: 8196811
>> Why not make the connection by using the instance of OracleConnectionCacheImpl?
what i mean was:

if you use OracleConnectionCacheImpl in everywhere of your code, you application is completely tied to oracle (or more acurately, a particular version of oracle jdbc driver).

if you use MyDataSource in other place of you code, all you need to change is the MyDataSource class when you migrate to another database.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8196822
and you need to move defination out of try block, so the return statement can access it.

change

   try
   {
     OracleConnectionCacheImpl ocpds = new    OracleConnectionCacheImpl();


to

   OracleConnectionCacheImpl ocpds = null;
   try
   {
     ocpds = new OracleConnectionCacheImpl();
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 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