Hey Tom,
I have a Java application that runs thousands of different types of queries against an Oracle database millions of times. I wanted to save the query preparation time by using the oracle.jdbc.implicitStatementCacheSize JDBC property to cache prepared queries. But I easily end up with an error ORA-01000: maximum open cursors exceeded, even when running a single query at a time and reading it to completion.
In my mind, an open cursor represents a way to scroll through the results of a query via communication with the database server. I don't immediately see a correlation between a statement and a cursor beyond the idea that the statement yields a cursor when executed. But it appears to be deeper than that in the Oracle JDBC driver.
See the following example code that can quickly reproduce what I am experiencing:
public class OracleCursorExhaustionThroughStatementCaching
{
public static void main(String[] args)
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
final Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
// CURSORS on the remote system are set currently to 300. Just run more unique queries than there are cursors to reproduce.
// This cache should only be holding statement information and cursors should only be used during an individual query
props.put("oracle.jdbc.implicitStatementCacheSize", "1500"); // commenting/removing this line allows this test to run without error
try (Connection c = DriverManager.getConnection("jdbc:oracle:thin:@someserver:1521/mydb", props))
{
DatabaseMetaData meta = c.getMetaData();
System.out.println("Product: " + meta.getDatabaseProductName());
System.out.println("Version: " + meta.getDatabaseProductVersion());
System.out.println("Driver: " + meta.getDriverVersion());
System.out.println("JVM Version: " + System.getProperty("java.runtime.version"));
for(int i = 0; i < 1000; i++)
{
// Each statement will be closed after executing
try(PreparedStatement ps = c.prepareStatement("select " + i + " from dual")) // for demo a unique query against dual is enough
{
// Being explicit with closing the result set after execution because logically this is the end of the cursor. (Statement close closes it anyway)
try(ResultSet rs = ps.executeQuery())
{
while(rs.next())
; // just read each result set fully, which should bring the cursor to its end
}
}
}
}
} catch(Exception ex)
{
ex.printStackTrace();
}
}
}
So on my machine and database this code yields the following:
Product: Oracle
Version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
Driver: 23.8.0.25.04
JVM Version: 21.0.8+9-LTS
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
Caused by: Error : 1000, Position : 0, SQL = select 299 from dual, Original SQL = select 299 from dual, Error Message = ORA-01000: maximum open cursors exceeded
The part I don't understand is: Why are open cursors associated with an implicit statement cache for queries that ran to completion and from my perspective should have closed the cursor? Why do they accumulate? Is there a way to make them close?
Thank you for any perspective you can offer.
We cache that cursor by holding it open (in effect, you say "please close it" and we say "Hey, you want us to cache that one, so we'll keep it open for you so no parse will be needed next time).
eg, I modified the code a little to see this in action
public class OracleCursorExhaustionThroughStatementCaching
{
public static void main(String[] args)
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
final Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
// CURSORS on the remote system are set currently to 300. Just run more unique queries than there are cursors to reproduce.
// This cache should only be holding statement information and cursors should only be used during an individual query
props.put("oracle.jdbc.implicitStatementCacheSize", "100"); // commenting/removing this line allows this test to run without error
try (Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1530/pdb21a", props))
{
DatabaseMetaData meta = c.getMetaData();
System.out.println("Product: " + meta.getDatabaseProductName());
System.out.println("Version: " + meta.getDatabaseProductVersion());
System.out.println("Driver: " + meta.getDriverVersion());
System.out.println("JVM Version: " + System.getProperty("java.runtime.version"));
for(int j = 0; j < 100; j++) // run each SQL 100 times
{
for(int i = 0; i < 100; i++) // 100 different SQLs
{
// Each statement will be closed after executing
try(PreparedStatement ps = c.prepareStatement("select /*MYSQL*/ " + i + " from dual")) // for demo a unique query against dual is enough
{
// Being explicit with closing the result set after execution because logically this is the end of the cursor. (Statement close closes it anyway)
try(ResultSet rs = ps.executeQuery())
{
while(rs.next())
; // just read each result set fully, which should bring the cursor to its end
}
}
}
}
System.out.println("Sleeping");
try { Thread.sleep(10000);} catch (InterruptedException e) { Thread.currentThread().interrupt(); return;}
}
} catch(Exception ex)
{
ex.printStackTrace();
}
}
}So when I run it and we get to the "Sleeping" part, I can look at V$SQL
SQL> select parse_calls, executions, open_versions, users_opening, sql_text
2 from v$sql
3 where sql_text like 'select /*MYSQL*/%';
PARSE_CALLS EXECUTIONS OPEN_VERSIONS USERS_OPENING SQL_TEXT
----------- ---------- ------------- ------------- -------------------------------
1 100 1 1 select /*MYSQL*/ 50 from dual
1 100 1 1 select /*MYSQL*/ 79 from dual
1 100 1 1 select /*MYSQL*/ 85 from dual
1 100 1 1 select /*MYSQL*/ 87 from dual
1 100 1 1 select /*MYSQL*/ 75 from dual
1 100 1 1 select /*MYSQL*/ 46 from dual
1 100 1 1 select /*MYSQL*/ 58 from dual
...
...
You can see we've eliminated parsing (both hard and soft, bar the very first parse) for repeated executions. We've done that by keeping the cursor open.
Once my program finishes sleeping and releases the connection
PARSE_CALLS EXECUTIONS OPEN_VERSIONS USERS_OPENING SQL_TEXT
----------- ---------- ------------- ------------- --------------------------------
1 100 0 0 select /*MYSQL*/ 50 from dual
1 100 0 0 select /*MYSQL*/ 79 from dual
1 100 0 0 select /*MYSQL*/ 85 from dual
1 100 0 0 select /*MYSQL*/ 87 from dual
1 100 0 0 select /*MYSQL*/ 75 from dual
1 100 0 0 select /*MYSQL*/ 46 from dual
...
...
those cursors are no longer open.