This discussion is archived
2 Replies Latest reply: Sep 19, 2012 6:16 PM by 962973 RSS

Inactive Session Count in Oracle DB in case of Databse XA-Connection

962973 Newbie
Currently Being Moderated
In an application MDB (Message Driven Bean) is taking message from MQ and doing some processing in java as well as in database.
When a message is received from MQ, an XA database connection is created and after processing XA databse connection is closed.
This processing is running properly without any error, but when we checked the database, we found there are more than 100 inactive database sessions
related with a particular SQL query (can say problematic SQL query). To handle this issue, now while processing a message, we take a seperate XA database connection and execute that particular SQL query (can say problematic SQL query).
By doing so, Inactive Session issue resolved, but now I am in dilemma, how and why it's happening becuase there are some specific points and on the basis them I am not getting the exact reason.
The specific points are:


1. For problematic SQL query, we just take a new XA connection connection every time, but we do not close it,
2. Even the above database connection is not closed, the count of new opened connection is not increased. It's same as before taking a new connection.
3. We didn't change any logic, we just took a new XA database connection for problematic SQL query, then why Inactive Session count issue resolved?


Application Server :Oracle Application Server (OAS)
Database : Oracle 10g
XA Connection pooling used: Yes
XA Connection pooling settings :Same as default provided by OAS

If anybody can give some explanation regarding above, I will be highly thankful for.


Reagrds
Ashish
  • 1. Re: Inactive Session Count in Oracle DB in case of Databse XA-Connection
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Thanks for posting some version info but you didn't post any information about how the connection pooling is setup and the information you posted about how you are using it is confusing.
    >
    1. For problematic SQL query, we just take a new XA connection connection every time, but we do not close it,

    2. Even the above database connection is not closed, the count of new opened connection is not increased. It's same as before taking a new connection.
    3. We didn't change any logic, we just took a new XA database connection for problematic SQL query, then why Inactive Session count issue resolved?
    >
    But earlier you said
    >
    When a message is received from MQ, an XA database connection is created and after processing XA databse connection is closed.
    >
    Which is it? Is your code closing the connection or not?

    When you use pooling when your code 'closes' a connection the connection is normally not really closed but just returned to the pool for reuse.

    When your code requests a 'new' connection the pool will either hand you a connection from the pool or create a new connection and hand you that one.

    It sounds like your pool is creating new connections every time you ask for one. That may be because of the way you have the pool configured; can't tell since you didn't post any of that information.

    Post the specifics about how the pool is configured and clarify how you are using it.
  • 2. Re: Inactive Session Count in Oracle DB in case of Databse XA-Connection
    962973 Newbie
    Currently Being Moderated
    Thanks for taking interest:

    The connection pool is created using Oracle Enterprise Manager console.
    The connection pool settings are as follows:

    Initial size of Connection Cache           : 0
    Minimum Number of Connections           : 0
    Maximum Number of Connections           : -1
    Connection Retry Interval (seconds)           : 1
    Maximum Connection Atempts                : 3
    Maximum Number of Statement Cached           : 0
    Lower Threshold Limit on Pool (%)           : 20

    Inactivity Timeouts (seconds)                : 60
    Wait For Used Connection Timeout (seconds)      : 0
    Max Active Time for a Used Connection (seconds) : 0
    Abandoned Connection Timeout(seconds)           : 0
    Enforce Timeout Limit Interval (seconds)      : 900

    I w'ld like to give you the following scenario as an example similiar to my application code:

    1. MDB received a message
    2. MDB open a new XA database connection (name XADBConnection1)
    3. MDB calls different methods to perform the task
         3.1 method1() executes Query1 with XAConnection1,
         3.2 method2() executes Query2 (this appears in database associated with Inactive Sessions) with XAConnection1,
         3.3 method3() executes Query3 with XAConnection1,
         3.4 method4() executes Query4 with XAConnection1,
    4. MDB closes the connection XAConnection1.


    Now after modification the only change in above scenario is in point 3.2 mentioned below:

    ...
         3.2 method2() opens a new XA database connection named XAConnection2 and executes Query2 with XAConnection2,
    ...


    I have following concerns:

    1. I was trying to findout why Inactive Session (associated with Query2 ) were there in database even though we were closing JDBC Result Set/Statement/Connection properly.
    2. I didn't get the reason for above (step1), so we just tried to handle Query2 by executing it under XAConnection2 (we don't close it), still count of open database connection doesn't increase,
    3. In above step 2, Inactive Session Count problem resolved, but how/why resolved? I am not getting the reason anywhere.


    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points