Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Pooled connection usage

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
cytello
Asked:
cytello
1 Solution
 
kennethxuCommented:
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
 
Venci75Commented:
Which connection pool do you use?
0
 
TimYatesCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cytelloAuthor Commented:
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
 
kennethxuCommented:
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
 
cytelloAuthor Commented:
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
 
kennethxuCommented:
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
 
kennethxuCommented:
sorry should be:
public static DataSource getDataSource() { return ds; }
0
 
kennethxuCommented:
damn.
con = MyDataSource.getDataSource().getConnection();
0
 
cytelloAuthor Commented:
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
 
kennethxuCommented:
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
 
cytelloAuthor Commented:
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
 
cytelloAuthor Commented:
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
 
kennethxuCommented:
>> 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
 
cytelloAuthor Commented:
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
 
kennethxuCommented:
>> 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
 
kennethxuCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now