Oracle AWR report generation using Jmeter

1. Add  "Thread group " and then add the "JDBC Connection Configuration" from the "Config Element" as shown below



2. Provide the Data base connection configuration info

       Database URL: jdbc:oracle:thin:@DB_host_Name:port:Service_Name
                          Ex:  jdbc:oracle:thin:@192.168.200.90:1521:test_instance
       JDBC Driver class: oracle.jdbc.driver.OracleDriver
       User Name: username of schema
      Password :Password of schema


 3. Add "Simple controller" to the "Thread group" and rename as "Begin Snapshot"



4. Add "Constant timer" as child to the "Simple controller (Begin Snapshot). Please see below

Don't Change the "Delay time " , Keep default value as same (300ms)





5. Add "JDBC Request as child to the "Simple Controller" as shown  and rename as "Begin Snapshot"





6. Finally The tree view of Jmeter looks like below



7. In JDBC request (Begin Snapshot), add the shown code, below is the Query that has to be copied to SQL Query area shown in below screenshot

select dbms_workload_repository.create_snapshot from dual 




8. Perform Step 3 (Add "Simple controller" to the "Thread group" and rename as "End Snapshot")
9. Perform Step 4 and change the "Thread Delay" value according to your requirement, please see more details below




10. Performance Step 5 and rename as "End Snapshot"
11.  In JDBC request (End Snapshot), add the shown code, below is the Query that has to be copied to SQL Query area shown in below screenshot

select dbms_workload_repository.create_snapshot from dual

12.  Finally the tree structure look like this



Following steps are very import to generate "awr report"

13. Add "Simple controller" to the " Thread group" and rename as "Generate Awr Report"
14.Add "Constant timer" as child to "Generate Awr Report" simple contoller
    Note : Don't change the "Delay time " in the "Constant timer"

15. Add the "JDBC Request" Sampler (rename as Get_Database_ID) to the "Generate Awr Report" Controller and enter the query and variable value as shown below


 16. Add the "JDBC Request" Sampler (rename as Generate_awr_Report) to the "Generate Awr Report" Controller and enter the query and Results variable value as shown below

17. Add the "BSF sample" as a child to  the "Generate Awr Report" Controller and Select "Script Language as "Beanshell" and write the code in script area

Final tree view look like this




  Please download the JMX file here https://app.box.com/s/z0yz36krdbf58cc6ybo4 , just chage the "Data base details" in "JDBC Connection Configuration"  and run the script