I am passing Resultset object to each thread. Each thread is connecting to the database and inserting data. Untill thread 110 it is working fine. After it crosses 111 thread it throws the above exception.
I am using oracle 11g.
My sample Thread code is:
class MyThreadClass implements Runnable
{
public Connection connection;
public Statement statement2;
public ResultSet rs2;
public String cookie;
public MyThreadClass(ResultSet rs1)
{
rs2=rs1;
}
public void run()
{
try
{
cookie=rs2.getString("COOKIE");
driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@127.0.0.1:1521:xx";
/* connection
statement2.executeUpdate("INSERT INTO visit_header VALUES ('"+cookie+"')");
}
I am not getting how to handle this exception.
解决方案
Your multi-threaded application is opening too many Connections/Sessions. Hence, the listener is dropping and blocking new connections for a while.
Check your DB resource usage first:
SELECT * FROM v$resource_limit WHERE resource_name IN ('processes','sessions');
Check to see if your MAX_UTILIZATION for either your Processes or Sessions is getting too close to the LIMIT_VALUE. If yes, you should either:
Use DB Connection pooling to share Connection objects between threads. Or,
Increase the number of processes/sessions that Oracle can service simultaneously.
Actually, Connection Pooling (#1) should always be done. An application cannot scale up otherwise. Check Apache Commons DBCP for details. For #2, open a new SQL*Plus session as SYSTEM and run:
ALTER system SET processes= scope=spfile;
to increase backend concurrency. Then RESTART the Database. IMPORTANT!