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

How to Read my oracle*ds.xml

maluram Feb 4, 2010 9:15 PM

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>
    
    <idle-timeout-minutes>1</idle-timeout-minutes>
    <track-statements>true</track-statements>
    <background-validation>true</background-validation>
    <background-validation-minutes>1</background-validation-minutes>
    <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 Feb 5, 2010 1:43 AM (in response to maluram)

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
Actions
2. Re: How to Read my oracle*ds.xml

maluram Feb 5, 2010 3:36 AM (in response to jaikiran)

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.
Actions
3. Re: How to Read my oracle*ds.xml

peterj Feb 5, 2010 10:10 AM (in response to maluram)

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.
Actions
4. Re: How to Read my oracle*ds.xml

peterj Feb 5, 2010 10:40 AM (in response to maluram)

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.
Actions
5. Re: How to Read my oracle*ds.xml

maluram Feb 5, 2010 6:21 PM (in response to peterj)

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.
Actions
6. Re: How to Read my oracle*ds.xml

peterj Feb 5, 2010 6:33 PM (in response to maluram)

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.
Actions
7. Re: How to Read my oracle*ds.xml

jaikiran Feb 6, 2010 12:18 AM (in response to peterj)

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.
Actions
8. Re: How to Read my oracle*ds.xml

vickyk Feb 6, 2010 12:29 AM (in response to jaikiran)

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.
Actions
9. Re: How to Read my oracle*ds.xml

vickyk Feb 6, 2010 12:38 AM (in response to maluram)

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.
Actions
10. Re: How to Read my oracle*ds.xml

maluram Mar 16, 2010 5:57 PM (in response to maluram)

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
Actions

Go to original post

JBossDeveloper

How to Read my oracle*ds.xml

**1. Re: How to Read my oracle*ds.xml**

**2. Re: How to Read my oracle*ds.xml**

**3. Re: How to Read my oracle*ds.xml**

**4. Re: How to Read my oracle*ds.xml**

**5. Re: How to Read my oracle*ds.xml**

**6. Re: How to Read my oracle*ds.xml**

**7. Re: How to Read my oracle*ds.xml**

**8. Re: How to Read my oracle*ds.xml**

**9. Re: How to Read my oracle*ds.xml**

**10. Re: How to Read my oracle*ds.xml**