Closing open Cursors

Marcus • May 07, 2020

Sign hanging in a door frame: “Sorry, we’re closed.”

Running out of open cursors in an Oracle database is a fan favorite of many of my fellow developers, by the looks of it. Who knew you needed to close prepared statements after use?

And how many months would it take to fix an issue that is primarily an issue for the tests?

How did we get here?

Our tale actually starts in the past: For a multitude of reasons all related to using an Oracle database in production, a few developers decided that Oracle databases would be very nice to have for integration tests.

And so they set out to build a small service that you could send a request to, and would get the URL for a test database back. That database was booted up as a container, and to ensure that tests ran fast, a few instances were always kept available.

And while the number of tests kept growing, the number of database instances didn’t.

Containers require resources

We encountered a ceiling to scaling up our containers: The hardware we ran the cluster on was finite. Reducing the CPU, RAM and hard drive space of individual nodes usually lead to test failures, I/O errors and the container refusing to talk to anyone in general.

So the reasonable way to reduce the resources seemed simple: Run multiple tests on a single containers.

And so that’s what I worked on, a little every other day, and it worked quite nicely for the 10~15 tests we had migrated to the new approach. As I’ve spent a while this week on migrating tests to this way of testing, a well-known issue popped up.

ORA-01000: maximum open cursors exceeded

The sad truth in many ways is simple: The code base under test was so deeply trenched in legacy that connections and prepared statements were handled rather naively. Cleaning up was almost never properly done, save for a few pieces that made it into production over the past year or three. The minority of places, really.

But to find out what cursors were opened by which test, only the database could provide us a useful answer, and I particularly cared for which SQL statements were the worst offenders.

Since the code fetching a database instance to test with, with just a new connection, you could query plenty of details. But you needed that connection first, and if you’re out of cursors that’s hard.

Peeking in

So instead, I extended the lifecycle of our containers for every test, to build a diff between the state before and after the test. In particular, the code below simply builds a map and compares it:

Map<String, Long> countOpenCursors(Connection connection) {
  try (PreparedStatement statement = connection.prepareStatement(
      "select sql_text, count(*) from v$open_cursor " +
      "group by sql_text")) {
    try (ResultSet resultSet = statement.executeQuery()) {
      Map<String, Long> cursors = new HashMap<>();

      while (resultSet.next()) {
        cursors.put(resultSet.getString(1), resultSet.getLong(2));
      }

      return cursors;
    }
  }
}

The new lifecycle was rather simple: Fetch a database instance, and before anything else is done with it, store the open cursors. After the test has successfully executed, you can just look at what’s left open:

currentCursors.entrySet()
  .stream()
  .filter(o -> initialCursors.getOrDefault(o.getKey(), 0L)
    < o.getValue())
  .forEach(o -> LOG.debug("Dangling cursor: {} ({}x)", o.getKey(),
    o.getValue() - initialCursors.getOrDefault(o.getKey(), 0L)));

Much to my surprise, running this on a very simple suite of tests produced a surprising output: The cursor count went through the roof, increasing by about 20-30 for simply starting the test, inserting some data via DbUnit, and doing a few simple bits of business logic. Only a handful SQL statements actually looked familiar, and most were nowhere to be found.

Counting connections

I was a little intimidated: I’ve got a list of cursors, but many unexpected. Somewhere, somehow, they were kept open. But where?

I decided to ignore that issue for the time being, and looked at a possibly simple solution instead: If you close all open connections, the cursors should be freed up as well. The SQL statement for this is very simple, actually:

select count(*) from v$session

Every single test class increased the connection count by 3 for the first test, and by 2 for every test afterwards. If I could track down these connections and close them, the statements opened by it wouldn’t matter.

Two of the connections were actually opened by our software, and easily found in the start class. Since the lifetime of a single execution (which is handled in a new Java process) was relatively short, the connections were allocated and released at JVM exit. Bad for the tests, but also easily fixed. For at least a short while, this would help with the initial problem.

But there was also one connection left, and over a hundred cursors for a class containing ~15 tests, increasing as more tests were executed in the same suite. That’s still way too many to ignore…

The many cursors of DbUnit

I looked through the SQL statements again, and found exactly one that looked familiar:

select 'TABLE' as table_type from dual
union
select 'VIEW' as table_type from dual
union
select 'SYNONYM' as table_type from dual

In particular, a test class with many different test cases ran into an open cursor issue. At the time, I seemed to recall it being a driver issue when using DbUnit. We fixed it somehow (possibly by cleaning up some prepared statement uses, I don’t remember).

Eventually it dawned upon me: Throughout the entire lifetime of the suite, we keep a single DbUnit connection open. The more dataset operations you execute on that single connection (primarily inserting & asserting), the more cursors are retained.

Closing the connection after each test class (and before printing the open cursors) resulted in ±0 connections and ±0 cursors for almost all of our existing tests. The two or three exceptions created additional connections in code that they never closed, and all listed SQL statements were coming from our application.

The SQL statement above leading to an open cursor seems to be due to a slightly outdated Oracle driver, and is not necessarily an indication we’re using DbUnit wrong. It helped me look in the right place.

Key Takeaways

}