Solved

Reached maximum capacity of pool "OraclePool"

Posted on 2004-03-22
12
5,015 Views
Last Modified: 2010-04-24
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

0
Comment
Question by:naveen_damerla
  • 5
  • 4
  • 3
12 Comments
 
LVL 2

Expert Comment

by:Moroni24
ID: 10651809
Your code looks ok to me (for what that's worth)

Maybe posting the

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

methods would reveal something.
0
 

Author Comment

by:naveen_damerla
ID: 10651838
    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
 
LVL 2

Expert Comment

by:Moroni24
ID: 10652030
I see that result is defined elsewhere. Is prepareResult losing the original pointer possibly? In other words is result being reassigned?
0
 

Author Comment

by:naveen_damerla
ID: 10652122
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
 
LVL 2

Expert Comment

by:Moroni24
ID: 10652174
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
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10655670
The code for acquiring the connection would also help, i.e. the method: getDBConnection();
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:naveen_damerla
ID: 10656976
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
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10657190
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
 

Author Comment

by:naveen_damerla
ID: 10658433
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
 
LVL 7

Accepted Solution

by:
grim_toaster earned 335 total points
ID: 10665225
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
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10665226
Also how about the setup of the connection pool, is that different in production to test?
0
 

Author Comment

by:naveen_damerla
ID: 10678728
The second query helped a lot in seeing how many cursors were really open ....
thanks again
Naveen.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now