Load Testing your Oracle database with Oracle Application Test Suite

I'm often asked if we can use Oracle Application Test Suite to load test other components that are not directly supported by OATS (Oracle Application Test Suite) and the simple answer is yes we can!!! I have load tested Telnet systems, FTP systems, Databases and even native C++ libraries in a Linux environment using JNA (Java Native Interface).

Oracle Application Test Suite has a module called Basic (Java Script Module) which has no specific support for anything, it's just a java class with 3 empty methods, Initialize, Run and Finish, which you can iterate with any number of users as many times as you like inside OLT (Oracle Load Test). We can populate this empty methods with any Java code we wish, and we can also create new classes and add external JAR files.

The following is a guide of using OATS to load test the Oracle Database using the basic module.

First if you do not have OATS you can download an evaluation version from Oracle OTN

To follow this guide you will need to have installed an Oracle database and downloaded the Oracle Thin driver.

Once OpenScript has been installed we will need to launch it, then create a new Basic Script

File -> New -> Java Code Script (Basic Module)
step01.JPG

Next we will need to select a workspace and enter a new name, I have selected Oracle Database load test as the script name.

Once the base script is created we will need to add 3 steps, and we will let the GUI take care of that:

Expand the node next to the script name to show the Initialize, Run and Finish methods, Right Click on initialize, select Add -> Step Group, enter Create Connection as the step name.
b1step3.JPG

Repeat the same steps to add 2 more groups, one at Run called Execute Query and another at Finish called Close Connections

So far we have created a Java Basic Script and added 3 step groups. We have one at Initialize which will report the time that it took to Initialize the connection, another at Run which will execute a query multiple times during run time and report the duration of the query, and another one at Finish which will report the time that spent to close the database connection.

Now its time to add some code to this script, so let's move from the Tester Perspective to the Developer Perspective

View -> Developer Perspective

Next Click on the Java Script tab next to the Tree View Tab to display the Java Code that we need to edit.

To follow standard Java practice we will create a new class that will handle the database logic:

Right Click on the Project Name and select New -> Class
b1step4.JPG

Enter ConnectionFactory as the name of the Class and select Finish to create the class, the read and copy the code below into the newly created class:



import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;
import java.sql.ResultSet;

public class ConnectionFactory {

  private String host = "db_host";
  // define the database port number
  private String port = "db_port";
  //Define the SID of the database
  private String SID = "db_sid";
  // Define the username for the database
  private String user = "db_username";
  // Define the password for the database
  private String password = "db_password";
  // Define a connection
  private Connection dbConn;

  // Method to open a database connection
  public Boolean dbConnect() {
    try {
    // Create a connection
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    dbConn = DriverManager.getConnection("jdbc:oracle:thin:@"
       + host + ":" + port + ":" + SID, user,password);
    return true;
    } catch (SQLException e) {
    System.err.print(e);
    return false;
    }
  }

  // Method to excecute a SQL Query
  public ResultSet exQuery(String query) {
    Statement stmt;
    // ececute the SQL Query
    try {
    stmt = dbConn.createStatement();
    ResultSet rset = stmt.executeQuery(query);
    return rset;
    } catch (SQLException e) {
    System.err.print(e + "\n" + query);
    return null;
    }
  }

  // Method to close a connection
  public Boolean dbClose() {
  try {
    // Close the connection
     dbConn.close();
    ;
    } catch (Exception e) {
    System.err.print(e);
    return false;
     }
  }

}


Ok so we now have a class that will create and execute our SQL Statements, next we need to integrate this class with our script, to do so we will edit the java code in the script class. Below is the code in the script class; note the code in blue is the code that was generated by OpenScript and the code in red the code that we added to the script.





import oracle.oats.scripting.modules.basic.api.*;
import java.sql.ResultSet;
public class script extends IteratingVUserScript {

  ConnectionFactory cf = new ConnectionFactory();
  private ResultSet rs;
  private Boolean success;


  public void initialize() throws Exception {

    // The initialse method we will only create the connection.
    beginStep("Create Connection", 0);
    {

    success = cf.dbConnect();
  if (!success)
      // Log an error in the OLT database and VU display
    fail("Connection Error");

    }
    endStep();
  }

  /**
  * Add code to be executed each iteration for this virtual user.
  */
  public void run() throws Exception {
    beginStep("Excecute Query", 0);
    {


    // exequte the query
    rs = cf.exQuery("select * from v$version");
    if (rs != null){
    // Print the contents to a log file.
    while (rs.next()) {
      getLogger().info(rs.getString(1));
      }
    } else {
      fail("Unable to excecute Query");
    }

    }
    endStep();
  }

