Temporary LOB Memory Allocation and Freeing in JAVA

This Blog demonstrates memory usage when creating and freeing a Temporary LOB, as well as Oracle 60025 Event for temp segment cleanup used for temp lobs.

Sample code is appended, and tested in Oracle 11.2.0.3.0 and 11.2.0.4.0.

Run test in 4 different combinations,  output as follows:
 
java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 no no
 >>> Lob insert...1
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 1 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 12
 ------------------------------------------
 >>> Lob insert...2
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 2 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 22
 ------------------------------------------

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 no event
 alter session set events '60025 trace name context forever'
 >>> Lob insert...1
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 1 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 12
 ------------------------------------------
 >>> Lob insert...2
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 2 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 22
 ------------------------------------------

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 free no
 >>> Lob insert...1
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 0 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 12
 ------------------------------------------
 >>> Lob insert...2
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 0 0 0
 tablespace, contents, segtype, size_mb
 TEMP TEMPORARY LOB_INDEX 2
 TEMP TEMPORARY LOB_DATA 12
 ------------------------------------------

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 free event
 alter session set events '60025 trace name context forever'
 >>> Lob insert...1
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 0 0 0
 tablespace, contents, segtype, size_mb
 ------------------------------------------
 >>> Lob insert...2
 sid, cache_lobs, nocache_lobs, abstract_lobs
 229 0 0 0
 tablespace, contents, segtype, size_mb
 ------------------------------------------

Recap the above ouptut:

1. In the first two runs, BLOB freeTemporary() is not called, the locator and contents of the temporary blob are not freed.
2. In the third run, freeTemporary() is called, but event not set, the locator is freed, but the last contents of the temporary blob is not freed.
3. In the fourth run, freeTemporary() is called, and event is also set, the locator is freed, and the last contents of the temporary blob is also freed immediately.

We can see that V$TEMPORARY_LOBS counts the locators of temporary blobs, and V$TEMPSEG_USAGE represents memory usage (in Buffer Cache BLOCKS).

---------------  TestCode ---------------

import oracle.sql.*;
import java.sql.*;
import java.util.*;
import javax.sql.*;
import oracle.jdbc.*;
/*
DB setup:
  create table test_blob(x blob);
References:
  How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)
  https://community.oracle.com/thread/906568
Compile:
  /usr/java/bin/javac -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest.java
Usage:
  TempLobMemTest <numloops> <freeTemporary_?> <event_60025__?>
Runs:
  /usr/java/bin/java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 no no
  /usr/java/bin/java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 no event
  /usr/java/bin/java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 free no
  /usr/java/bin/java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar TempLobMemTest 2 free event
Oracle V$ performance views:
  freeTemporary_?: free pointer, but not memory.        see in v$temporary_lobs
  event_60025__?:  free memory.                         see v$tempseg_usage
*/
public class TempLobMemTest
{
  private static String URL = "jdbc:oracle:thin:user/pwd@testhost:1522:testdb";
  private static int numLoop;
  private static String freeTemporary;
  private static String event_60025;
  private static String SQL_LOB =
   "select sid, cache_lobs, nocache_lobs, abstract_lobs " +
    "from v$temporary_lobs where sid=sys.dbms_support.mysid";
  private static String SQL_MEM =
   "select t.tablespace, t.contents, t.segtype, " +
           "round (((t.blocks * 8192) / 1024 / 1024), 2) size_mb " +
    "from v$tempseg_usage t, v$session s " +
    "where s.saddr=t.session_addr and s.sid=sys.dbms_support.mysid";
  private static String SQL_EVENT =
    "alter session set events '60025 trace name context forever'";
 
  private static void printMemInfo(Connection conn) throws SQLException {
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(SQL_LOB);
      System.out.println("sid, cache_lobs, nocache_lobs, abstract_lobs");
      while(rs.next()) {
        System.out.println(
          rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
      }
     
      rs = stmt.executeQuery(SQL_MEM);
      System.out.println("tablespace, contents, segtype, size_mb");
      while(rs.next()) {
        System.out.println(
          rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
      }
      System.out.println("------------------------------------------");
  }
 
  private static void insertBlob(Connection conn) throws SQLException {
      BLOB b = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
      byte[] val = new byte[1024*1024*10];
      Arrays.fill(val, (byte) 1);
      b.setBytes(1, val);
      OraclePreparedStatement pstmt = (OraclePreparedStatement) conn
          .prepareStatement("insert into test_blob values(?)");
      pstmt.setBLOB(1, b);
      pstmt.execute();
     
      if (freeTemporary.equals("free"))
         b.freeTemporary(); // free pointer, no more seen in v$temporary_lobs; but memory not freed.
         //b.free();        // same as freeTemporary()
      conn.commit();
  }
 
  public static void main(String[] args) throws Exception {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection conn = DriverManager.getConnection(URL);
      numLoop = Integer.parseInt(args[0]);
      freeTemporary = args[1];
      event_60025 = args[2];
      Statement stmt = conn.createStatement();
     
      if (event_60025.equals("event")) {
        System.out.println(SQL_EVENT);
        stmt.executeUpdate(SQL_EVENT);
      }
     
      for(int i = 0; i < numLoop; ++i) {
        System.out.println(">>> Lob insert..." + (i+1));
        insertBlob(conn);
        printMemInfo(conn);
      }
  }
}