10 Replies Latest reply: Nov 16, 2012 11:16 AM by user10828256 RSS

    weblogic connection pool alive connection timeout too soon issue

    user10828256
      We are having a problem with weblogic connection pool. Our web application is built on top of Spring Framework, deploy to Weblogic using the datasource to connect to Oracle. We are using a min 1 and max 15 connection pool setting on weblogic. But the problem we are facing is the pool doesn't seem to handle any connection as a pool. Every request issues a new connection to the Oracle server and closed right in a few seconds of connection. The new connection count could be easily up to over 100 within a 10 min.

      Is there a timeout setting to check on the alive connection? Could anyone please share your thoughts how to solve the issue? Your input is really appreciated.

      Spring framework (2.5.3), Weblogic (10.3), Oracle (11gR2)

      Here is the setting on our datasource:

      -----------------------
      Driver class: oracle.jdbc.OracleDriver

      Initial Capacity:30
      Maximum Capacity:30
      Capacity Increment:1
      Statement Cache Type: LRU
      Statement Cache Size:10

      Test Connections On Reserve: checked
      Test Frequency: 120
      Test Table Name: SQL SELECT 1 FROM DUAL
      Seconds to Trust an Idle Pool Connection:10
      Shrink Frequency:900
      Connection Creation Retry Frequency:0
      Login Delay:0
      Inactive Connection Timeout:0
      Maximum Waiting for Connection:2147483647
      Connection Reserve Timeout:10
      Statement Timeout: -1
      Ignore In-Use Connections: checked
      Pinned-To-Thread: unchecked
      Remove Infected Connections Enabled: checked
      ---------------------
      From the Monitoring tool, we observed the following statistics:

      Active Connections Average Count : 0
      Active Connections Current Count : 0
      Active Connections High Count : 1
      Connections Total Count :1523
      Current Capacity : 1
      Failed Reserve Request Count : 46
      Highest Num Available : 30
      Leaked Connection Count : 0
      Wait Seconds High Count :0
      Reserve Request Count : 1531

      Other parameters are all 0.

      --------------------------
      For the Spring setting:

      <bean id="ourdatasource" class="org.springframework.jndi.JndiObjectFactoryBean">
      <property name="jndiName"><value>weblogic.datasource</value></property>

      <!-- added the following for debugging issue if caused by cluster or not -->
      <property name="cache" value="false"/>
           <property name="lookupOnStartup" value="false"/>
      <property name="proxyInterface" value="javax.sql.DataSource"/>
      </bean>
        • 1. Re: weblogic connection pool alive connection timeout too soon issue
          Joe Weinstein-Oracle
          Try unchecking "Remove Infected Connections Enabled" and let me know...
          • 2. Re: weblogic connection pool alive connection timeout too soon issue
            user10828256
            Hi Joe, thanks for the input. It did not help. The same high connections (180) created on the Oracle side. Each of them only last less than 10 seconds.
            • 3. Re: weblogic connection pool alive connection timeout too soon issue
              Joe Weinstein-Oracle
              turn on jdbc logging and see what the server log shows about connections being created and/or failing tests etc.
              • 4. Re: weblogic connection pool alive connection timeout too soon issue
                user10828256
                Joe, thanks for the tips, I turned on the JDBC logging. Please see the following, obviously, this pool closed within one second, any clue why?

                ####<Nov 15, 2012 10:46:59 PM UTC> <Info> <Common> <servername> <server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1353019619067> <BEA-000628> <Created "1" resources for pool "xxxxx", out of which "1" are available and "0" are unavailable.>
                ####<Nov 15, 2012 10:47:00 PM UTC> <Debug> <JDBCSQL> <servername> <server5> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-025D004FC13B9F9EFDB1> <> <1353019620263> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@1d0] close()>
                ####<Nov 15, 2012 10:47:00 PM UTC> <Debug> <JDBCSQL> <servername> <server5> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-025D004FC13B9F9EFDB1> <> <1353019620263> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@1d0] close returns>
                ####<Nov 15, 2012 10:47:00 PM UTC> <Info> <JDBC> <servername> <server5> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <> <1353019620264> <BEA-001128> <Connection for pool "xxxxx" closed.>
                • 5. Re: weblogic connection pool alive connection timeout too soon issue
                  Joe Weinstein-Oracle
                  I am going to need to help you via an official Oracle support case.
                  • 6. Re: weblogic connection pool alive connection timeout too soon issue
                    user10828256
                    Every request from the web app initiates a connection to the oracle server, then close right away as following:

                    ####<Nov 15, 2012 11:32:12 PM UTC> <Debug> <JDBCConn> <servername> <Server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0035B3AE6C649F9EFDB1> <> <1353022332635> <BEA-000000> <JTS/JDBC Connect: url = jdbc:weblogic:jts:xxxxx, tx = Xid=BEA1-0035B3AE6C649F9EFDB1(151076941),Status=Active,numRepliesOwedMe=0,numRepliesOwedOthers=0,seconds since begin=2,seconds left=598,activeThread=Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads],XAServerResourceInfo[weblogic.jdbc.wrapper.JTSXAResourceImpl]=(ServerResourceInfo[weblogic.jdbc.wrapper.JTSXAResourceImpl]=(state=started,assigned=none),xar=weblogic.jdbc.wrapper.JTSXAResourceImpl@885f2ee,re-Registered = false),SCInfo[Server5]=(state=active),properties=({weblogic.jdbc=t3://....}),OwnerTransactionManager=ServerTM[ServerCoordinatorDescriptor=(CoordinatorURL=RiskServer5, XAResources={weblogic.jdbc.wrapper.JTSXAResourceImpl, Server5},NonXAResources={})],CoordinatorURL=RiskServer5), props = {EmulateTwoPhaseCommit=false, connectionPoolID=xxxxx, jdbcTxDataSource=true, LoggingLastResource=false, dataSourceName=xxxxx}>

                    ####<Nov 15, 2012 11:32:12 PM UTC> <Debug> <JDBCSQL> <servername> <Server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0035B3AE6C649F9EFDB1> <> <1353022332635> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@4e] getVendorConnection()>

                    ####<Nov 15, 2012 11:32:12 PM UTC> <Debug> <JDBCSQL> <servername> <Server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0035B3AE6C649F9EFDB1> <> <1353022332635> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@4e] getVendorConnection returns oracle.jdbc.driver.T4CConnection@84646a4>

                    ####<Nov 15, 2012 11:32:12 PM UTC> <Debug> <JDBCSQL> <servername> <Server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0035B3AE6C649F9EFDB1> <> <1353022332636> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@4e] close()>

                    ####<Nov 15, 2012 11:32:12 PM UTC> <Debug> <JDBCSQL> <servername> <Server5> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0035B3AE6C649F9EFDB1> <> <1353022332636> <BEA-000000> <[weblogic.jdbc.wrapper.JTSConnection_oracle_jdbc_driver_T4CConnection@4e] close returns>

                    Edited by: user10828256 on Nov 15, 2012 3:44 PM
                    • 7. Re: weblogic connection pool alive connection timeout too soon issue
                      user10828256
                      Hi Joe, what do you need to open an oracle support case? We have support agreement with Oracle for both Weblogic and Oracle. Thanks in advance.
                      • 8. Re: weblogic connection pool alive connection timeout too soon issue
                        user10828256
                        Thank you Joe, it's working now.
                        • 10. Re: weblogic connection pool alive connection timeout too soon issue
                          user10828256
                          I changed a few other things at the same time and it suddenly started to work. So I removed the combinations one by one and identified your initial suggestion was the minimum to keep this pool alive. Not sure why it did not work at the beginning, maybe some other parameters changed and prevented it from working correctly. Thank you very much Joe!

                          Edited by: user10828256 on Nov 16, 2012 9:16 AM