Oracle processes got maxed out while archiver was running.

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Oracle processes got maxed out while archiver was running.

Vikas
This post has NOT been accepted by the mailing list yet.
The_ibatislogs.zipHello Team,

Our product uses Ibatis for its Archiver module. Archiver uses below archiversqlconfig.xml as main configuration:

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

<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings
        cacheModelsEnabled="false"
        enhancementEnabled="true"
        lazyLoadingEnabled="false"
        defaultStatementTimeout="1800"
        useStatementNamespaces="true"
        statementCachingEnabled="true"/>

    <transactionManager type="${transMgrType}">
        <dataSource type="${datasourceType}">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${user}"/>
            <property name="JDBC.Password" value="${password}"/>
           
            <property name="DataSource" value="${datasource}"/>
        </dataSource>
    </transactionManager>

    <sqlMap resource="selectprocessdata.xml"/>
    <sqlMap resource="deleteprocessdata.xml"/>
    <sqlMap resource="insertprocessdata.xml"/>

</sqlMapConfig>
===================

We have observed that some of the time in customer's environment when Archiver runs for quite some time, it errors out with "java.sql.SQLNonTransientConnectionException: [][Oracle JDBC Driver][Oracle]Connection refused", which basically is coming from DB because max process at DB is reached.

If we run a report query against the DB host with below query:

select machine, '~' as D1, schemaname, '~' as D2, status, '~' as D3, TO_CHAR(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS'), '~' as D4, LAST_CALL_ET "Elapsed Seconds", '~' as D5, COUNT(*)
from v$session WHERE SCHEMANAME NOT IN ('SYS','SYSTEM','DBSNMP','DBAUSER') GROUP BY machine, schemaname, status, TO_CHAR(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS'), LAST_CALL_ET order by machine, schemaname, status, TO_CHAR(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS'), LAST_CALL_ET;

We in our local machine could see connection count rising to 220 and above, with no user working on the application. We could see Ibatis working with few connection with their own unique ID, but behavior is contradicting with above report. All those 220 and above connection shows up as inactive in the report and stays there until long.

We do wrap up our delete and update queries within:

{
sqlMapper.startTransaction();
---------
try
{ sqlMapper.endTransaction(); }
catch(Exception ignore) {}
}

But there are some places where we do "sqlMap.queryForList" frequently out side of transaction boundary(outside of code as above). Can anyone help as determine if there is connection leak at some point in the Product code or is this a know Ibatis problem?

Thanks
--Vikas