Sample demonstrating usage of ROW PRE-FETCH

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This application illustrates the use of row prefetching performance extension. It gives a comparison between querying using three different values of row-prefetch:
- The default value
- Row-Prefetch value of 100
- Row-Prefetch value of 200

The samples queries for the PRODUCT_ID in the PRODUCTS table and the execution time for each prefetch value will be displayed in the corresponding text fields.

Working of the Sample

• When you invoke the application, it connects to the OE/OE user of Oracle9i database sample schema. The status and errors, if any, will be displayed in the status bar.
• Press "DEFAULT ROW PRE-FETCH" Button to see the execution time taken for querying using the default values of Row-Prefetch.
• Press "WITH ROW PRE-FETCH=100" Button to see the execution time taken for querying using Row-Prefetch value 100.
• Press "WITH ROW PRE-FETCH=200" Button to see the execution time taken for querying using Row-Prefetch value 200.

Here is the code where the data is reterived by setting ROW PRE-FETCH value. You can find more details of the code in RowPrefetchSample.java file under src/oracle/otnsamples/jdbc/prefetch folder. Look into Description of Sample Files section for folder and file details.

public void prefetchRows(int prefetchValue) { .............. .............. // The SQL query for selecting all rows from PRODUCTS table String query = "Select product_id from products"; // Create a Statement to execute query Statement stmt = connection.createStatement(); // Casting the Statement prepare to OracleStatement // and setting the Row Pre-fetch value as prefetchValue ((OracleStatement)stmt).setRowPrefetch(prefetchValue); // Obtain time at the start of retrieval long startTime = System.currentTimeMillis(); // Execute the Query in query ResultSet rst = stmt.executeQuery(query); // Populating the ResultSet Object while(rst.next()) { numFetchedRows++; } // Obtain time at the end of retrieval long endTime = System.currentTimeMillis(); // Compute time taken timetaken = endTime-startTime; ..................... ......................

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided RowPrefetchSample.jar using the following command 

> jar xvf RowPrefetchSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder RowPrefetchSamplewith all the source files

• Edit RowPrefetchSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to OE/OE user of Oracle9i database sample schema.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the RowPrefetchSample.jws from the RowPrefetchSample directory.
• Next, select Project/RowPrefetchSample.jpr from main menu.
• Now, select Run/Run RowPrefetchSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from RowPrefetchSampledirectory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\RowPrefetchSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\RowPrefetchSample> set JAVA_HOME=d:\jdk1.3.1
D:\RowPrefetchSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar. 
• Also add RowPrefetchSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\RowPrefetchSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\RowPrefetchSample;.
  
• From the directory RowPrefetchSample\src\oracle\otnsamples\jdbc\prefetch, compile all the java files using javac:
  Example: 
  D:\RowPrefetchSample\src\oracle\otnsamples\jdbc\prefetch>javac -d . *.java
  
• Run the class file using java from  RowPrefetchSample\src\oracle\otnsamples\jdbc\prefetch directory
  Example:
  D:\RowPrefetchSample\src\oracle\otnsamples\jdbc\prefetch>
  java oracle.otnsamples.jdbc.prefetch.RowPrefetchSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from RowPrefetchSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to RowPrefetchSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar.
• RowPrefetchSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/RowPrefetchSample:.
• From the directory RowPrefetchSample/src/oracle/otnsamples/jdbc/prefetch, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same RowPrefetchSample/src/oracle/otnsamples/jdbc/prefetchdirectory.
  Example:
  $java oracle.otnsamples.jdbc.prefetch.RowPrefetchSample