5 Replies Latest reply on Nov 6, 2008 4:04 AM by 668614

    Connection Pooling doesn't work.

    668614

      Good day. all

      I've tested OracleConnectionPoolDataSource and OracleDataSource.

      And found that there is no performance difference. And connections are not pooled to my mind.

      My sample fill table with records, and than read and update concurrently it in two threads.

      Bellow is a code provided.

      <strong>

      A piece of logs</strong>: each time new connection is created.

      ...

      connection= oracle.jdbc.driver.OracleConnection@1198891

      0. updatedNumber= 1

      connection.close()= oracle.jdbc.driver.OracleConnection@1198891

      connection= oracle.jdbc.driver.OracleConnection@17725c4

      connection.close()= oracle.jdbc.driver.OracleConnection@17725c4

      list.size()= 10

      0, name_update0, name_desc0, name_status0, 2008-11-04, 2008-11-04.

      1, a1, c1, b1, 2008-11-04, 2008-11-04.

      ...

      viewing the logs I see that every time new connection is created in both cases whether I use OracleConnectionPoolDataSource or OracleDataSource.

      <strong>

      Question:</strong> Can anybody help me to understand how to use Connnection pooling? And how to see with my own eyes that it works?



      <strong>DDL</strong>:

      to run the program one should create table:

      {color:#000080}CREATE TABLE TEST_RECORD1 (

      ID NUMBER(10) NOT NULL PRIMARY KEY,

      NAME VARCHAR2(255 BYTE) ,

      STATUS VARCHAR2(255 BYTE) ,

      DESCRIPTION VARCHAR2(255 BYTE),

      START_DATE TIMESTAMP ,

      END_DATE TIMESTAMP

      );{color}

      <strong>

      Java Code</strong>:

      package test.jdbc;



      import java.sql.Connection;

      import java.sql.PreparedStatement;

      import java.sql.ResultSet;

      import java.sql.ResultSetMetaData;

      import java.sql.SQLException;

      import java.sql.Statement;

      import java.util.ArrayList;

      import java.util.Date;

      import java.util.List;



      import javax.sql.ConnectionPoolDataSource;

      import javax.sql.DataSource;



      import oracle.jdbc.pool.OracleConnectionPoolDataSource;

      import oracle.jdbc.pool.OracleDataSource;



      public class TestConnectionPooling2 {



      static DataSource dataSource;



      /** number of iterations */

      static final long BASE_REPEAT_NUMBER = 5;

      static final long RECORDS_NUMBER = 10;



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



      //here you can change data source (pooled or no), but performance won''t change

      // setupDataSource( new OracleDataSource());

      setupDataSource(new OracleConnectionPoolDataSource());



      long startTime = System.currentTimeMillis();



      //clean table

      removeAllRecords("delete from test_record1");



      //insert records into table

      for (int i = 0; i &lt; RECORDS_NUMBER; i++) {

      insertRecord(i,

      "insert into TEST_RECORD1 (id,name,status, description, start_date, end_date) "

      + " values (?, ?, ?, ?, ?, ?)");

      }



      //setup thread which queries data from table

      Thread th1 = new Thread("thread 1") {

      public void run() {

      for (int i = 0; i &lt; BASE_REPEAT_NUMBER; i++) {

      retrieveRecords("select * from test_record1 order by id asc");

      Thread.yield();

      }

      }

      };



      //setup thread which updates data on table

      Thread th2 = new Thread("thread 2") {

      public void run() {

      for (int counter = 0; counter &lt; BASE_REPEAT_NUMBER; counter++) {

      for (int i = 0; i &lt; RECORDS_NUMBER; i++) {

      performSingleUpdate(counter, i,

      "update TEST_RECORD1 set name = ?, description=?, status=? where id=?");

      Thread.yield();

      }

      }

      }

      };



      th1.start();

      th2.start();



      //do not finish until threads work

      while (th1.isAlive() || th2.isAlive()) {

      Thread.yield();

      }



      long endTime = System.currentTimeMillis();



      System.out.println("Execution time:" + (endTime - startTime) + " ms.");



      }



      private static void performSingleUpdate(long counter, long i, String sql) {



      PreparedStatement stmt = null;



      Connection conn2 = getConnection();

      try {

      stmt = conn2.prepareStatement(sql);

      long temp = counter * 10 + i;

      stmt.setString(1, "name_update" + temp);

      stmt.setString(2, "name_desc" + temp);

      stmt.setString(3, "name_status" + temp);

      stmt.setLong(4, i);



      int updatedNumber = stmt.executeUpdate();

      System.out.println(counter + ". updatedNumber= " + updatedNumber);

      } catch (SQLException e) {

      e.printStackTrace();

      } finally {

      try {

      stmt.close();

      } catch (SQLException e) {

      e.printStackTrace();

      }

      if (conn2 != null) {

      closeConnection(conn2);

      }

      }

      }



      private static void removeAllRecords(String deleteAllSQL) {

      Statement stmt = null;

      Connection conn2 = getConnection();

      try {

      stmt = conn2.createStatement();

      int updatedNumber = stmt.executeUpdate(deleteAllSQL);

      System.out.println("Deleting all records. updatedNumber= " + updatedNumber);

      stmt.close();

      } catch (SQLException e) {

      e.printStackTrace();

      } finally {

      if (conn2 != null) {

      closeConnection(conn2);

      }

      }

      }



      public static void retrieveRecords(String sql) {

      List list = new ArrayList();

      Statement stmt = null;

      ResultSet resultset = null;



      int recordsCounter = 0;

      Connection connection = getConnection();

      try {



      stmt = connection.createStatement();

      resultset = stmt.executeQuery(sql);



      while (resultset.next()) {

      Record newRecord = new Record();

      recordsCounter++;

      newRecord.setId(resultset.getLong("id"));



      newRecord.setName(resultset.getString("name"));

      newRecord.setDesc(resultset.getString("description"));

      newRecord.setStatus(resultset.getString("status"));

      newRecord.setStartDate(resultset.getDate("start_Date"));

      newRecord.setEndDate(resultset.getDate("end_Date"));



      list.add(newRecord);

      }

      } catch (SQLException e) {

      e.printStackTrace();

      } finally {

      if (resultset != null)

      try {

      resultset.close();

      } catch (Exception e) {

      e.printStackTrace();

      }

      if (stmt != null)

      try {

      stmt.close();

      } catch (Exception e) {

      e.printStackTrace();

      }

      if (connection != null) {

      closeConnection(connection);

      }

      }



      StringBuilder sb2 = new StringBuilder("list.size()= " + list.size() + "\n");

      for (int i = 0; i &lt; list.size(); i++) {

      Record record = (Record)list.get(i);

      sb2.append(" " + record.toString() + "\n");

      }

      System.out.println("" + sb2);



      }



      public static void insertRecord(long counter, String sql) {



      PreparedStatement stmt = null;



      Connection conn2 = getConnection();

      try {

      stmt = conn2.prepareStatement(sql);



      stmt.setLong(1, counter);

      stmt.setString(2, "a" + counter);

      stmt.setString(3, "b" + counter);

      stmt.setString(4, "c" + counter);

      stmt.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));

      stmt.setTimestamp(6, new java.sql.Timestamp(System.currentTimeMillis()));

      int updatedNumber = stmt.executeUpdate();



      System.out.println(counter + ".insertNumber= " + updatedNumber);

      } catch (SQLException e) {

      e.printStackTrace();

      } finally {

      try {

      stmt.close();

      } catch (SQLException e) {

      e.printStackTrace();

      }

      if (conn2 != null) {

      closeConnection(conn2);

      }

      }

      }



      // Initial methods

      private static void setupDataSource(OracleDataSource oracleDataSource) throws SQLException {



      OracleDataSource ds = oracleDataSource;

      ds.setUser("akhlystov");

      ds.setPassword("xallex");

      ds.setURL("jdbc:oracle:thin:@localhost:1521:abc");

      dataSource = ds;



      }



      public static DataSource getDataSource() {

      return dataSource;

      }



      private static void closeConnection(Connection connection) {

      if (conn != null) {

      return;

      }

      System.out.println("connection.close()= " + connection + "\n");

      try {

      connection.close();

      } catch (SQLException e) {

      throw new RuntimeException(

      "application should not work when database can't close connection");

      }

      }



      static Connection conn;



      public static Connection getConnection() {



      /*

      * uncomment to test without any data source, single connection if ( conn ==

      * null){ try { conn = getDataSource().getConnection(); } catch

      * (SQLException e) { e.printStackTrace(); } } return conn;

      */



      try {

      Connection connection =null;

      if ( getDataSource() instanceof ConnectionPoolDataSource){

      connection = ((ConnectionPoolDataSource)getDataSource()).getPooledConnection().getConnection();

      }else{

      connection = getDataSource().getConnection();

      }

      System.out.println("connection= " + connection);

      return connection;

      } catch (SQLException e) {

      e.printStackTrace();

      throw new RuntimeException("application should not work when database is unavailable");

      }



      }



      // Record class



      static class Record {

      long id;

      String name;

      String desc;

      String status;

      Date startDate;

      Date endDate;



      public long getId() {

      return id;

      }



      public void setId(long id) {

      this.id = id;

      }



      public String getName() {

      return name;

      }



      public void setName(String name) {

      this.name = name;

      }



      public String getDesc() {

      return desc;

      }



      public void setDesc(String desc) {

      this.desc = desc;

      }



      public String getStatus() {

      return status;

      }



      public void setStatus(String status) {

      this.status = status;

      }



      public Date getStartDate() {

      return startDate;

      }



      public void setStartDate(Date startDate) {

      this.startDate = startDate;

      }



      public Date getEndDate() {

      return endDate;

      }



      public void setEndDate(Date endDate) {

      this.endDate = endDate;

      }



      public String toString() {

      return id + ", " + name + ", " + desc + ", " + status + ", " + startDate + ", " + endDate + ".";

      }

      }



      }

      Edited by: Alexandr Khlystov on 04.11.2008 20:14

        • 1. Re: Connection Pooling doesn't work.
          friscoki
          Have a look at http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/concache.htm#CDEGABI

          From the docs:

          "Turning Caching On

          An application turns the implicit connection cache on by calling OracleDataSource.setConnectionCachingEnabled(true). After implicit caching is turned on, the first connection request to the OracleDataSource class transparently creates a connection cache."
          1 person found this helpful
          • 2. Re: Connection Pooling doesn't work.
            668614
            1. Thanks, but this is about Oracle 10. I have Oracle 9. There is no such method OracleDataSource.setConnectionCachingEnabled(true).



            2. I can't get the difference between words caching & pooling ? I think it is the same. Am I right? Why I need caching, when I think pooling doing the same? I think pooling means - creating pool of connections ( not closing them ) and giving new connection only if there is no free connection at the pool, otherwise it gives already created connection from the pool.



            Thanks.



            Edited by: Alexandr Khlystov on 04.11.2008 19:57

            Edited by: Alexandr Khlystov on 04.11.2008 19:58
            • 3. Re: Connection Pooling doesn't work.
              friscoki
              The relevant documentation for the 9i JDBC drivers is here http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/connpoca.htm#1056354.

              In the 9i JDBC driver world,there was a distinction between a connection pool (pooled connection) and a connection cache.

              A connection pool attempts to eliminate the overhead and resource consumption of connecting and disconnecting from the database. If the connection is going to be reused then no point in closing the connection just to open it up again.

              What performance gain were you looking for? You would only see a performance gain if your application was constantly opening and closing connections, requiring all the resources to setup and tear down database communications. Once a connection is established there is no difference in performance of the execution of SQL.

              You can see this with
              import java.sql.Connection;
              import java.sql.ResultSet;
              import java.sql.SQLException;
              
              import oracle.jdbc.pool.OracleConnectionPoolDataSource;
              import oracle.jdbc.pool.OracleDataSource;
              import oracle.jdbc.pool.OraclePooledConnection;
              
              public class TestConnectionCache {
              
                public static void exec(Connection conn) throws SQLException {
                  ResultSet rs = conn.prepareStatement("select sys_context('USERENV','SESSIONID') from dual").executeQuery();
                  rs.next();
                  System.out.println("Session id: " + rs.getInt(1));
                  conn.close();
                }
                
                public static void main(String[] args) throws SQLException {
                  
                  OracleDataSource ods = new OracleConnectionPoolDataSource();
                  ods.setUser("...");
                  ods.setPassword("...");
                  
                  // Construct the jdbc url.
                  String host     = "...";
                  String port     = "...";
                  String instance = "...";
                  
                  String url =
                    "jdbc:oracle:thin:@//" + host + ":" + port + "/" + instance
                    ;
                  ods.setURL(url);
                
                  // No connection re-use.
                  exec(ods.getConnection());
                  exec(ods.getConnection());
                  
                  // Connection re-use. (Connection pooling)
                  OraclePooledConnection opc = (OraclePooledConnection)((OracleConnectionPoolDataSource)ods).getPooledConnection();
                  exec(opc.getConnection());
                  exec(opc.getConnection());
                }
              }
              Produces output similar to:
              Session id: 4110149
              Session id: 4110150
              Session id: 4110151
              Session id: 4110151
              Note when connection pooliing is used, the database session id is the same. Calling close() did not close the physical connection.

              A connection cache is a layer on top of pooled connections that manages the creation and destruction of connections. It usually has limits as to how many connection it should open and the max number of connections to allow. Then when the cache is asked for a connection, it can return one that is not in use, or possibly create a new one.

              The later versions of the JDBC driver greatly simplify managing your connections and here is the note from the docs:

              Note:
              The previous cache architecture, based on OracleConnectionCache and OracleConnectionCacheImpl, is deprecated. Oracle recommends that you take advantage of the new architecture, which is more powerful and offers better performance.


              Any reason why your are not using a more recent JDBC driver?
              • 4. Re: Connection Pooling doesn't work.
                668614
                First, thanks for your answer.

                About 1st question, I found that it is possible to use JDBC 10g driver even for Oracle 9i DBMS, and there connection caching is really usable. Previously I thought that to connect to Oracle 9i I need to use only the Oracle 9 JDBC dirver.


                You have enlighted me! I didn't find a place at oracle site and whole internet where to read how pooled DataSource should be used. Now I get the main Idea of pooling.

                Thanks again!
                • 5. Re: Connection Pooling doesn't work.
                  668614
                  I mean your answered on 2nd question too. Thanks for code sample