5

For inexplicable reasons however, this morning the performance increased for two of my Queries that used to be slow. I have no idea why.
I have no authority over the server, maybe someone changed something.
The problem is no more.

In a nutshell:

  • s.executeQuery(sql) runs extremely slowly within a tomcat servlet on server
  • Same query runs fine without servlet (simple java program) on the same machine
  • Not all queries are slow within the servlet. Only a few bigger ones do
  • Same servlet runs fast on another machine

UPDATES

Please read the updates below the text !

I have a servlet that executes SQL requests and sends back the results via JSON. For some reason, some requests take a huge amount of time to execute, but when I run them in any Oracle SQL Client, they are executed in no time.

I am talking about a difference of 1 second vs 5 minutes for the same SQL (that is not that complex).

How can this be explained ? Is there a way to improve the performance of a java based SQL request ?

I am using the traditional way of executing queries:

java.sql.Connection conn = null;
java.sql.Statement s = null;
ResultSet rs = null;

String dbDriver = "oracle.jdbc.driver.OracleDriver";
String dbConnectionString = "jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":" + dbSid;

Class.forName(dbDriver).newInstance();
conn = DriverManager.getConnection(dbConnectionString, dbUser, dbPass);
s = conn.createStatement();
s.setQueryTimeout(9999);
rs = s.executeQuery(newStatement);
ResultSetMetaData rsmd = rs.getMetaData();

// Get the results
while (rs.next()) {
// collect the results
}

// close connections

I tried with ojdbc14 and ojdbc6 but there was no difference.

UPDATE 1: I tried the same SQL in a local Java project (not a servlet) on my client machine, and I get the results immediately. So I assume the problem is coming from my servlet or the tomcat configuration ?

UPDATE 2: The culprit is indeed rs = s.executeQuery(mySql); I tried to use preparedStatement instead, but there is no difference.

UPDATE 3: I created a new Servlet running on a local Tomcat and the Query comes back fast. The problem is therefore coming from my production server or Tomcat config. Any ideas what config items could affect this ?

UPDATE 4: I tried the same code in a normal java program instead of a servlet (still on the same server) and the results are coming fast. Ergo the problem comes from the Servlet itself (or Tomcat ?). Still don't know what to do, but I narrowed it down :)

UPDATE 5: Jstack shows the following (It starts where my servlet is, I cut the rest)

    "http-8080-3" daemon prio=3 tid=0x00eabc00 nid=0x2e runnable [0xaa9ee000]
   java.lang.Thread.State: RUNNABLE
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at oracle.net.ns.Packet.receive(Packet.java:311)
        at oracle.net.ns.DataPacket.receive(DataPacket.java:105)
        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:249)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:171)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:89)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79)
        at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:429)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:397)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1104)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
        - locked <0xe7198808> (a oracle.jdbc.driver.T4CConnection)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)

So i am Stuck at java.net.SocketInputStream.socketRead0(Native Method) ?

Community
  • 1
  • 1
