0 Replies Latest reply on Jun 19, 2008 5:22 AM by Lukas Vopelka

    Pool managing

    Lukas Vopelka Newbie

      I have this problem. We have distributed application with thousands users. When we have some fatal problem with application or problems with connection then we'll see many selects : "SELECT 'X' FROM DUAL" this is probably caused by OracleValidConnectionChecker class. I search the code and have found no cycle which could cause so many select calling. Maybe problem is that all users are at the same time calling the select that I mentioned above? Then we loose all the connections and have no connections available! Is problem in application or could we solve the problem with better pool managing. This is ours oracle-ds.xml file

      <?xml version="1.0" encoding="UTF-8"?>

      <local-tx-datasource>
      <jndi-name>OKnouzeDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@hndb:1521:ns01</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>OKHNAPP</user-name>
      OKHNAPP
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <blocking-timeout-millis>10000</blocking-timeout-millis> <!-- doba na cekani getConnection -->
      <idle-timeout-minutes>25</idle-timeout-minutes> <!-- doba necinnosti po ktere je connection odpojen z db -->
      <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <!-- Checks the Oracle error codes and messages for fatal errors -->
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <!-- sql to call when connection is created-->
      <new-connection-sql>
      BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = xczech';
      END;
      </new-connection-sql>
      <check-valid-connection-sql>select 1 from dual;</check-valid-connection-sql>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

      <type-mapping>Oracle9i</type-mapping>

      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>NskDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@ssp123:1521:ssp2</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>NSSEHRAV</user-name>
      Carcassonne
      <min-pool-size>0</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <blocking-timeout-millis>10000</blocking-timeout-millis> <!-- doba na cekani getConnection -->
      <idle-timeout-minutes>25</idle-timeout-minutes> <!-- doba necinnosti po ktere je connection odpojen z db -->
      <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <!-- Checks the Oracle error codes and messages for fatal errors -->
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <!-- sql to call when connection is created-->
      <new-connection-sql>
      BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = xczech';
      END;
      </new-connection-sql>
      <check-valid-connection-sql>select 1 from dual;</check-valid-connection-sql>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

      <type-mapping>Oracle9i</type-mapping>

      </local-tx-datasource>


      Please give me some post or advice how to solve this problem !!! Thanks