CODEWITHSUNDEEP
linuxperformanceoracle-databasejdbcjava
phyerbarte
phyerbarte

Reputation: 199

JDBC programms running long time performance issue

My program has an issue with Oracle query performance, I believe the SQL have good performance, because it returns quickly in SQLPlus.

But when my program has been running for a long time, like 1 week, the SQL query (using JDBC) becomes slower (In my logs, the query time is much longer than when I originally started the program). When I restart my program, the query performance comes back to normal.

I think it is could be something wrong with the way I use the preparedStatement, because the SQL I'm using does not use placeholders "?" at all. Just a complex select query.

The query process is done by a util class. Here is the pertinent code building the query:

public List<String[]> query(String sql, String[] args) {
    Connection conn = null;
    conn = openConnection();
    conn.setAutocommit(true);
    ....
    PreparedStatement preStatm = null;
    ResultSet rs = null;
    ....//set preparedstatment arg code 
    rs = preStatm.executeQuery();
      ....
    finally{
           //close rs
           //close prestatm
           //close connection
     }
 }

In my case, the args is always null, so it just passes a query sql to this query method. Is that possible this way could slow down the DB query after program long time running? Or I should use statement instead, or just pass args with "?" in the SQL? How can I find out the root cause for my issue? Thanks.

Upvotes: 2

Views: 1513

Answers (2)

bubooal
bubooal

Reputation: 621

You first need to look into data that will help you see where you are spending most your time, guessing is not an option when performance tunning.

So I would recommend get solid data that pin points the layer presenting the issue (JAVA or DB).

For this I would suggest to look at AWR and ASH reports when the problem is most noticeable. Also collect data on the JVM (you can use JConsole and/or JVisualVM).

When first diagnosing bad performance I always do the "USE" method, Utilization, Saturation and Error.

So first, look for Errors in logs.

Then look for any resource becoming Saturated (CPUs, Memory etc...)

Finally Look at the Utilization of each resource, having a client server layout will make this easier, if this is not the case you will need to drill down to process level to know whether its Java or the DB.

Once you have collected this data you can direct your tunning efforts accordingly. Going this approach will only make you waste time and sometimes even mask problems or induce new ones.

You can come back later with this data and we can take a look!

Upvotes: 0

refeline
refeline

Reputation: 39

Maybe problem in jdbc cache... oracle spec Try to turn it off. or try to reinit the driver some times (one time per day)

Upvotes: 1

Related Questions