Simple Example of Memory Profiling in JDeveloper 11g

In the 11g JDBC Developer's guide below it clearly specifies the following.

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oraint.htm#i1058743

Important: The cursor associated with a REF CURSOR is closed whenever the statement object that produced the REF CURSOR is closed. Unlike in past releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed.

So here is how I used JDeveloper memory profiler to verify that this indeed was the case.

1. new java class as follows

package pas.jdbc.refcursor;
import java.sql.*;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleTypes;

public class RefCursorExample
{
private boolean detailsDisplayed = false;
private Connection conn = null;

public RefCursorExample() throws SQLException
{
conn = getConnection();
}

public void run () throws SQLException
{
CallableStatement cstmt = null;
ResultSet cursor = null;

try
{
if (!detailsDisplayed)
{
  displayDeatils(conn);
  detailsDisplayed = true;
}

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a standard ResultSet
while (cursor.next ())
{
  System.out.println (cursor.getString(1));
}
System.out.println("\n");
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
if (cursor != null)
{
  cursor.close();
}
}


}

public void displayDeatils (Connection conn) throws SQLException
{
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData ();

// gets driver info:

System.out.println("\n=============\nDatabase Product Name is ... " +
meta.getDatabaseProductName());
System.out.println("\nDatabase Product Version is  " +
meta.getDatabaseProductVersion());
System.out.println("\n=============\nJDBC Driver Name is ........ " +
meta.getDriverName());
System.out.println("\nJDBC Driver Version is ..... " +
meta.getDriverVersion());
System.out.println("\nJDBC Driver Major Version is " +
meta.getDriverMajorVersion());
System.out.println("\nJDBC Driver Minor Version is " +
meta.getDriverMinorVersion());
System.out.println("\n=============");
}

public static Connection getConnection() throws SQLException
{
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@beast.au.oracle.com:1521:linux11g";
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(thinConn, username, password);
conn.setAutoCommit(false);
return conn;
}

public static void main(String[] args) throws Exception
{
RefCursorExample refCursorExample = new RefCursorExample();
refCursorExample.run();

for (int i = 1; i <= 5; i++)
{
System.out.println("Run #" + i + "\n");
refCursorExample.run();
}

Thread.sleep(30000);

System.out.println("finished");
}
}

You can see in the run() method we don't call close on the statement itself which leads to a code leak.

finally
{
if (cursor != null)
{
  cursor.close();
}
}

2. In the project edit the default profile by double clicking on the project and selecting "Run/Deug/Profile" and then select "Edit" for default profile, then select "Tools Settings/Profiler" and select the checkbox "Begin Use Case on application Startup"

3. Press Ok twice

Now we are ready to memory profile the application. As ou can see from the code we call the method run() 5 times so lets see if we can see the Statement leak.

4. Select Run -> Memory profile

A snapshot is taken as soon as the program runs, and then it sleeps for 30 seconds before finally completing. Click on the icon "End task" and set the class filter to "T4CStatement"






5. You will see 2 columns , one called "New" the other one called "Final Num"

Both are 6 which shows that we have 6 "T4CStatement" classes still in the heap.

Now lets fix the code to actually call close() on the statement in the finally block of the run() mnethod as the 11g JDBC Developer's guide suggests to.

if (cstmt != null)
{
cstmt.close();
}

6. Now profile the class "RefCursorExample" again and you will see that we no longer have a build up of "T4CStatement" as previously examined, when the statement was closed as well.

We have "New" as 6 and "Final Num" as 1 as expected.