Jmeter - Oracle Database Testing

Oracle Database Testing using Apache JMeter

Index      



  

About - Apache Jmeter Features Benefits Downloading Jmeter JMeter Components Preparing Test Plan • Thread Group Control • JDBC Connection Configuration control • JDBC Request Control • View Result Tree Control Parameterization • User Parameter Control • CSV Data Set Config Stored Procedure Execution Log File Measuring Response Time & Throughput

About Apache JMeter 



Apache JMeter is open source software, a 100% pure Java desktop application designed to load test functional behavior and measure performance. It was originally designed for testing Web Applications but has since expanded to other test functions. Apache JMeter may be used to test performance both on static and dynamic resources (files, Servlets, Perl scripts, Java Objects, Data Bases and Queries, FTP Servers and more). It can be used to simulate a heavy load on a server, network or object to test its strength or to analyze overall performance under different load types. You can use it to make a graphical analysis of performance or to test your server/script/object behavior under heavy concurrent load.

Features 

 

  

Can load and performance test many different server types: • Web - HTTP, HTTPS • SOAP • Database via JDBC • LDAP • JMS • Mail - POP3(S) and IMAP(S) Complete portability and 100% Java purity. Full multithreading framework allows concurrent sampling by many threads and simultaneous sampling of different functions by separate thread groups. Careful GUI design allows faster operation and more precise timings. Caching and offline analysis/replaying of test results. Highly Extensible: • Pluggable Samplers allow unlimited testing capabilities. • Several load statistics may be chosen with pluggable timers . • Data analysis and visualization plugins allow great extendibility as well as personalization. • Functions can be used to provide dynamic input to a test or provide data manipulation. • Scriptable Samplers (BeanShell is fully supported; and there is a sampler which supports BSF-compatible languages)

Benefits 







Allows to perform load testing of sql queries, stored procedure, triggers and functions at different volume sizes. Helps in data validation of sql queries, stored procedures. Helps in graphical monitoring and analysis of response time & throughput of the database objects under heavy concurrent load. Allows saving the results obtained in a file for analysis.

Download & Running JMeter 



Download the JMeter version 2.3.4 from the following url: • http://jakarta.apache.org/site/downloads/downloads_jmeter.cgi Run the jmeter.bat file from the JMeter installation directory.

JMeter Components Group

Purpose

Samplers

The requests to the servers are being sent by these elements for the request types SOAP, JDBC, "Java", HTTP/HTTPS, FTP.

Listeners

The results of the run can be saved by these elements in the single request, tabular forms etc by saving the results in a named file.

Timers Logic

To make the test more realistic we use Timers element to insert delays between the requests. Logic controllers are used if you have defined the request to be executed on some logic like if-then-else and loop structures in Java etc.

Configuration Common information about the requests is added to work with samplers using this element. Elements Assertions

This element is used to check whether you are getting the responses within a given amount of time containing the expected data or not.

Preparing Test Plan Thread Group Control 



Thread Group allows to run script with nth no. of users with defined ramp-up period and also the scheduling of execution. Right click on the Test Plan and add the Thread Group control.

Thread Properties: Allows to set the no. of users, ramp-up time and iterations

JDBC Connection Configuration control  



Used to configure the database i.e. Oracle, MS SQL Server etc. Add JDBC Connection Configuration control to the Test Plan. Database Connection Configuration • Database URL: jdbc:oracle:thin:@localhost:IP:service (e.g. IP: 1521, Service: oracle) • JDBC Driver Class: oracle.jdbc.driver.OracleDriver • Username: Username of the database (e.g. Username: scott) • Password: Password of the user (e.g. Password: tiger) Important: • Add the following Jars in JMeter Installation directory (D:\jakartajmeter-2.3.1\lib) 

classes12.jar & ojdbc14.jar

• These files are available in Oracle installation directory (C:\oracle\ora92\jdbc\lib\)

JDBC Request Control  

JDBC Request control allows to run a SQL query, procedure etc. Add the JDBC Request control below the Thread Group.

Query: type SQL Queries and stored procedure

View Result Tree control 



View Result Tree control is used to view the result of the query executed. Sampler Result: shows the load time, response Add View Result Tree control to the Test Plan. code etc..

Green: Request passed Red: Request failed

View Result Tree control

Request: shows the request

View Result Tree control

Response data: shows the output

Parameterization

User Parameters Control 

1.

User Parameters control allows the script to be run with multiple values like username, password, department no. etc. We can add variables and also provide values of the variable to multiple users. Add the User Parameters control to the Thread Group.

Name: variable name User_1: value of User_1 User_2: value of User_2

Parameterization User Parameters Control 2.

Pass the variable to the query or procedure. Syntax: ${variable_name} (e.g. ${Dept_No}) $: used to fetch the variable value

Sample Query: update dept set loc=‘India' where deptno=${Dept_No}

Parameterization CSV Data Set Config Control  

Allows parameterization using a file. Add the CSV Data Set Config control to the Thread Group.

Execution of Stored Procedure 



Add the JDBC Request control to the Thread Group. • Procedure must exist in database. Type the following command in SQL Query text box of the JDBC Request control begin{…};end; block: PL/SQL

begin statement should be enclosed within block {call update_DEPT_PKG.update_DEPT(10)}; end;

• •

This example takes the department no as input and doesn’t return anything begin…end block.

Stored Procedure must be written inside the Call keyword is used to call the stored procedure.

View Result Tree control

Response Message: Ok – if request passed else error message will be returned

Viewing SProc returning value  

Choose Callable Statement from Query Type combo box. Type the following command in SQL Query parameter of the JDBC Request control declare: used to declare variable(s)

This example takes the declare department no as input and return department name deptname varchar2(20); which we store in deptname begin variable {call update_DEPT_PKG1.update_DEPT1(${Dept_No},?)}; end;

 

Write ${deptname} in Parameter values text field. Write OUT VARCHAR in Parameter types text field. ${Dept_No}: IN variable – passing department no. to the SProc

?: store the OUT variable in variable $ {deptname} to verify the output

Viewing SProc returning value

Callable Statement: choose in case of PL/SQL request

Viewing SProc returning value

ACCOUNTING: output returned by SProc

Log file 



Create a file_name.jtl file to save log and provide the .jtl file path in File Name text box in any of the Listener (View Results Tree). Click on the configure button and choose the following checkbox: • Save Response Data (XML) 

Returns the output data

• Save Sampler Data (XML) 

Returns the executed sql query

• Save Response message (XML) 

Returns the error message (if returned)

Measure Response Time & Throughput Statistical Aggregate Report

Thanks!!!