Reached maximum capacity of pool "OraclePool"

Hello all,
            I am having some kind of a problem with closing my connection (or I am assuming thats the problem). I am using prepared statements as in

------------------------------------------------------------------------------------------------------------------
                                                              START: CODE
_________________________________________________________________________________

queryStr.append( "SELECT * FROM TABLE WHERE TABLE.x = X" );

        // SEARCH FOR THE SPECIFIC XXX SELECTED
            if ( XXX != null && XXX .intValue() != 0 )
            {
                queryStr.append( " AND TABLE.y =" + XXX );
            }
        System.out.println( "Executing query :: " + queryStr.toString() );

        try {

            getDBConnection();

            stmt = dbConnection.prepareStatement( queryStr.toString() );            
           
            result = stmt.executeQuery( queryStr.toString() );
 
            list = prepareResult( result );

        } catch( SQLException se ) {

            throw new XXXDAOException( "SQLException while searching for XXX \n" + se );

        } catch ( Exception e ) {

            throw new XXXDAOException( "Exception while searching for XXX \n\n" + e );

        } finally {

            closeResultSet( result );
            closeStatement( stmt );
            closeConnection();

        }
------------------------------------------------------------------------------------------------------------------
                                                              END: CODE
_________________________________________________________________________________

This is the error I get on the console
<ExecuteThread: '3' for queue: 'weblogic.kernel.Default'> <40199> <BEA1-3F1F451F3F1207F9FB47> <BEA-000627> <Reached maximum capacity of pool "OraclePool", making "0" new resource instances instead of "1".>

 

Hence I assumed there is some kind of a "LEAK" in one of the DAO sql queries, but couldnt find one.
Then I started looking on the internet and found instances of people talking abt cursors not getting closed and littering the database, hence when I did a

select sid,count(*) from v$open_cursor group by sid;

the count in the SID keeps increasing......which would ideally, I presume, should go upwhenever there is a page queried and then go down immediately, but which wont happen in my case......

any input will be greatly appreciated.....

This is affecting me more since this is forcing me do a start and stop of the server at frequent intervals.

Thanks in advance.
ND

naveen_damerlaAsked:
Who is Participating?
 
grim_toasterConnect With a Mentor Commented:
I would suggest then that you compare your production and test databases.  Things such as which version of Oracle each is running on (they should be the same), and then compare the init files.

Check out the settings of the following properties on each:
  _close_cached_open_cursors
  session_cached_cursors
  cursor_space_for_time
  open_cursors

