How to test OCI row prefetch count in Java with oraaccess.xml in Oracle 12.1

Credit: I have used and adapted Java program published on Greg Rahn blog .

what are OCI and row prefetch count ?

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:

  • connect to database instance
  • submit SQL statement that will be compiled in an execution plan (parse step)
  • execute the SQL statement (execute step)
  • retrieve SQL statement result set data (execute and fetch step)

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.

setup needed

On Oracle Linux 6.3:

  1. an Oracle 12.1 Entreprise Edition Oracle Home
  2. a non-container 12.1 database
  3. table emp in SCOTT schema has been modified to store dba_objects data:
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:

  • connect to database instance to SCOTT schema using JDBC thick driver
  • print JDBC driver version
  • print used row prefetch count according to JDBC API
  • run SQL statement “select ename from emp”
  • print time needed to retrieve all result rows
/*
   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:

  • ¬†takes as argument the row prefetch size to set in oraaccess.xml
  • ¬†generates oraaccess.xml with the specified row prefetch size from oraaccess.xml.template
  • prints the row prefetch size from oraccess.xml
  • runs rowPrefetch.java
#!/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

Test results

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.

About these ads
Post a comment or leave a trackback: Trackback URL.

Comments

  • Krishna  On February 25, 2014 at 10:25 am

    getRowPrefetch() may be returning what has been set/the default only through the JDBC interface (perhaps cached at JDBC layer). oraaccess.xml overwrites this at a lower layer and may not be seen by JDBC layer. Better to check with Oracle JDBC.

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: