The following is a simple example that uses 'useFetchSizeWithLongColumn' on a long column where a the row prefetch is set to 15.
I confirm it returns 15 rows on each trip to the database by tracing the session and checking the trc file on the database.
My code looks at follows:
--------------------------------------------------------------------------------------------------------
package project1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleDriver;
public class TestFetchSizeWithLongColumn {
private Connection conn = null;
private Statement stmt = null;
private Statement altstmt = null;
private ResultSet rset = null;
final String traceSQL =
"select c.value || '/' || d.instance_name || '_ora_' || " +
"to_char(a.spid, 'fm99999') || '.trc' " +
"from v$process a, v$session b, v$parameter c, v$instance d " +
"where a.addr = b.paddr " + "and b.audsid = userenv('sessionid') " +
"and c.name = 'user_dump_dest'";
public static void main(String[] args) throws SQLException {
new TestFetchSizeWithLongColumn().run();
}
public void run() {
try {
Connection conn = getConnection();
altstmt = conn.createStatement();
stmt = conn.createStatement();
altstmt.execute("alter session set events '10046 trace name " +
"context forever, level 12'");
ResultSet rset =
stmt.executeQuery("select search_condition,rownum" +
"from dba_constraints");
altstmt.execute("alter session set events '10046 trace " +
"name context off'");
displayTraceFileName(conn);
// while (rset.next())
// System.out.println(rset.getInt(2));
} catch (SQLException sqle) {
//sqle.printStackTrace();
System.out.println("sqlexception");
} finally {
try {
if (rset != null)
rset.close();
if (stmt != null)
stmt.close();
if (altstmt != null)
altstmt.close();
if (conn != null)
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
System.out.println("fatal error");
}
}
}
public static Connection getConnection() throws SQLException {
String url =
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
"(HOST=sracanov-au2.au.oracle.com)(PORT=1522))" +
"(CONNECT_DATA=(SERVICE_NAME=orcl)))";
java.util.Properties props = new java.util.Properties();
props.setProperty("user", "system");
props.setProperty("password", "welcome1");
props.setProperty("defaultRowPrefetch", "15");
props.setProperty("useFetchSizeWithLongColumn", "true");
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, props);
return conn;
}
public void displayTraceFileName(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(traceSQL);
rset.next();
String fileName = rset.getString(1);
System.out.println("TRACE FILE NAME : " + fileName);
}
}
--------------------------------------------------------------------------------------------------------
Now run the class as follows:
[stever@STEVER-8500 C]$ java -version
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode)
[stever@STEVER-8500 C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn.java
[stever@STEVER-8500 C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn
TRACE FILE NAME : /u01/programs/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_7002.trc
[stever@STEVER-8500 C]$
The result returned is the location of my trace file on the database.
Upon checking the trace file, the following is shown:
=====================
PARSING IN CURSOR #1 len=51 dep=0 uid=5 oct=3 lid=5 tim=1221750605502677 hv=1856978345 ad='2bdf40f8'
select search_condition,rownum from dba_constraints
END OF STMT
PARSE #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502672
BINDS #1:
EXEC #1:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502850
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605502898
FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008
WAIT #1: nam='SQL*Net message from client' ela= 77131 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605807896
=====================
The line:
FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008
Contains 'r=15'. 15 rows are prefetched.
