Slow insert values with array binding after JDBC upgrade to 19c

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 11.2.0.3.0 to version 19.3.0.0.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.

9 thoughts on “Slow insert values with array binding after JDBC upgrade to 19c”

Good article. Thanks for it.

Check in v$active_session_history for time your insert is running if there is a large amount of rows with ‘V8 Describe Any’ in top_level_call_name column. That may suggest problem with prepare command used in loop on java side.

savvinov says:

June 19, 2020 at 8:22 pm

Hi Mirek,

if you read the post carefully enough, you’ll see that the sessions doing the INSERT were inactive most of the time. Active Session History only helps for active sessions.

Reply

Did you try to profile the java application to see what it is waiting for?

savvinov says:

October 15, 2020 at 6:05 am

Sorry, which Java application? The issue I’m describing here is a database one. And it has more to do with connectivity/stability than with performance.

Reply