It is not easy to describe my problem but I will try. We noticed a slow processing problem with the database when migrating an application from a current VM named VM1 (RedHat 7.9, Java 8, Tomcat, Apache in a DC data center) to a new VM named VM2 (RedHat 8.7, more performance on the Azure platform).
The database is Oracle 19c on a RedHat 7.9 (named VM_DB) on the DC.
Following this observation, I made a small Java program, which launches on the 2 VMs, VM1 and VM2, and we discovered the time "SQL*Net message from client" is too large on VM2 compared to VM1 (same driver ojdbc8-19.18.0.0.jar).
We looked on the network side, database and found nothing for the moment. Have you had any ideas or experiences on this subject?
my test app
int nLoop = 100;
long time0 = System.currentTimeMillis();
Connection con=null;
//---------------- connection 1 ---------------
con=DriverManager.getConnection(url,usr,pwd);
//---------------- connection 2 ---------------
/*
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL(url); //"jdbc:oracle:thin:@//localhost:1521/XE"
pds.setUser(usr);
pds.setPassword(pwd);
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(25);
//pds.setFastConnectionFailoverEnabled(true);
//pds.setImplicitCachingEnabled(true);
//pds.setConnectionCachingEnabled(true)
con = pds.getConnection();
*/
long time1 = System.currentTimeMillis();
System.out.println("T1 - connection :"+ ((time1-time0)));
//step3 create the statement object
Statement stmt=con.createStatement();
for(int t=0; t < nLoop; t++) {
System.out.println("------- loop " + t + "-------");
//step4 execute query
time1 = System.currentTimeMillis();
ResultSet rs=stmt.executeQuery(sql);
long time2 = System.currentTimeMillis();
System.out.println("T2 - executeQuery :"+ ((time2-time1)));
ResultSetMetaData MetaData = rs.getMetaData();
int nbColumn = (MetaData != null) ? MetaData.getColumnCount() : 1;
//System.out.println("nombreCol :"+ nbColumn);
// test fetch without display
while (rs.next()) {
for(int i = 0; i < nbColumn; i++) {
String temp = rs.getString(1+i);
if (temp != null) {;} else {;};
}
}
rs.close();
long time3 = System.currentTimeMillis();
System.out.println("T3 - fetch without display :"+ ((time3-time2)));
// test fetch avec display
rs=stmt.executeQuery(sql);
int nb = 0;
time3 = System.currentTimeMillis();
while (rs.next()) {
nb++;
for(int i = 0; i < nbColumn; i++) {
String temp = rs.getString(1+i);
System.out.println(temp);
//if (temp != null) {;} else {;};
}
}
rs.close();
long time4 = System.currentTimeMillis();
System.out.println("T4 - fetch avec display :"+ ((time4-time3)) + ", nb:" + nb);
}
long time5 = System.currentTimeMillis();
System.out.println("Total :"+ (time5-time0));
//step5 close the connection object
con.close();
// ---- JVM ---
System.out.println(" ---- JVM ---- ");
// get a RuntimeMXBean reference
RuntimeMXBean runtimeMxBean = ManagementFactory.getRuntimeMXBean();
// get the jvm's input arguments as a list of strings
List<String> listOfArguments = runtimeMxBean.getInputArguments();
// print the arguments using my logger
listOfArguments.forEach(e -> {
System.out.println(e);
//logger.log(s"ARG: $a")
});