  /**
  * Add any cleanup code or perform any
  * operations after all iterations are performed.
  */
  public void finish() throws Exception {
    beginStep("Close Connections", 0);
    {


    success = cf.dbClose();
    if (!success)
      fail("Error during database close connection"

    }
    endStep();
  }

  public static void main(String[] args) throws Exception {

  }
}



Finally we will need to add the classes12.jar file to the MANIFEST.MF file so its ready for deployment under OLT

First lets copy the jar file into the script directory, on my system that will be:

C:\OracleATS\OFT\RSWDemo!\Oracle Database Load Test

Please note that this may be different in your system, but it will always have this format:

path_to_repository\repository\workspace\script_name

Back in OpenScript in the Navigator pane double click on the META-INF folder, then double click on the MANIFEST.MF file, change views by clicking on the MANIFEST.MF tab at the bottom of the window, to show the following file:
b1step5.JPG

We will need to add this line Bundle-ClassPath: classes12.jar,. at the end of the file. Note the dot . is very important.

The file should look like this:
b1step6.JPG

We are finally ready to test the script, lets go back to the Tester Perspective:
View -> Tester Perspective
And Click on the PlayBack icon or selectScript PlayBack look at the results pane you should see the step name and the message Passed

Now the script is ready to be executed from OLT!!!

Comments:

My teacher suggested this site, and she is totally right keep up the terrific work!

Posted by microsoft firewall on January 14, 2010 at 10:07 AM GMT #

ba5WfW I am always excited to visit this blog in the evenings.Please churning hold the contents. It is very entertaining.

Posted by Silvia on February 17, 2010 at 05:56 AM GMT #

Just thought i would review and also say neat theme, did you code it yourself? Looks great.

Posted by Johnny Rosetta on April 10, 2010 at 06:12 PM BST #

Hi Amat, The above article is really nice. I found it's really helpful for me. could you please guide me how can we connect to unix box and search the file in that system using openscript. is there any utility classes for achieving this?

Posted by Ashok on July 29, 2010 at 03:37 AM BST #

Hi administrator I wallow in w/ ur content . can i copy this knowledge for my academic check ? thanks

Posted by Faustina Krauth on August 16, 2010 at 08:54 PM BST #

Please by all means do copy this code.

Posted by Alex on August 18, 2010 at 02:32 AM BST #

apologies for being a noob - but I can't get this script to work for me. I've installed it on my site about Cairns Info but it just doesn't seem right. Does anyone want to take a look and let me know what I'm doing wrong? My page uses a fairly simple css if that helps.

Posted by Marty Cairns on September 01, 2010 at 03:28 AM BST #

Hi If you are looking for help in OpenScript your best bet is to go the OTN you can find the ATS forum here: http://forums.oracle.com/forums/forum.jspa?forumID=550 Alex

Posted by Alex on September 01, 2010 at 03:36 AM BST #

this sounds like the way we do it in Cairns as well.

Posted by Larita Canonica on September 14, 2010 at 06:41 AM BST #

Hi Alex, I have a 3000 records in my script and i would like to run this script in a controller so that each virtual user will take unique record. What i mean, V1 user should take the first record and V2 should take the second record. How can i set the settings in the OLT - Databank Controller? Can you please help me out? Thanks, Shyam Kunti

Posted by Shyam Kunti on January 31, 2011 at 07:10 AM GMT #

Hi Alex, I have a 3000 records in my script and i would like to run this script in a controller so that each virtual user will take unique record. What i mean, V1 user should take the first record and V2 should take the second record. How can i set the settings in the OLT - Databank Controller? Can you please help me out? Thanks, Shyam Kunti

Posted by Shyam Kunti on January 31, 2011 at 07:21 AM GMT #

The fail("Connection Error"); is good. But how do you send metrics (eg server time) to the OLT-report.

Posted by guest on March 12, 2011 at 01:29 AM GMT #

Hi any code within the beginStep("" 0); endStep(); methods will be picked up by OLT and displayed in the OLT-Report.

Posted by Alex on March 23, 2011 at 08:11 AM GMT #

There is a GetNextDatabankRecord call in the script which by default is in the run section, just move that into initialize and that should do the trick.

Posted by Alex on March 23, 2011 at 08:13 AM GMT #

What is classes12.jar and where do I get that jar file?

Posted by guest on May 09, 2012 at 01:24 PM BST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today