Tim
  • 3,910
  • 8
  • 45
  • 80
  • Are the client is on same machine as the "jdbc" client? What about batching? – Jayan Jun 11 '15 at 08:21
  • 1
    are you comparing on the same basis ? Your code prints the resultset, do you do the same with the SQL Client ? Normally a query runs very fast, but transferring the data and displaying it also takes a lof ot time. And by the way: you should use connection pooling. Have you checked if the time is perhaps spent while logging on ? One more hint: you can use dbVisualizer to connect to the database. It is written in the same language (Java) but is optimized. So you can perhaps see if the problem is within Java/the driver or your code. – Marged Jun 11 '15 at 08:25
  • your not using preparedStatements that would cache your query. Please post both the queries otherwise we cannot help. – Paizo Jun 11 '15 at 08:31
  • Use a prepareStatement and set fetchziseof the resultSet to around 1000, this will decrease the number of i/o – Kalyan Chavali Jun 11 '15 at 08:38
  • Which client exactly is "*any Oracle SQL client*"? What technology are those clients using? –  Jun 11 '15 at 08:38
  • To clarify: I use Aqua Data Studio, but it's the same with Toad. Other (much bigger) sql queries run just fine. Also, it is not related to the number of results. My query is slow even with one result. I will take a look at dbVisualizer, thanks. – Tim Jun 11 '15 at 09:19
  • UPDATE: I tried the same SQL in a local Java project (not a servlet), and I get the results immediately. So I assume the problem is coming from my servlet or the tomcat configuration ? – Tim Jun 11 '15 at 11:24
  • UPDATE2: The culprit is indeed rs = s.executeQuery(mySql); I tried to use preparedStatement instead, but there is no difference. – Tim Jun 11 '15 at 12:14
  • Found a similar case: http://www.coderanch.com/t/625133/JDBC/databases/java-sql-executequery-process-slow – Tim Jun 11 '15 at 12:22
  • UPDATE3: I created a new Servlet running on a local Tomcat and the Query comes back fast. The problem is therefore coming from my production server or Tomcat config. Any ideas what config items could affect this ? – Tim Jun 11 '15 at 12:59
  • UPDATE4: I tried the same code in a normal java program instead of a servlet (still on the same server) and the results are coming fast. Ergo the problem comes from the Servlet itself (or Tomcat ?). Still don't know what to do, but I narrowed it down :) – Tim Jun 12 '15 at 08:22
  • May be issue might be with connection pooling. Check tomcat configuration. – uhs Jun 15 '15 at 07:50
  • Already checked it - it is worth mentioning that my particular servlet does not take long for all SQL queries, only a few bigger ones (but still, they are not that complex - outside of the servlet they run in less than a second). – Tim Jun 15 '15 at 07:53

5 Answers5

1

In some cases (not sure if this applies to yours) setting fetchSize on the Statement object yields great performance improvements. It depends on the size of the resultSet that is being fetched.

Try playing with it by setting it to something bigger than default 10 for Oracle (see this link).

See Statement.setFetchSize.

siphiuel
  • 3,480
  • 4
  • 31
  • 34
1

Given your symptoms, I believe that your issue is not with your SQL client code and you are in fact looking at issues with your server. The stack shows that your client is waiting for a response. This tallies with the fact that you can run the client without any problem in a separate process.

So what you probably need to look at is systemic reasons why the SQL server is running slowly and how that may be tied to Tomcat. My experience in cases like this is its usually the disk, so I'd be inclined to check whether you are paging due to a lack of RAM when Tomcat is loaded, or suffering from much higher disk ops due to a reduced disk cache. Assuming you are running on a UNIX variant, I'd have a look at vmstat and iostat for a working and broken case to eliminate such issues.

Peter Brittain
  • 13,489
  • 3
  • 41
  • 57
  • I already checked these parameters and I even changed the jvm options for Tomcat. For inexplicable reasons however, this morning the performance increased and my problem is no more. I have no idea why. I have no authority over the server, maybe someone changed something. – Tim Jun 16 '15 at 08:12
  • Oh well. At least it works now. BTW, I had assumed that your DB and web server were on the same machine. If not, you may just find that the DB was under-performing with large queries for any of the usual resource constraints related issues. – Peter Brittain Jun 16 '15 at 08:35
0

For inexplicable reasons however, this morning the performance increased and my problem is no more. I have no idea why. I have no authority over the server, maybe someone changed something.

Tim
  • 3,910
  • 8
  • 45
  • 80
0

Since your thread is waiting on socket read, which means is waiting for a response from the database server I would :

Check database performance, make sure not the instance nor the query is getting impacted at some point in time during the day?

Check your network latencies between Java and DB Servers. Same as above. Probably traceroute?

bubooal
  • 621
  • 3
  • 8
  • The DB performance is not the issue - the same query runs fast outside of a servlet on the same machine – Tim Jun 19 '15 at 09:14
  • What about network latencies between Apps and DB Server? – bubooal Jun 20 '15 at 06:02
  • I excluded that as well since most SQLs run fast within the servlet. And now the new ones do too. – Tim Jun 21 '15 at 09:34
0

Since you have not put the query, I can give you a scenario where it is possible. If you use a function in your query like to_char etc. then your table indexes wouldn't be used while executing query via JDBC but will work fine you run it in console. I don't exactly know why but there's something with JDBC driver. I had the exact same issue in db2 and I resolved it removing the use of functions.

Other scenario could be that a huge no of records is being fetched and proper batching is not implemented.

ares
  • 4,283
  • 6
  • 32
  • 63