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.
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.
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.
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.
Go back to the JMeter and create a new thread group, which will monitor and control our testing.
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.
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.
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.
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.
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.
Now we have to add a constant throughput time which helps us to main a constant throughput through out our test window.
Here I had set the timer as 60 threads/minute (1 Thread/Sec).
Let us add the component that calls stored procedure. Add a JDBC request to the thread group and include stored procedure details in it.
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.
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.
We are all set here, lets start the execution and collect the test results.
Once the test is complete, listeners provide test summary and other execution details.
We can see the stored procedure invocation details (input values, output values etc.) also from the result tree screen.
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.