Home » Developer & Programmer » JDeveloper, Java & XML » Oracle JDBC Multithread Problem?
Oracle JDBC Multithread Problem? [message #91851] Thu, 08 May 2003 07:17 Go to next message
Sheldon Tien
Messages: 4
Registered: May 2003
Junior Member
To Whom It May Concern:
The following is our problem description.

Test Envirment:
*Client:
*Machine: Hp/8500
*Operating System: Windows2000 advanced server
*CPU: 8*733M
*Memory: 2G
*Software:Oralce 9.0.1.1.1 Client/JDK1.4.1_02/JDBC9.0.1

Server:
*Machine: Dell
*Operating System: Windows2000 server
*CPU: 2*1.1G
*Memory: 1G
*Software: Oralce 9.0.1.1.1 Server

Problem:

First we increase the oracle initial parameter process on the server side to guarantee the client can establish enough connections. Then no matter how many threads we opened on client and no matter how many connection we setup on client, the usage of CPU on client is still about 20%. But the usage of CPU on the server side is only 10% which meaned it was not busy, and the disk is also not busy. So where is the problem? How can we solve it?

When the test was going on, we often encountered ORA-1810 (format code appears twice) and ORA-1821 (date format not recognized). What does that mean? And how can we solve it?

Thank you for your concern.

Best regards

Test Script:

package JTestOracle;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class JTestForOracleSvc
extends Thread {

private String m_strID; //Thread ID

public JTestForOracleSvc(String strID) {
m_strID = strID;
}

public void run() {
String strDirver = "oracle.jdbc.OracleDriver";
String strURL = "jdbc:oracle:oci:@TRANDEMO187";
String strUserName = "scott";
String strPassWord = "tiger";
Connection con = null;
Statement stat = null;
String m_strSQL = "";

// Create DB connection
try {
Class.forName(strDirver);
try {
con = DriverManager.getConnection
(strURL, strUserName, strPassWord);
}
catch (Throwable e) {
System.out.println(e.toString());
}
System.out.println("conn create success on thread " + m_strID);
}
catch (Exception e) {
System.out.println(e.toString());
}

//Create Statement
try {
stat = con.createStatement(ResultSet.
TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}
catch (SQLException eSQL) {
System.out.println(eSQL.getMessage());
}
catch (Exception e) {
e.printStackTrace();
}

//Execute Query
while (true) {
try {
m_strSQL = "select * from scott.emp t where t.ename = 'SMITH'";
ResultSet rs = stat.executeQuery(m_strSQL);
}
catch (SQLException eSQL) {
System.out.println(eSQL.getMessage());
}
catch (Exception e) {
e.printStackTrace();
}
}
}

public static void main(String[[]] args) {

if(args.length != 1)
{
System.out.println("----please input thread count ");
return;
}
int iThreadNumber = Integer.parseInt(args[[0]]); //thread count
//Set up Threads
JTestForOracleSvc oraThread[[]] = new JTestForOracleSvc[[iThreadNumber + 1]];
for (int i = 1; i <= iThreadNumber; i++) {
oraThread[[i]] = new JTestForOracleSvc("thread" + String.valueOf(i));
}

System.out.println("----test for oracle start......");

for (int i = 1; i <= iThreadNumber; i++) {
oraThread[[i]].start();
}

}
}
Re: Oracle JDBC Multithread Problem? [message #91852 is a reply to message #91851] Thu, 08 May 2003 08:37 Go to previous messageGo to next message
Roye Avidor
Messages: 2
Registered: May 2003
Junior Member
I don't know if it's related to your problem, but it seems that you may do calls on uninitial object.
If the connection failed and it throw the program
will still try to get a statement object from faild
connection. this can explain why you're getting more
than just one error at a time. use a flag that will indicate if you can continue ( if an error was detected ). plus, add to each of the println ( in the catch function ) some string that will tell you which
object done the throw.
Re: Oracle JDBC Multithread Problem? [message #91854 is a reply to message #91852] Thu, 08 May 2003 20:33 Go to previous messageGo to next message
Sheldon Tien
Messages: 4
Registered: May 2003
Junior Member
When I run the Test Program, the messages show every thread had got a success initial connection, and no exception thrown out.

And something more detail:
when we set the thread number to 80, and run the program on the client, the CPU is about 20%. Then we set the thread number to 40, and start two instance of the program, which means we have two process running , each have 40 threads, and total 80 thread, then the CPU is about 40%. It means the server is not the bottle neck, and the client machine is not the bottle neck, we just can't use mutiple CPU efficiently within one process . So what's the problem? is there any configuration we can set?
Re: Oracle JDBC Multithread Problem? [message #91863 is a reply to message #91851] Fri, 30 May 2003 14:09 Go to previous messageGo to next message
Tom
Messages: 67
Registered: June 1998
Member
VERY important!

You have to close your connection...
If not, all connections will used up and eventually be freed because of a timeout.

con.close();
Re: Oracle JDBC Multithread Problem? [message #91864 is a reply to message #91863] Sun, 01 June 2003 20:11 Go to previous message
Sheldon Tien
Messages: 4
Registered: May 2003
Junior Member
yes, I lost "con.close" at the end of the RUN() function.but I think it's not the problem.
I open a new connection at the beginning of the RUN() function, and use it in the while loop, the loop will never end, the connection is always in use.

I installed Oralce 9.2 client last days , and the CPU usage become higer, but still not reach 100% . What is the problem ?
Previous Topic: JDBC Thin client and proxy user
Next Topic: Stored Java function and UNC pathname
Goto Forum:
  


Current Time: Thu Aug 05 20:40:09 CDT 2021