How JDBC ResultSetType Settings Trigger SQL Parsing Errors and Performance Issues

Background

Production environment had page response times of 2‑5 seconds, far exceeding normal thresholds. AWR analysis showed DBtime of 4800 per hour and a dominant wait event library cache lock, library cache:mutex X. Event 10035 logs revealed many parse‑failure errors; every failing SQL contained a ROWID column. The root cause was identified as an inappropriate JDBC ResultSetType setting that caused Oracle to automatically add ROWID during parsing.

JDBC ResultSetType Parameter

JDBC connections are created via PreparedStatement or Statement. Three constructor parameters exist: ResultSetType, ResultSetConcurrency, and ResultSetHoldability. This study focuses on ResultSetType, which can take three values: ResultSet.TYPE_FORWARD_ONLY – default, forward‑only traversal. ResultSet.TYPE_SCROLL_INSENSITIVE – scrollable, does not reflect changes made by other sessions. ResultSet.TYPE_SCROLL_SENSITIVE – scrollable, intended to reflect changes made by other sessions.

Test Environment

Three tables ( psname, psdept, psobj) were created and populated. Before each Java program run the Oracle shared pool was flushed with alter system flush shared_pool. After execution the V$SQL view was queried to capture the actual SQL that Oracle parsed.

Test Cases

1. Simple SELECT

SQL: select ID, NAME from psname When ResultSetType was TYPE_SCROLL_SENSITIVE, Oracle added a ROWID column (an alias is added in 12c, not in 11g). No parse error was logged; the final V$SQL entry showed the original SQL without ROWID.

2. Aggregate / DISTINCT

SQLs:

select count(ID) as SUM, dept_id from psname group by dept_id;

select DISTINCT OWNER, OBJECT_TYPE from TEST001.PSOBJ;

Parsing succeeded with TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE. With TYPE_SCROLL_SENSITIVE the parser generated 10035 errors because Oracle injected ROWID. Oracle’s internal correction removed the ROWID and the query executed correctly.

3. Join Query

SQL:

select n.id, n.name, d.dept_name from TEST001.PSNAME n, TEST001.PSDEPT d where n.dept_id = d.dept_id and d.dept_name = 'Sales'

Same pattern: TYPE_SCROLL_SENSITIVE caused parse errors that were later corrected; the other two types parsed cleanly.

4. WHERE Clause

SQL: select * from psname where dept_id = 2 Again, TYPE_SCROLL_SENSITIVE introduced ROWID and generated parse errors; the other two types behaved normally.

5. Dynamic Update (ResultSet Refresh)

With TYPE_SCROLL_SENSITIVE the result set was expected to reflect underlying data changes. The Java program updated dept_id in psname while iterating. The output demonstrated that the result set did not update dynamically, confirming that the parameter does not provide real‑time refresh.

JVM Memory Impact

JVM was configured with -Xmx128m -Xms64m -Xmn32m -Xss16m. Table PSOBJ was filled with 1.5 million rows (>128 MB). The same queries were executed under each ResultSetType.

Results: TYPE_FORWARD_ONLY – Oracle processed the large result set without memory errors. TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE – Oracle raised memory exceptions.

MySQL overflowed in all three cases.

Recommendations

Use the default ResultSetType ( TYPE_FORWARD_ONLY) for ordinary queries.

Choose TYPE_SCROLL_INSENSITIVE only when scrollable navigation or row comparison is required.

Avoid TYPE_SCROLL_SENSITIVE; it does not deliver true dynamic updates and can trigger ROWID‑related parse errors that waste database resources.

For very large result sets, stay with the default to prevent JVM out‑of‑memory failures.

Conclusion

Improper JDBC ResultSetType settings cause Oracle to inject unwanted ROWID columns, leading to parse failures, library‑cache lock contention, and severe performance degradation. Selecting the appropriate value—default or TYPE_SCROLL_INSENSITIVE —eliminates these issues and stabilizes database operation.