Credit: I have used and adapted Java program published on Greg Rahn blog .
A database client that connects to an Oracle database instance will use Oracle Call Interface (OCI) unless it is using some JDBC thin driver. OCI allows to:
OCI communicates with database instance with client/server round trips: the number of rows retrieved by each round trip when retrieving result set data is named the row prefetch count and can be specified in some OCI API call. For example in Java you can use setDefaultRowPrefetch method for this purpose. If your database client is SQL*Plus you can use the SQL*Plus parameter ARRAYSIZE to change this parameter. The row prefetch count is also sometimes named the array interface.
Greg Rhan blog post shows how changing the row prefetch count can have a positive impact on application performance. Until 12.1 release it was not possible to change dynamically this parameter: i.e. it was needed to change application code using the right API call (unless your database client was SQL*Plus). Starting with 12.1 it is now possible to change this parameter using a new Oracle Client parameter file named oraaccess.xml.
This blog article shows how to use oraaccess.xml to change row prefetch count in Java.
On Oracle Linux 6.3:
SQL> drop table emp;
Table dropped.
SQL> create table emp as select object_name as ename from dba_objects;
Table created.
SQL> select count(*) from emp;
COUNT(*)
----------
90734
4. ojdbc6.jar JDBC 6 for Oracle 12.1 downloaded from OTN Oracle Database 12c Release 1 JDBC Driver Downloads
5. JDBC driver has been installed in /home/oracle/java
6. following rowPrefetch.java source file has been created in /home/oracle/java to:
/*
NAME
rowPrefetch.java
DESCRIPTION
this program takes no argument
NOTES
MODIFIED (MM/DD/YYYY)
grahn 04/28/2007 - Creation
http://structureddata.org
pforstmann 08/24/2013 - Adapted to Oracle 12.1
*/
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
public class rowPrefetch {
public static void main(String[] args) {
try {
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci8:@//oel6twsf:1521/db12c");
ods.setUser("scott");
ods.setPassword("tiger");
OracleConnection conn = (OracleConnection) ods.getConnection();
conn.setAutoCommit(false);
DatabaseMetaData meta = conn.getMetaData();
System.out.println(
"JDBC driver version:\t" + meta.getDriverVersion());
Statement stmt = conn.createStatement();
Integer batchSize = ((oracle.jdbc.OracleStatement)stmt).getRowPrefetch();
System.out.println("getRowPrefetch:\t\t" + batchSize);
long start1 = System.currentTimeMillis();
ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
while (rset.next()) {
rset.getString(1);
}
rset.close();
stmt.close();
long elapsedTimeMillis1 = System.currentTimeMillis() - start1;
// Get elapsed time in seconds
float elapsedTimeSec1 = elapsedTimeMillis1 / 1000F;
System.out.println("elapsed seconds:\t" + elapsedTimeSec1);
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
}
7. rowPrefetch.java is compiled using Oracle Home JDK:
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export CLASSPATH=/home/oracle/java/ojdbc6.jar $ORACLE_HOME/jdk/bin/javac rowPrefetch.java
8. oraaccess.xml.template file has been created in $ORACLE_HOME/network/admin with a placeholder named BS for row prefetch count:
$ cat /u01/app/oracle/product/12.1.0/db_1/network/admin/oraaccess.xml.template
<?xml version="1.0" encoding="ASCII" ?>
<!--
Here is a sample oraaccess.xml.
This shows defaulting of global and connection parameters
across all connections.
-->
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
schemaLocation="http://xmlns.oracle.com/oci/oraaccess
http://xmlns.oracle.com/oci/oraaccess.xsd">
<default_parameters>
<prefetch>
<rows>BS</rows>
</prefetch>
<statement_cache>
<size>100</size>
</statement_cache>
<tresult_cache>
<max_rset_rows>100</max_rset_rows>
<max_rset_size>10K</max_rset_size>
<max_size>64M</max_size>
</result_cache>
</default_parameters>
</oraaccess>
9. script rj.ksh allows to generate oraaccess.xml and run rowPrefetch.java in one go:
#!/bin/bash # # rj.ksh # if [ "$1" = "" ] then parm=10; else parm=$1 fi # TEMPLATE=$ORACLE_HOME/network/admin/oraaccess.xml.template ORAACCESS=$ORACLE_HOME/network/admin/oraaccess.xml sed s,'<rows>BS</rows>','<rows>'$parm'</rows>',g $TEMPLATE> $ORAACCESS TMP1=/tmp/xml1.$ TMP2=/tmp/xml2.$ grep '<rows>' $ORAACCESS> $TMP1 sed -e s,'<rows>',,g -e s,'</rows>',,g $TMP1 > $TMP2 BS=`cat $TMP2` echo "XML batch size: $BS" rm -f $TMP1 $TMP2 # export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export CLASSPATH=/home/oracle/java:/home/oracle/java/ojdbc6.jar $ORACLE_HOME/jdk/bin/java rowPrefetch
As expected we can see that changing row prefetch count has a positive impact on SQL execution time measured from client side:
$ ./rj.ksh 10 XML batch size: 10 JDBC driver version: 12.1.0.1.0 getRowPrefetch: 10 elapsed seconds: 0.582 $ ./rj.ksh 100 XML batch size: 100 JDBC driver version: 12.1.0.1.0 getRowPrefetch: 10 elapsed seconds: 0.367 $ ./rj.ksh 500 XML batch size: 500 JDBC driver version: 12.1.0.1.0 getRowPrefetch: 10 elapsed seconds: 0.343
But JDBC API always says that rowPrefetch is set to 10 ? I cannot explain the behaviour (I have very little JDBC skills): if you can explain this, please post a comment.