Java : reading 100 millions of rows (big data)

[bahri sirine]

Hello,
I work with java (jdbc) and oracle (BIG DATA) and when I execute a query to select all the rows in the data base (165 000 000 rows) always the program stopped and blocked (in around 10 million rows ) and there is no exception
and no error? Any idea please ? (each rows= 30 bytes)

[chris webster]

Could I ask why you are trying to read 165 million database rows via JDBC? If each row is around 30 bytes, that's about 5GB of data you are apparently trying to move around. Generally I'd need a pretty good reason to do something like that e.g. ETL into a separate system. But mostly it's a good idea to leave your data in the database if possible, and only read the rows/columns or aggregated values that you need for your Java application.

Alternatively, are you simply searching the data but only expect to fetch a much smaller volume of data into your Java application?

Either way, we probably need some more information about what you are trying to do here. Have you checked your RAM settings for Java and Oracle, or looked in the logs to see what happened when your query apparently died after reading 10 million * 30 bytes?

[bahri sirine]

How can I chech the RAM settings for java please.

[Roel De Nijs]

Maybe it could be a connection timeout as well...

[Roel De Nijs]

bahri sirine wrote:How can I chech the RAM settings for java please.

You could use the Runtime class from within your Java code to print some memory statistics: freeMemory(), maxMemory() and totalMemory().

[bahri sirine]

Roel De Nijs wrote:Maybe it could be a connection timeout as well...

I dont undrestand this ? how can I check this information and how can I correct it?

[Roel De Nijs]

bahri sirine wrote:

Roel De Nijs wrote:Maybe it could be a connection timeout as well...

I dont undrestand this ? how can I check this information and how can I correct it?

Depends on your database (e.g. for MySQL).

[bahri sirine]

SGBD= oracle

[edit] SGBD is the French acronym for RDBMS

[Roel De Nijs]

bahri sirine wrote:and there is no exception and no error? Any idea please ? (each rows= 30 bytes)

That's very strange! Are you sure your application isn't ignoring the exception? Did you check every log file?

[bahri sirine]

yes I'm sure .Just the program was bloqued. (Netbeans was bloqued also)

[Roel De Nijs]

And can you share the appropriate part of your code causing the issue?

[chris webster]

Let's step back a bit and find out what you're doing.

Start with your query:

What does your SQL query look like, and what is it trying to achieve?

Are you really trying to fetch 165 million rows into your Java application?

If so, is it really necessary to do this, or could you fetch the information you require some other way e.g. via SQL aggregate functions?

If you don't really need to fetch all this data, stop now and re-write your query so you only fetch what you need.

If you really do need to fetch all this data, have you tried running the same query directly in your SQL*Plus (or SQL Developer) client?

If so, what happens e.g. how long does it take to run?

If you haven't run it from a proper SQL client, do so and see what happens.

Have you asked your DBA to check the database logs to see what might have happened?

If your query is working OK and it is what you really need to do, then it's time to look at how it's working with JDBC. You'll need to check out the Oracle JDBC developer's guide for details on how to use Oracle JDBC drivers, or ask extra questions here. I don't use Java these days, and I don't have an Oracle RDBMS or app server available, so right now I can only suggest a few places to look for problems.

Make sure your code is not simply swallowing any exceptions.

Are you using an application server e.g. WebLogic? If so, you'll need to check the logs and connection settings etc in your app server.

Did your application or database logs show any errors or strange behaviour when you tried this query?

As Roel says, it could be a connection timeout because this query takes so long to run.

It could be a memory problem if your app is trying to read 5GB of data - this could be happening in your app server (if any), on your client or inside the database. Check the logs.

Are you running your app server with a fixed amount of RAM per session?

There are probably lots of other possibilities, but right now it's hard to know what to suggest because we don't really know anything about what you're actually doing.