10 Replies Latest reply on Mar 16, 2010 5:57 PM by Ram kumar

    How to Read my oracle*ds.xml

    Ram kumar Newbie

      In my testing environment, I have trouble managing the connections. Beyond certain point, connections are not destroyed in JBoss EAP5.0. Without going much in there, thought of testing my *ds.xml parameters.

       

      <local-tx-datasource>
          <jndi-name>myDataSource</jndi-name>
          <use-java-context>false</use-java-context>
          <connection-url>@rca_url@</connection-url>
          <driver-class>oracle.jdbc.OracleDriver</driver-class>
          <user-name>@user@</user-name>
          <password>@pwd@</password>
          <min-pool-size>5</min-pool-size>   
          <max-pool-size>50</max-pool-size>
          <!--<blocking-timeout-millis>60000</blocking-timeout-millis> -->
          <idle-timeout-minutes>1</idle-timeout-minutes>
          <track-statements>true</track-statements>
          <background-validation>true</background-validation>
          <background-validation-minutes>1</background-validation-minutes><!-- deprecated ? -->
          <background-validation-millis>10000</background-validation-millis>   
          <prepared-statement-cache-size>10</prepared-statement-cache-size>     
          <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
          <new-connection-sql>select 1 from dual</new-connection-sql>
          <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>      
          <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
          <metadata>
             <type-mapping>Oracle9i</type-mapping>
          </metadata>
        </local-tx-datasource>

       

      With above xml deployed, I fired my java test client to get 25 connections in a loop and closing them immediately.

      Result: Connection created 10 & Connection Destroyed Count = 5.  Fine with proper closing of connections.

       

      Now I flushed the connections and run the test client again - without closing the connections, which I am interested in.

      Even after waiting 15 minutes,  Connection Destroyed Count = 0 and all the 25 created are still in use.

       

      How do I read my above *ds.xml - if I want to know when/whether unused connections will be destroyed ?. I would expect, stale connection validation fires every 10 seconds and if found they are closed.

       

      Any help much appreciated.


        • 1. Re: How to Read my oracle*ds.xml
          jaikiran pai Master

          maluram wrote:

           


           

          and run the test client again - without closing the connections, which I am interested in.

          Even after waiting 15 minutes,  Connection Destroyed Count = 0 and all the 25 created are still in use.

           

          How do I read my above *ds.xml - if I want to know when/whether unused connections will be destroyed ?. I would expect, stale connection validation fires every 10 seconds and if found they are closed.

           

          Any help much appreciated.


          Connections handed out to clients (like your java program) are not stale connections. Those are in-use connections. Unless the client calls close on that connection, the connection will not be returned to the pool and will stay as in-use

          1 of 1 people found this helpful
          • 2. Re: How to Read my oracle*ds.xml
            Ram kumar Newbie
            Thanks, Jai. I understand that. What I am looking is something like a 'query-timeout' but for a connection. My focus is not to touch the code, at this point.
            • 3. Re: How to Read my oracle*ds.xml
              Peter Johnson Master

              Where are you getting the Connection Destroyed Count? From the connection pool mbean? There is no way that that count could decrease to 0 after being set at 5, unless there is something you are not telling us.

               

              The real value of interest, however, is the in use connection count. And what does your database say is the connection count?

               

              As far as interpreting your settings, you will have a minimum of 5 and a maximum of 50 connections, and if a connection is idle for 1 minute it will be closed.

              • 4. Re: How to Read my oracle*ds.xml
                Peter Johnson Master
                maluram wrote:
                What I am looking is something like a 'query-timeout' but for a connection.

                 

                There is a <query-timeout> entry you can add to the *-ds.xml file. The DTD file is located at docs/dtd/jboss-ds_5_0.dtd, it lists all of the settings along with a brief description of each.

                • 5. Re: How to Read my oracle*ds.xml
                  Ram kumar Newbie
                  peterj wrote:

                   

                  Where are you getting the Connection Destroyed Count? From the connection pool mbean? There is no way that that count could decrease to 0 after being set at 5, unless there is something you are not telling us.


                  I flushed the connections and run the test client again - without closing the connections, which I am interested in. Even after waiting 15 minutes,  Connection Destroyed Count = 0 and all the 25 created are still in use. This is from admin-console.

                  peterj wrote:

                   

                  The real value of interest, however, is the in use connection count. And what does your database say is the connection count?

                   

                  As far as interpreting your settings, you will have a minimum of 5 and a maximum of 50 connections, and if a connection is idle for 1 minute it will be closed.

                  The active connections are confirmed both from netstat and database. Perhaps as Jai pointd out, unclosed connections are still in use. Not recycled.

                  • 6. Re: How to Read my oracle*ds.xml
                    Peter Johnson Master
                    Yes, you do have to close a connection before it can be considered as being idle. I know that at one time the app server would close the connection for you if your servlet did not close it (saw lots of forums posts on this); not sure if that is still the case.
                    • 7. Re: How to Read my oracle*ds.xml
                      jaikiran pai Master

                      peterj wrote:

                       

                      I know that at one time the app server would close the connection for you if your servlet did not close it (saw lots of forums posts on this); not sure if that is still the case.

                      I think (although i am not sure), the CachedConnectionManager comes into picture only in a app server environment (i.e. a client like a servlet, EJB etc...). In this case where the client is a standalone application (and doesn't doesn't enroll in some JTA transaction), i don't think it plays a part. I'll have to lookup the docs some time later today.

                      • 8. Re: How to Read my oracle*ds.xml
                        Vicky Kak Master

                        jaikiran wrote:

                         

                        peterj wrote:

                         

                        I know that at one time the app server would close the connection for you if your servlet did not close it (saw lots of forums posts on this); not sure if that is still the case.

                        I think (although i am not sure), the CachedConnectionManager comes into picture only in a app server environment (i.e. a client like a servlet, EJB etc...).

                        Yes CCM comes into picture with meta-aware objects which are Servlet/EJB.

                        • 9. Re: How to Read my oracle*ds.xml
                          Vicky Kak Master

                          Isolate the problem from your end.

                          Using the JCA connection in the standalone application does not add much value, you should use it via jee component(servlet/ejb).

                          Perform some of the tests with the ejb components, your standalone client should talk to the SLSB and the SLSB should perform these tests.

                          Let us know if you find the difference in tests.

                          Investigate jca logs, dig code, attach test case which will simulate the issue.


                          • 10. Re: How to Read my oracle*ds.xml
                            Ram kumar Newbie

                            Could not do/close it in time though. For the solution, please see the 'Note:' at section "Configuring a DataSource for remote usage" on /wiki/configdatasources