0 Replies Latest reply on Jun 1, 2007 4:49 PM by Justin Rosenberg

    Oracle Datasource Slow (Max CPU) while JDBC super-fast

    Justin Rosenberg Newbie

      If I create a direct connection to Oracle using JDBC I can execute a simple query that pulls back about 1,400 records in 100-200 ms. If I used a JBOSS defined data source, the query takes 23 SECONDS!

      I'm running:
      JBoss 4.02
      Oracle 10gR2 (10.2.0.2.0) on Linux
      JDK 1.5
      WinXP


      If I execute the following code, it is very fast.

      Class.forName("oracle.jdbc.driver.OracleDriver");
      conn = DriverManager.getConnection("jdbc:oracle:thin:@linoradev1.crlcorp.com:1521:CRLDEV", "stsetup", "stsetup");

      Statement smt = conn.createStatement();
      ResultSet rs = smt.executeQuery("SELECT STUDY.STUDY_PKID AS ID, STUDY.STUDY_CODE AS CODE, STUDY.STUDY_NAME AS STUDY_NAME, SPONSOR.SPONSOR_NAME AS SPONSOR_NAME FROM STUDY, SPONSOR where STUDY.SPONSOR_PKID = SPONSOR.SPONSOR_PKID");

      while (rs.next()) {
      int id = rs.getInt("ID");
      String code = rs.getString("CODE");
      String studyName = rs.getString("STUDY_NAME");
      String sponsorName = rs.getString("SPONSOR_NAME");
      StudySummary study = new StudySummary();
      study.setID(id);
      study.setStudyCode(code);
      study.setStudyName(studyName);
      study.setSponsorName(sponsorName);
      studies.add(study);
      }

      If I replace the first two statements with:

      DataSource dataSource = (DataSource) new InitialContext().lookup("java:/crlcorp/tls/accessioning");
      conn = dataSource.getConnection();

      Then it takes 23 seconds.

      My -ds.xml file contains:

      <local-tx-datasource>
      <jndi-name>crlcorp/tls/accessioning</jndi-name>
      <connection-url>jdbc:oracle:thin:@linoradev1.crlcorp.com:1521:CRLDEV</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver
      </driver-class>
      </local-tx-datasource>



      I'm using Classes12.zip for the driver.

      From logging I can tell that the query executes quickly. The slow down occurs when reading the result set. I have also noticed that my CPU is maxed when I'm doing this. This problem has been replicated on a separate desktop.