As an additional note, you can use the below query to see if there is a specific query that is causing the problems.  I use it to find where cursors are not closed properly, however, the v$open_cursor table is not ideal for finding out the number of open cursors (the query you ran - or the one I'm about to post), as it can maintain old cached results, that were closed (if that makes sense!?), in order to find the number of open cursors, it is better to use the second query:

SELECT sql_text, COUNT(sql_text) FROM   v$open_cursor GROUP BY sql_text ORDER BY COUNT(sql_text) DESC;

-- The number of open cursors per session...
SELECT sid, name, value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'

0
 
Moroni24Commented:
Your code looks ok to me (for what that's worth)

Maybe posting the

closeResultSet( result );
closeStatement( stmt );
closeConnection();

methods would reveal something.
0
 
naveen_damerlaAuthor Commented:
    private void closeConnection() throws XXXDAOException {
        try {
            if ( dbConnection != null && !dbConnection.isClosed() ) {
                dbConnection.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing DB connection : \n" + se );
        }
    }

    private void closeResultSet( ResultSet result ) throws XXXDAOException {
        try {
            if ( result != null ) {
                result.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing Result Set : \n" + se );
        }
    }

 private void closeStatement( PreparedStatement stmt ) throws XXXDAOException {
        try {
            if ( stmt != null ) {
                stmt.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing Statement : \n" + se );
        }
    }


Hope that would throw more light on my problem......
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Moroni24Commented:
I see that result is defined elsewhere. Is prepareResult losing the original pointer possibly? In other words is result being reassigned?
0
 
naveen_damerlaAuthor Commented:
Thought the whole code at one place might help someone who is looking at it for the first time.....so please neglect the above code

HERE is the complete code

---------------------------------------------------------------------------------
                               START: CODE
_________________________________________________________________________________

     PreparedStatement stmt = null;
        ResultSetMetaData resultSetMetaData = null;
        ResultSet result = null;
        List list = null;
        StringBuffer queryStr = new StringBuffer();



      queryStr.append( "SELECT * FROM TABLE WHERE TABLE.x = X" );

        // SEARCH FOR THE SPECIFIC XXX SELECTED
            if ( XXX != null && XXX .intValue() != 0 )
            {
                queryStr.append( " AND TABLE.y =" + XXX );
            }
        System.out.println( "Executing query :: " + queryStr.toString() );

        try {

            getDBConnection();

            stmt = dbConnection.prepareStatement( queryStr.toString() );            
           
            result = stmt.executeQuery( queryStr.toString() );
 
            list = prepareResult( result );

        } catch( SQLException se ) {

            throw new XXXDAOException( "SQLException while searching for XXX \n" + se );

        } catch ( Exception e ) {

            throw new XXXDAOException( "Exception while searching for XXX \n\n" + e );

        } finally {
            closeResultSet( result );
            closeStatement( stmt );
            closeConnection();
        }


     
     private List prepareResult( ResultSet rs ) throws SQLException {
     .....//logic
     .....//logic
       return list;
    }

     
     private void closeConnection() throws XXXDAOException {
        try {
            if ( dbConnection != null && !dbConnection.isClosed() ) {
                dbConnection.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing DB connection : \n" + se );
        }
    }

    private void closeResultSet( ResultSet result ) throws XXXDAOException {
        try {
            if ( result != null ) {
                result.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing Result Set : \n" + se );
        }
    }

 private void closeStatement( PreparedStatement stmt ) throws XXXDAOException {
        try {
            if ( stmt != null ) {
                stmt.close();
            }
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while closing Statement : \n" + se );
        }
    }

---------------------------------------------------------------------------------
                               END: CODE
_________________________________________________________________________________
0
 
Moroni24Commented:
Everthing looks right to me. Have you tried running it through a debugger like JProfiler to see what's not getting freed. It could also be a problem with the interfaces implementation. Maybe there is an update available?
0
 
grim_toasterCommented:
The code for acquiring the connection would also help, i.e. the method: getDBConnection();
0
 
naveen_damerlaAuthor Commented:
Here is the code for the DB connection (Thank you for your interest and time)
------------------------------------------------------------------------------------------

import javax.sql.DataSource;

private transient DataSource datasource   = null;


    /** Creates a new instance of XXXDAO */
    //CONSTRUCTOR
    public XXXDAO() throws XXXDAOException {
        LogManager.info( "XXXDAO Object Created" );
        try
      {
            InitialContext ic = new InitialContext();
            datasource = (DataSource) ic.lookup( "OracleDataSource" );      

        } catch ( NamingException ne ) {
            throw new XXXDAOException( "Naming Exception while looking up OracleDataSource Connection: \n" + ne.getMessage() );
        }
    }

     private Context getInitialContext() throws NamingException {        
        try {            
            return new InitialContext();            
        } catch (NamingException ne) {                  
             System.out.println("We were unable to get a connection to the WebLogic server at ");
             System.out.println("Please make sure that the server is running.");
           throw ne;            
        }
    }

private void getDBConnection() throws XXXDAOException {
        try {
            dbConnection = datasource.getConnection();
        } catch ( SQLException se ) {
            throw new XXXDAOException( "SQL Exception while getting DB connection : \n" + se );
        }
        return;
    }
0
 
grim_toasterCommented:
Hmm... how about the datasource definition?

But as a possibility, I've previously had problems that the isClosed() returned true when it was not closed (although the API docs guarantee that it only returns true if closed), it may be worth removing the isClosed() check - if it is closed then it is a no-op call anyway.
0
 
naveen_damerlaAuthor Commented:
Another input that I observed
----------------------------------

The number of cursors keeps on increasing which I can tell from

.........................................................................................................................................
select sid,count(*) from v$open_cursor WHERE USER_NAME LIKE '%ALLSTATED%' group by sid ;
.........................................................................................................................................

This was on the production box. I tried to replicate on the TEST and DEV boxes.....

While the TEST box cursors also keep increasing and never decrease, the DEV box behaves fine with the cursors increasing when and decreasing as pages are traversed and for different logins.

So this could be some Oracle setting .......but I donot know a whole lot abt Oracle....so can anyone guide me abt any kind of setting that I could look at.

Thanks in advance
0
 
grim_toasterCommented:
Also how about the setup of the connection pool, is that different in production to test?
0
 
naveen_damerlaAuthor Commented:
The second query helped a lot in seeing how many cursors were really open ....
thanks again
Naveen.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.