ORA-00020: maximum number of processes (1000) exceeded ..Urgent
Hi ,
i have developed one webaplication in tomcat 5.5 which uses 15,000 employees.problem is in some peek times database is giving exceptionORA-00020: maximum number of processes (1000) exceeded .once i restarted my tomcat every thing is working fine...i used connection polling in my application ...
<Resource name="jdbc/Ids" auth="Container" type="javax.sql.DataSource" username="DATASYNC" password="DATASYNC"
driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@10.3.1.163:1521:ISPC" removeAbandoned="true" logAbandoned="true" removeAbandonedTimeout="60" maxActive="10" maxIdle="3" maxWait="10000" />
i dont know this problem with tomcat or database..
[720 byte] By [
newwavea] at [2007-11-27 7:11:34]
# 1
sry... here is my connection pooling code <Resource name="jdbc/Ids" auth="Container" type="javax.sql.DataSource" username="" password=""
driverClassName="oracle.jdbc.driver.OracleDriver" url="url" removeAbandoned="true" logAbandoned="true" removeAbandonedTimeout="300" maxActive="10000" maxIdle="100" maxWait="10000" />
plz let me know if any thing need to be done for databse or tomcat.
# 2
First off, I think your connection pool configuration is bad. I think that tells the pool to allow up to 10000 concurrent connections to the database, and I don't think you need that many. I haven't used this pool configuration before, but just looking at http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html I think you want something like maxActive="50" maxIdle="20" maxWait="10000". That will let the pool grow to 50 concurrent connections during heavy load and shrink down to 20 during light load. That's probably enough connections to serve many hundreds or even thousands of _concurrent_ users, depending on what kind of SQL/app you're writing.
Second, make sure you always close/release the database connections and statements and ResultSets in your Java code. And make sure that you use connections for the shortest time possible, then release them. Get a connection, run the query, read results, release connection. Probably you are not releasing connections, so the connection pool keeps creating new ones. Eventually you will hit the max, and it will die.
Connection con = null;
Statement stmt = null;
ResultSet rs = null
try {
con = // however you get a connection
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select foo from bar");
// do some processing
} finally {
try { rs.close(); } catch( Exception ex ) {}
try { stmt.close(); } catch( Exception ex ) {}
try { con.close(); } catch( Exception ex ) {}
}
Jemiah
Message was edited by:
fishninja007
# 3
Thanks for quick reply..
my server
2GB os RAM i incresed the heap size 128 to 512mb.
jdk1.5
tomcat 5.5.9
oracle 9 i.
in my appliaction is deveolped for 20000 employess.iam using only 10 dynamicjsp pages and only servlet for employee information.
the problem is this web application is default in all internet explorers in my comany...so we can expect more hits...i already checked the code many times...i closed all connections and result sets....Tell me one thing if u put maxActive="50".is it enough to thousands of _concurrent_ users...?
maxactive means ..it will give u 50 concuurent conections at any point of time...more than 50 concuurent connections to databse ...tomcat will refuse the request..am i rite?
i checked in the log file ...in some places sql exception showing connection already closed exception..is closed to connection is returned to poll?
plz share your views ..so that i can tune my application accordingly
# 5
I think you should not close the connection each time you Resultset or statement is closed.
Keep the connection as long as a user session.
If a user connects, connect to database and disconnect it on closing the session.
Each connection needs extra memory on the server and is limited also by a parameter.
You can run all your queries sharing the connection object.
Message was edited by:
pelle23
# 6
thanks for your quick reply....
iam using only 10 jsp pages ..in all pages i used DBconnector java calss to open and excuting querys ..and closing the connections and result set and statement ...
in finally blocks
problem is my database is not relasing proceesses after completion of requests...is this problem bcz of my code? whenever proceesses more than 1000 it is throughing ....ORA-00020: maximum number of processes (1000) exceeded ..once i restarted tomcat no of proceess going down....plz have a look into my db connector.....file
public class DatabaseConnector
{
public DatabaseConnector(String s)
{
driver = "";
database = "";
query = "";
userid = "";
password = "";
datasource = "";
usingPool = false;
parameterIndex = 0;
errorMessage = "";
ctr = 0;
datasource = s;
usingPool = true;
}
public boolean getConnection()
{
try
{
con = null;
prepstat = null;
if(usingPool)
{
InitialContext initialcontext = new InitialContext();
Context context1 = (Context)initialcontext.lookup("java:comp/env");
DataSource datasource1 = (DataSource)context1.lookup("jdbc/" + datasource);
con = datasource1.getConnection();
}
return true;
}
catch(SQLException sqlexception)
{
errorMessage = "DBConnector:" + sqlexception;
System.out.println(errorMessage);
}
catch(NamingException namingexception)
{
return false;
}
return false;
}
public ResultSet makeQuery(String s)
{
rs = null;
try
{
rs = con.createStatement().executeQuery(s);
}
catch(SQLException sqlexception)
{
errorMessage = "DBConnector:" + sqlexception;
System.out.println("Error while obtaining ResultSet:" + errorMessage + "\nQuery was:" + s);
}
catch(Exception exception)
{
errorMessage = "DBConnector:" + exception;
System.out.println("Error while obtaining ResultSet:" + errorMessage + "\nQuery was:" + s);
}
return rs;
}
public boolean makeUpdate(String s)
{
try
{
if(ctr == 0)
con.setAutoCommit(false);
ctr++;
prepstat = con.prepareStatement(s);
setValues();
prepstat.executeUpdate();
values = null;
parameterIndex = 0;
return true;
}
catch(SQLException sqlexception)
{
values = null;
parameterIndex = 0;
errorMessage = "DBConnector:" + sqlexception;
return false;
}
}
public void addValue(String s)
{
if(values == null)
values = new Vector();
parameterIndex++;
if(s != null && s.equalsIgnoreCase("null"))
s = null;
values.add(s);
}
public void rollback()
{
try
{
con.rollback();
}
catch(SQLException sqlexception) { }
}
public void commit()
{
try
{
if(ctr > 0)
{
con.commit();
ctr = 0;
}
}
catch(SQLException sqlexception) { }
}
public void closeAll()
{
boolean flag = false;
boolean flag1 = false;
try
{
commit();
if(prepstat != null)
{
prepstat.close();
flag = true;
prepstat = null;
}
if(con!=null)
{
con.close();
flag1 = true;
con = null;
System.out.println("connection is closed succesfully");
}
}
catch(SQLException sqlexception)
{
System.out.println("DBConnector.closeAll():Error occured while closing connection" + sqlexception + "::closeStatement?" + flag + ";closedConnection?" + flag1);
}
catch(Exception exception)
{
System.out.println("DBConnector.closeAll():Error occured while closing connection" + exception + "::closeStatement?" + flag + ";closedConnection?" + flag1);
}
}
public String getErrorMessage()
{
if(errorMessage != null)
return errorMessage;
else
return "";
}
private void setValues()
{
if(values != null && values.size() != 0)
try
{
for(int i = 1; i <= parameterIndex; i++)
prepstat.setString(i, (String)values.elementAt(i - 1));
}
catch(Exception exception)
{
System.out.println("Error occured in DBconnector" + exception);
}
}
private String url;
private String driver;
private String database;
private String query;
private String userid;
private String password;
private String datasource;
private boolean usingPool;
private int parameterIndex;
private Vector values;
private Connection con;
private PreparedStatement prepstat;
private ResultSet rs;
private ServletContext context;
private String errorMessage;
private int ctr;
}
# 7
You need to close your Statements and ResultSets!
Look at you code!
makeQuery - You need to close the returning ResultSet
makeUpdate - prepStat is never closed!
May be you should check is prepStat is open before you create a new statement.
if(prepstat!=null)
prepstat.close();
prepstat = con.prepareStatement(s);
# 8
hi..
this is only DBconnector file....
public ResultSet makeQuery(String s)
{
rs = null;
try
{
rs = con.createStatement().executeQuery(s);
}
catch(SQLException sqlexception)
{
errorMessage = "DBConnector:" + sqlexception;
System.out.println("Error while obtaining ResultSet:" + errorMessage + "\nQuery was:" + s);
}
catch(Exception exception)
{
errorMessage = "DBConnector:" + exception;
System.out.println("Error while obtaining ResultSet:" + errorMessage + "\nQuery was:" + s);
}
return rs;
}
iam closing the resultset in jsp page..look at the code..here iam just returning resultse from this method to jsp page
prepare statement closing and connection closing by using below methos of dbconnector.
public void closeAll()
{
boolean flag = false;
boolean flag1 = false;
try
{
commit();
if(prepstat != null)
{
prepstat.close();
flag = true;
prepstat = null;
}
if(con!=null)
{
con.close();
flag1 = true;
con = null;
System.out.println("connection is closed succesfully");
}
}
catch(SQLException sqlexception)
{
System.out.println("DBConnector.closeAll():Error occured while closing connection" + sqlexception + "::closeStatement?" + flag + ";closedConnection?" + flag1);
}
catch(Exception exception)
{
System.out.println("DBConnector.closeAll():Error occured while closing connection" + exception + "::closeStatement?" + flag + ";closedConnection?" + flag1);
}
}
i am closing all (i hope so)..problem is connection are closing..but in database proceess stil persists...is there any thing needs to be done for database?
# 9
> I think you should not close the connection each time
> you Resultset or statement is closed.
>
> Keep the connection as long as a user session.
>
> If a user connects, connect to database and
> disconnect it on closing the session.
>
> Each connection needs extra memory on the server and
> is limited also by a parameter.
>
> You can run all your queries sharing the connection
> object.
>
> Message was edited by:
> pelle23
That is really bad advice dude. The whole point of a connection pool is that a user may be logged in for 30 minutes but only need to execute queries for maybe 5 seconds of that time - you don't want to tie up a database connection for the whole 30 minutes. Do you really think the Sun forums are keeping a database connection special for me, that is just sitting there idle taking up memory while I'm typing this message?
You have to get a connection, run your query, release the connection. Any good connection pool implementation (anything you don't write yourself) will override the connection.close() method so that it just returns the connection to the pool and doesn't actually close the connection.
Jemiah
# 10
The first problem is that your pool is too big. If you need that many then it's probably because you're not really closing connections when you should be, and so the pool is eventually exhausted.
This code potentially allows a lot of unclosed resources. For example, what if you call getConnection() or makeQuery() more than once, without calling closeAll() in between? That will leave an open Connection or ResultSet hanging around. Also, your makeQuery() creates a statement that you have no reference to and can never explicitly close.
The first thing you should do in makeQuery or getConnection is to check if rs != null or if con != null, and if they are not null then close them (or throw an exception).
If you want to be really safe (production quality code), then you need to get rid of the member variables for connection, resultset, statement etc. and write your methods like the one below. Notice how this method always closes the result, statement, and connection no matter what. It's impossible for you to forget to close a connection elsewhere in your code because this method does it for you.
public List makeQuery( String s ) {
Connection con = null;
Statement stmt = null
ResultSet rs = null;
try {
List results = new ArrayList();
con = dataSource.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery(s) ;
while( rs.next() ) {
// copy results into the "results" list
}
return results;
} catch( SQLException ex ) {
// do something about the exception
} finally {
try { if( rs != null) rs.close(); } catch( Exception ex ) {}
try { if( stmt != null) stmt.close(); } catch( Exception ex ) {}
try { if( con != null) con.close(); } catch( Exception ex ) {}
}
}
Jemiah
Message was edited by:
fishninja007
# 11
> iam closing the resultset in jsp page..look at the> code..here iam just returning resultse from this> method to jsp pageAnd what happens when exceptions occur?For example where does the connection get closed if the commit() throws an exception?
# 12
thank you very much ..Jemiah
i'll will implement this....i think retuning result set from method is bad idea..am i rite?
problem is now iam using this DBconnector file in all jsp and servlets.....and also give me one suggestion ..in connection pool i put 500 maxactive 50 idle.....is it ok......
# 13
> thank you very much ..Jemiah
> i'll will implement this....i think retuning
> result set from method is bad idea..am i
> rite?
The result set can not exceed the scope of the statement.
The statement can not exceed the scope of the connection.
