Load Testing Oracle Stored Procedures Using Apache JMeter

Last week I was asked to load test few oracle stored procedures created by another team. These stored procedures are invoked by java services and the delivery team wanted to test them in the performance environment before pushing them to production. I was asked to use an Open Source tool for this testing since the project doesn’t have budget to buy a commercial tool.

Since Apache JMeter is a free tool, we decided to use it for our testing. The stored procedure takes one INTEGER value as input and returns an INTEGER value and a cursor as out put. The input value came from a CSV file which has 100K records that we mined from the database. I have detailed the steps we used to create a database test plan, our biggest challenge was to receive cursor output from the stored procedure. Details on how we achieved this can be found below. I have also explained how you can download the necessary tools and configure them.

**Please note that I have simulated below tests in my laptop since the actual code belongs to my client. I have no authority to publish the actual code here, but the configurations used in both cases are same. **

First, Lets download the latest version of Apache JMeter.

http://jmeter.apache.org/download_jmeter.cgi

jmeter_download_page

JMeter needs a JDBC driver to connect to oracle database, You can find the JDBC connect for Oracle at the Oracle Technology Network (OTN) website.

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

jdbc_driver_download

First, download JMeter and unzip to the folder where you want to save it. Go to /bin folder and you can find windows batch executable (jmeter.bat), double-click on it and it will start the JMeter GUI. If you are in Linux there is a jmeter.sh script in the bin directory which invokes jmeter UI.

Now, download the JDBC driver from OTN and move the .jar file to /lib folder. It will be automatically detected and used by JMeter.

You can also download a lot of JMeter plugins, which will make your testing easy and will help you present your results in beautiful graphs.

Go to the JMeter UI.

jmeter_home

I had created a stored procedures in my local oracle instance, which will be tested by JMeter. This stored procedure accepts number value as input and return a number and a cursor.

stored_proc_code

Go back to the JMeter and create a new thread group, which will monitor and control our testing.

create_thread_group

Change the thread group name, you can give whatever name you want. Here I gave “OracleLoadTest_Sproc” for my thread group. Also we can set the number parallel threads and ramp up period in this screen.

thread_grp_details

After this we have to configure our database connection. JMeter can connect to Oracle database using a JDBC driver.

Right click on thread group and add a new JDBC connection configuration.

db_connection_1

Now configure the connection settings, Here I have specified the server name as localhost since it is installed on my laptop. You can find your DB server details in TNSNAMES.ORA file or can get it from your DBA. You also have to set your database username and password for establishing the connection.

db_connection_2

Our input data is coming from a CSV file and we usually test with 100K input fields. Here I have used a small file just to make sure the setup is working.

csv_config1

You have to configure the source file name and column names here. These column names are used later as the input parameter for the stored procedure.

csv_config2

Now we have to add a constant throughput time which helps us to main a constant throughput through out our test window.

throughput_timer_1

Here I had set the timer as 60 threads/minute (1 Thread/Sec).

throughput_timer_2

Let us add the component that calls stored procedure. Add a JDBC request to the thread group and include stored procedure details in it.

sproc_jdbc_call_1

We have to set the query type as Callable Statement and use the CALL command to invoke the stored procedure. We can also pass the values from CSV file to the stored procedure. Just to make sure the type for cursor variable is-10 for the stored procedure.

sproc_jdbc_call_2

Now add few listeners which monitor the execution and summarize the output. This will help us to present the final results more clearly to the client. You can add as many listeners as you want, We are adding just two here.

summary_report

summary_report_final

resultstree

We are all set here, lets start the execution and collect the test results.

test_start

Once the test is complete, listeners provide test summary and other execution details.

summary_report_final

We can see the stored procedure invocation details (input values, output values etc.) also from the result tree screen.

sproc_output_2

sproc_output_1

One important factor is JMeter is a Java application and could use a lot of heap memory. By default JMeter 3.1 uses 512 MB of heap memory. You will definitely need more data to run tests in GUI mode. Edit the jemeter.bat using any good text editor like UltraEdit and add more memory based on your hardware configuration.

You can run JMeter from the command prompt if you have a memory constraint and save the results to a jtl file.

Advertisements

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