A very brief note to alert the community of a nasty JDBC bug affecting INSERT performance. It was noticed by our Java developers after upgrading their JDBC driver 126.96.36.199.0 to version 188.8.131.52.190416DBRU. They were inserting data in batches of 5,000 rows at a time, 250,000 total, and the time to process the entire batch went up from 16 to 102 minutes.
We ran a quick test. They fired up their load, but I wasn’t seeing anything on the database side — all their sessions were inactive, waiting on idle “SQL*Net message from client”. Then I ran SQL tracing, but once again, all I got was very quick inserts and a bunch of SQL*Net wait events. At this point I declared this to be “an application/driver issue” and moved on to other things on my agenda.
The next morning they contacted me again. They were able to get to the bottom of it, and it turned out that I was only partially right. It was indeed an application, or rather, JDBC driver issue, but a kind that could have probably been diagnosed on the database side if I paid more attention.
The problem was, as they told me, that whenever there was a lot of NULLs inserted, the driver would pause inserting data, and issue a SELECT statement, probably to poll some metadata about column data types or something like that. Since there was a lot of NULLs, there was a large amount of such SELECTs, and even though they were comparatively lightweight (so they didn’t pop up in database instrumentation), they still added enough overhead via increased network roundtrips to slow everything down manifold.
I did a quick search on MOS and I couldn’t find the bug document, and at the moment I don’t have much time to dig much deeper into this (there are some other things in my pipeline, including a few articles I’d like to publish in this blog) so I’m just sharing this as a warning for the community — if your bulk INSERTs become much slower after upgrading your JDBC driver, check if the behaviour depends on the number of NULLs being inserted, and if it does, you might be hitting this bug.
If I get any further specifics, I will add them as an update to this post.