OracleLobHandler: Temporary LOBs don't get freed and eat TEMP tablespace [SPR-6209]

[spring-projects-issues]

Carsten Jahn opened SPR-6209 and commented

We were facing an issue which the temp tablespace being used more and more while CLOBs were fetched in the application.

There are two kinds of LOBs: "ordinary" LOBs and temporary LOBs. Temporary LOBs are constructed on the fly, e.g. while processing a select that appends to LOBs to each other (e.g. molformula || molformula) or functions that create LOBs (e.g. molfile(ctab) returns a temporary CLOB). Temporary CLOBs need to be stored somewhere until they are not needed any more, and they are stored in the TEMP tablespace.

From the Oracle documentation on "Working With Temporary LOBs":
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oralob.htm#i1060097
"When fetching data from a ReultSet with columns that are temporary LOBs, use getClob or getBlob instead of getString or getBytes. Also invoke freeTemporary to free the temporary LOBs."

This practice is not implemented in OracleLobHandler. It has methods like

public String getClobAsString(ResultSet rs, int columnIndex) throws SQLException {
logger.debug("Returning Oracle CLOB as string");
Clob clob = rs.getClob(columnIndex);
return (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
}

that should better be implemented that way:

public String getClobAsString(ResultSet rs, int columnIndex) throws SQLException {
logger.debug("Returning Oracle CLOB as string");
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(columnIndex);
String returnString = (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
if( clob.isTemporary() ) {
clob.freeTemporary();
}
return returnString;
}

I think that all getClob* / getBlob* methods are affected, although I just tested the change that I pasted above. It works for me, the TEMP tablespace does not get used much and I can fetch large amounts of records with temporary CLOBs - that was not possible before, I just got "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" after some time.

To reproduce, I think you need a sufficiently small TEMP tablespace, and a SELECT statement that returns large temporary CLOBs, e.g. by appending columns with || and returning the append result.

Without this fix, TEMP tablespace is freed only after Oracle session termination.

---

Affects: 2.5.6, 3.0 RC1

Issue Links:

• Memory Leak in Oracle LOB Reading?? [SPR-5998] #10666 Memory Leak in Oracle LOB Reading??

Referenced from: commits 5a158fb

[spring-projects-issues]

Thomas Risberg commented

I can see the temporary LOBS hanging around using "select * from V$TEMPORARY_LOBS"

Should be able to get this fixed by calling freeTemporary()

Looks like the temp space is not completely freed up until the connection is closed since I can see space used in the temporary tablespace. But no temporary lobs are reported by "select * from V$TEMPORARY_LOBS".

[spring-projects-issues]

Thomas Risberg commented

Both of these issues involve releasing LOB resources after read

[spring-projects-issues]

Thomas Risberg commented

If we free the temporary LOB then we can't access the LOB again.

Since this is a change in behavior for the LobHandler it won't be the default to call freeTemporary on any LOB after read.

I added a property on the OracleLobHandler named "releaseResourcesAfterRead" which can be set to true to force open/close calls to be made on any LOBs that are read and freeTemporary on any temporary LOBs read. This of course means that you can only read the LOBs once.