JdbcCursorInputSource and OutOfMemoryError

[ndefreitas]

Did you guys try using Ecplise TPTP to help narrow down which objects are hanging around, and what's referencing them?

[gcollin]

In fact, I could get eclipse profiler to run (timeout in running process) and the client don't want to buy a tool.


Anyway, if it's a normal behaviour of the oracle driver to keep data in memory when using SCROLLABLE resultset, I guess there's not much I can do.

I think I'm going to rewrite a custom JdbcCursorInputSource to override this problem.

Gerard COLLIN

[ndefreitas]

You can send the profile data to a file, and then analyze it later - this way you shouldn't timeout. Also, if you want a better tool try JInsight from IBM: http://www.alphaworks.ibm.com/tech/jinsightlive

[gcollin]

Howver, this kind of test is planned for later.

Gérard

[gonzalad]

Hello,

Very interesting thread.

Just googled a bit looking at jdbc drivers implementation of scrollability.

It appears most jdbc drivers don't implement server side scrollability, but emulate it on the client side.
ResultSet scrollability is usefull only with Sql server on Jtds drivers.

So, I'm a bit sceptic about JdbcCursorItemReader usability for reading large volumes of data.
One way of using JdbcCursorItemReader is like gcollin told to remove TYPE_SCROLL_INSENSITIVE, and forget abour restartability (never call absolute()).
Another way is perhaps on restart to reread the data from the last line read (using a separate restartSql and storing the pk of the last processed item ?).

This link shows a list of drivers and whether they implement or not server side rs scrollability : http://www.hibernate.org/314.html.
Here's a summary (I've just copy/paste the info for quick reference) :

1. Oracle : [KO] Client side support
2. Sql server on Jtds : [OK] Server side support
3. Sybase on Jtds : [KO] Client side support
4. Mysql [KO] : Client side support.
5. Hsqldb [KO] : scrollable rs not supported.
6. Firebird JDBC driver : [KO] client side.
7. PostgreSQL JDBC driver ?
8. Informix JDBC driver : [OK] by creating a temporary table.

[1] Reference documentation for Oracle (http://download.oracle.com/docs/cd/B...t.htm#i1022319).

Oracle reference documentation claims scrollability is implemented on the client side.
Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer.
It is important to be aware that this is accomplished by using a client-side memory cache to store rows of a scrollable result set.

Important:
Because all rows of any scrollable result set are stored in the client-side cache, a situation where the result set contains many rows, many columns, or very large columns might cause the client-side Java Virtual Machine (JVM) to fail. Do not specify scrollability for a large result set.

In fact, Oracle jdbc driver 11.1.0.6.0 default implementation (oracle.jdbc.driver.OracleResultSetCacheImpl) just stores every line of the resultSet in a Vector. The lines are stored in the Vector when they are requested (for instance, if you call rs.last(), it just retrieves every line of the resultSet and store them in the Vector).
Oracle allows to override the default behaviour by implementing the OracleResultSetCache interface (for instance caching a number of items equal to the commit-intervall for Spring Batch). Not sure it's the way to go since it's really Oracle-specific.

Hope this search helped !

[lucasward]

That's a really great post. I've known about issues such as this for awhile, which is why I also created the DrivingQueryItemReader. It's the approach used on another client to solve this issue. The problem is, there's really a limit to the number of keys you can store in memory within a jvm. At this client we solved it by 'partitioning' the driving query with a maximum key limit in mind, something like 100,000 keys per partition. You could then split the data up with a query such as : select count(ID) from T_FOOS order by ID. (The order by would be important here) Then you could kick off N jobs where N equals the number of partitions. The JobParameters would then contain the begin and end ID for that partition (giving you separate instances). There's a little work to be done to make sure the KeyCollector implementations can pull and set up a prepared statement from the JobParameters, which I'll be working through today and tomorrow. If you look at the latest trunk I've put something forward that works for JdbcCursorItemReader, but I agree there's some work that needs to be done there too. (I may move some implementation pieces into samples before the release)

One long term solution to the cursor problem might be to make it forward only and buffer up until mark. It would require holding what the RowMapper returned in a buffer until the next mark, in which case the buffer contents can be thrown away. The only reason the buffer is needed is so that rollback can be supported. I don't have time to implement that right now, but it's an enhancement I'm looking to make in the future.

[gonzalad]

Thanks Lucas !
You fixed this issue with 1.0.0-FINAL.

(I was testing my own buffered implementation, I'll throw it away )

[lucasward]

Yeah, sorry about that, I forgot to mention in this thread that I fixed in final. I wasn't going to touch it until 1.1, but then when I dug into it, i really started to think of it as a bug. It didn't take too long to fix, I'm glad it works for you.

[Dave Syer]

I'm glad we fixed the issue. As a further improvement, why couldn't we use the RDBMS to do the scrolling? (It would be platform dependent SQL.) DrivingQueryItemReader would benefit from this as well.

[lucasward]

I'm not sure I fully understand Dave? It seems like to do that you're talking about some form of pagination? Isn't that quiet a bit different from opening a database cursor? I think you're right that it might hold some promise for the driving query reader, but I think it would be fundamentally different from a cursor reader, unless I'm completely misunderstanding.