Tune the Oracle for Performance improvements during Performance testing

Oracle data base Performance improvements during Performance testing.

Let’s assume an instance that you observed multiple execution plans, during performance testing of your application SQL’s, wondering why & how to resolve?  Here I will try to explain why oracle SQL will have multiple execution plans and how it will impact overall performance (response times of your transactions, server throughput, etc...) of your data base.

What is execution plan?

When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row, what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins, hash joins, star joins, and sort merge join methods). These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.

The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics. To always choose the best execution plan for a SQL query, Oracle relies on information about the tables and indexes in the query.

Once the optimizer has done its job, it provides an execution plan to Oracle. An execution plan is like a set of instructions that tells Oracle how to go and get the data

What is database gather stats and why should I do gather stats frequently on my data base?

Oracle has its own feature to generate the statistics on each table and indexes, CBO (cost based optimizer uses this statistics and make better execution plans for your SQL query so that SQL can be executed quicker).

We should gather stats on data base as and when there are major changes on data base like production data was populated on your test data base, major changes happened to tables and indexes, etc… that time we should gather stats so that better execution plans can be created by CBO. So that we will have better performance.

How to gather data base stats?

Oracle provides a stored procedure (or program) for you to run that will generate the statistics is needs.

Example:

declare

Begin

DBinstancename.wcsgather_Stats('Tablename');

End;

Example2:

From SQL Developer, check when table’s statics are gathered, click on the table. You will be able to see statistics tab.  Check when is last analyzed. Make sure tables are analyzed when there are major changes on your test data base.

 If you want to gather stats how?

Just run the above procedure or from SQL developer below are the steps.

From OEM (Oracle enterprise manager), we can monitor where your SQL has any multiple execution plans.

Snapshot from OEM for Multiple execution plans found for an SQL.

So if you see like this you should first check whether gather stats are done properly, if yes and still you see multiple execution plans than start analysis from SQL perspective.

A snapshot for without any multiple execution plans:

 So finally what I wanted to share on this post is , gather stats are important on database as it will affect your performance test results in terms of SQL speed.

If SQL is retrieving the data slowly because of bad execution plans obviously your transactions response times will effect.

In the next post, I will share more on AWR, ADDM and ASH reports, how to analyze bad SQL’s and fix… wait for my next post J  

Web service Performance Testing using Load runner

This post explains about the performance test scripting for web service without recording.

we have various methods to test the web service performance testing in load runner it self , by using web service protocol.

More feasible way is by using web custom request , this would facilities more reliable scripting.

Taken  weather report as an example for load runner scripting  (Sample SOAP requests are available on below site).

http://www.webservicex.com/globalweather.asmx.

1) Choose Web(HTTP/HTML) protocol and Don't record , put the below simple code will serve the purpose.

Action()
{

   char *request_xml;



   // save web service url to param
    char *URL = "http://www.webservicex.com/globalweather.asmx";
    lr_save_string(URL, "URL_Param");


 // save xml request to param
 request_xml=
  "<?xml version=\"1.0\" encoding=\"utf-8\"?>"
  "<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">"
    "<soap:Body>"
     "<GetWeather xmlns=\"http://www.webserviceX.NET\">"
    "<CityName>Seattle</CityName>"
    "<CountryName>United States</CountryName>"
     "</GetWeather>"
    "</soap:Body>"
  "</soap:Envelope>";

 lr_save_string(request_xml, "REQUEST_XML_PARAM");

 // add http headers

 web_add_header("Host", "www.webservicex.com");


 web_add_header("Content-Type", "text/xml; charset=utf-8");


 web_add_header("SOAPAction", "http://www.webserviceX.NET/GetWeather");



         // validate response
 web_reg_find("Text=SEATTLE-TACOMA INTERNATIONAL  AIRPORT , WA, United States (KSEA)", LAST);

 //SEATTLE-TACOMA INTERNATIONAL AIRPORT , WA, United States
 //
 //
 // send request
 //

 lr_start_transaction("post_xml");

 //  This is to capture the Temperature of the "Seattle" City.


 web_reg_save_param("ResponseBody",
    "LB=Temperature",
    "RB=/Temperature",
    "Search=Body",
    "IgnoreRedirections=Yes",
    LAST);




  web_custom_request("post_to_http_jms_provider",
   "URL={URL_Param}",
   "Method=POST",
   "TargetFrame=",
   "Resource=0",
   "Referer=",
   "Mode=HTTP",
   "Body={REQUEST_XML_PARAM}",
   LAST);


  lr_output_message("Temp is<>: %s",lr_eval_string("{ResponseBody}"));





 lr_end_transaction("post_xml", LR_AUTO);
}


Out put of the script execution:

with extended log enabled on load runner script.

Virtual User Script started at : 2014-04-04 20:17:00
Starting action vuser_init.
Web Turbo Replay of LoadRunner 11.0.0 for Windows 7; build 8825 (Aug 15 2010 23:38:14)   [MsgId: MMSG-27143]
Run Mode: HTML   [MsgId: MMSG-26000]
Run-Time Settings file: "C:\Users\\AppData\Local\Temp\noname1\\default.cfg"   [MsgId: MMSG-27141]
Ending action vuser_init.
Running Vuser...
Starting iteration 1.
Starting action Action.
Action.c(10): Notify: Saving Parameter "URL_Param = http://www.webservicex.com/globalweather.asmx".
Action.c(25): Notify: Saving Parameter "REQUEST_XML_PARAM = <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><GetWeather xmlns="http://www.webserviceX.NET"><CityName>Seattle</CityName><CountryName>United States</CountryName></GetWeather></soap:Body></soap:Envelope>".
Action.c(29): Warning -26593: The header being added may cause unpredictable results when applied to all ensuing URLs. It is added anyway   [MsgId: MWAR-26593]
Action.c(29): web_add_header("Host") highest severity level was "warning"   [MsgId: MMSG-26391]
Action.c(32): Warning -26593: The header being added may cause unpredictable results when applied to all ensuing URLs. It is added anyway   [MsgId: MWAR-26593]
Action.c(32): web_add_header("Content-Type") highest severity level was "warning"   [MsgId: MMSG-26391]
Action.c(35): web_add_header("SOAPAction") was successful   [MsgId: MMSG-26392]
Action.c(40): Registering web_reg_find was successful   [MsgId: MMSG-26390]
Action.c(48): Notify: Transaction "post_xml" started.
Action.c(53): Registering web_reg_save_param was successful   [MsgId: MMSG-26390]
Action.c(63): Notify: Parameter Substitution: parameter "URL_Param" =  "http://www.webservicex.com/globalweather.asmx"
Action.c(63): Notify: Parameter Substitution: parameter "REQUEST_XML_PARAM" =  "<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><GetWeather xmlns="http://www.webserviceX.NET"><CityName>Seattle</CityName><CountryName>United States</CountryName></GetWeather></soap:Body></soap:Envelope>"
Action.c(63): t=763ms: 277-byte response headers for "http://www.webservicex.com/globalweather.asmx" (RelFrameId=1, Internal ID=1)
Action.c(63):     HTTP/1.1 200 OK\r\n
Action.c(63):     Cache-Control: private, max-age=0\r\n
Action.c(63):     Content-Type: text/xml; charset=utf-8\r\n
Action.c(63):     Content-Encoding: gzip\r\n
Action.c(63):     Vary: Accept-Encoding\r\n
Action.c(63):     Server: Microsoft-IIS/7.0\r\n
Action.c(63):     X-AspNet-Version: 4.0.30319\r\n
Action.c(63):     X-Powered-By: ASP.NET\r\n
Action.c(63):     Date: Fri, 04 Apr 2014 14:47:04 GMT\r\n
Action.c(63):     Content-Length: 725\r\n
Action.c(63):     \r\n
Action.c(63): t=813ms: 725-byte ENCODED response body received for "http://www.webservicex.com/globalweather.asmx" (RelFrameId=1, Internal ID=1)
Action.c(63): t=817ms: 1109-byte DECODED response body for "http://www.webservicex.com/globalweather.asmx" (RelFrameId=1, Internal ID=1)
Action.c(63):     <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.or
Action.c(63):     g/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://
Action.c(63):     www.w3.org/2001/XMLSchema"><soap:Body><GetWeatherResponse xmlns="http://www.webserviceX.NE
Action.c(63):     T"><GetWeatherResult>&lt;?xml version="1.0" encoding="utf-16"?&gt;\r\n
Action.c(63):     &lt;CurrentWeather&gt;\r\n
Action.c(63):       &lt;Location&gt;SEATTLE-TACOMA INTERNATIONAL  AIRPORT , WA, United States (KSEA) 47-27N
Action.c(63):     122-19W 136M&lt;/Location&gt;\r\n
Action.c(63):       &lt;Time&gt;Apr 04, 2014 - 09:53 AM EDT / 2014.04.04 1353 UTC&lt;/Time&gt;\r\n
Action.c(63):       &lt;Wind&gt; from the S (190 degrees) at 12 MPH (10 KT):0&lt;/Wind&gt;\r\n
Action.c(63):       &lt;Visibility&gt; 10 mile(s):0&lt;/Visibility&gt;\r\n
Action.c(63):       &lt;SkyConditions&gt; mostly cloudy&lt;/SkyConditions&gt;\r\n
Action.c(63):       &lt;Temperature&gt; 44.1 F (6.7 C)&lt;/Temperature&gt;\r\n
Action.c(63):       &lt;DewPoint&gt; 39.9 F (4.4 C)&lt;/DewPoint&gt;\r\n
Action.c(63):       &lt;RelativeHumidity&gt; 85%&lt;/RelativeHumidity&gt;\r\n
Action.c(63):       &lt;Pressure&gt; 29.94 in. Hg (1013 hPa)&lt;/Pressure&gt;\r\n
Action.c(63):       &lt;Status&gt;Success&lt;/Status&gt;\r\n
Action.c(63):     &lt;/CurrentWeather&gt;</GetWeatherResult></GetWeatherResponse></soap:Body></soap:Envelope
Action.c(63):     >
Action.c(63): Notify: Saving Parameter "ResponseBody = &gt; 44.1 F (6.7 C)&lt;".
Action.c(63): Registered web_reg_find successful for "Text=SEATTLE-TACOMA INTERNATIONAL  AIRPORT , WA, United States (KSEA)" (count=1)   [MsgId: MMSG-26364]
Action.c(63): web_custom_request("post_to_http_jms_provider") was successful, 725 body bytes, 277 header bytes   [MsgId: MMSG-26386]
Action.c(74): Notify: Parameter Substitution: parameter "ResponseBody" =  "&gt; 44.1 F (6.7 C)&lt;"
Action.c(74): Temp is<>: &gt; 44.1 F (6.7 C)&lt;
Action.c(80): Notify: Transaction "post_xml" ended with "Pass" status (Duration: 1.2061 Wasted Time: 0.6257).
Ending action Action.
Ending iteration 1.
Ending Vuser...
Starting action vuser_end.
Ending action vuser_end.
Vuser Terminated.

Another advantage of this process is if we want to validate the response of web service and customize it , we have lr_xml functions ( refer LR help).

example : lr_xml_get_values().

Performance Monitoring Tools ( Unix/Linux commands )- Server Performance Stats

Monitoring the servers during performance testing is very important and useful to find out the actual bottleneck on any specific server, now a day's most of our applications runs on Non-windows machines ( Unix /Linux , etc..) hence we should know few commands to quick monitoring , I felt below are the bare minimum commands to monitor the performance of server.

By using Perfmon we can monitor WINDOW's based servers but not Non-Windows based machines, here are few, also if we have a facility of having Monitoring tools like CA APM wily , Site scope . Dyna trace , etc.... in our testing environment that's good , it will reduce this overhead.  But if you don't have that facility below commands will surely help.

Unix/Linux Commands for Performance Monitoring:

• vmstat
• sar
• iostat
• mpstat
• free
• top
• netstat
• ps

vmstat :

vmstat reports virtual memory statistics. The following are some of vmstat command examples.

$ vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----                                                                                        -
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0    332 1587232 106808 4368288    0    0   266 11974    1    7 12  6 78  5    

if you use vmstat 60 120  ,  120 reading will be taken for every 60 secs ( so for 2 hrs load test you can use this)

if you want have vmstat stats with time stamp use below command.


vmstat 3 5 | sed "s/$/$(date)/"

$ vmstat 3 5 | sed "s/$/$(date)/"
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------Wed Mar 26 14:32:28 UTC 2014
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa stWed Mar 26 14:32:28 UTC 2014
 2  0    332  44600 118912 6070464    0    0   266 11974    1    7 12  6 78  5  0Wed Mar 26 14:32:28 UTC 2014
 1  0    332  44020 119104 6061464    0    0    24 107865 2775 5262 16  7 74  4  0Wed Mar 26 14:32:28 UTC 2014


Get the "id" values (-----cpu------ Column) which is ideal time of CPU , 100-id value will give the CPU utilization.

From Above example : id value is 78 and 74, so 22 and 26 are CPU utilizations.

Sar

Using sar utility you can do two things: 1) Monitor system real time performance (CPU, Memory, I/O, etc) 2) Collect performance data in the background on an on-going basis and do analysis on the historical data to identify bottlenecks.

Sar is part of the sysstat package:

Example :

 sar 1 1
Linux 2.6.18-371.4.1.el5 (HostName)    03/26/2014

02:36:08 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle
02:36:09 PM       all     17.48      0.00      6.24      5.12      0.00     71.16
Average:          all     17.48      0.00      6.24      5.12      0.00     71.16

iostat:

iostat reports CPU, disk I/O, and NFS statistics

Iostat without any argument showes CPU usage, and I/O statistics about all the partitions on the system as shown below:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          11.84    0.00    5.79    5.01    0.00   77.36

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
cciss/c0d0       16.41        52.06       365.14   59021735  413998136
cciss/c0d0p1      0.00         0.00         0.00       5262         16
cciss/c0d0p2     16.41        52.05       365.14   59016129  413998120
sda             156.52       704.20     19325.03  798415632 21910654896
sda1            156.52       704.19     19325.03  798404688 21910654896


To execute every 5 seconds for a total of 10 times, do the following

iostat 5 10

mpstat:

By default mpstat displays CPU statistics as shown below:

11:56:10 AM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
11:56:10 AM  all   11.84    0.00    4.44    5.01    0.06    1.29    0.00   77.36   2913.01


$ mpstat -P ALL

Option -P ALL, displays all the individual CPUs (or Cores) along with its statistics as shown below.

1:55:16 AM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
11:55:16 AM  all   11.84    0.00    4.44    5.01    0.06    1.29    0.00   77.36   2913.03
11:55:16 AM    0   12.49    0.00    3.96    3.52    0.00    1.31    0.00   78.72   1985.54
11:55:16 AM    1    9.64    0.00    4.73    9.93    0.24    4.80    0.00   70.65    496.52



To display statistics information of a particular CPU (or core) than use below
$ mpstat -P 3 ( means for 3rd core CPU stats ).

TOP:

This command will show us, which proccess is consuming more CPU and memory , this will be usefull to quickly check.

top - 12:08:17 up 13 days,  3:12,  5 users,  load average: 2.24, 1.94, 1.86
Tasks: 342 total,   3 running, 336 sleeping,   3 stopped,   0 zombie
Cpu(s): 15.9%us,  4.6%sy,  0.0%ni, 70.3%id,  7.4%wa,  0.1%hi,  1.7%si,  0.0%st
Mem:   8176252k total,  8132220k used,    44032k free,    87712k buffers
Swap:  4194288k total,      332k used,  4193956k free,  5941952k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16502 wasadm    23   0  390m 189m  21m S 96.3  2.4   1:03.30 java
10856 root      10  -5     0    0    0 S 15.5  0.0   2119:55 kmirrord
 8860 wasadm    14  -1 68724 1532  340 R  5.3  0.0   0:38.07 rsync
16363 wasadm    14  -1 70520 1564  340 S  5.0  0.0   0:03.01 rsync
18964 wasadm    14  -1 68468 1508  336 S  5.0  0.0   0:00.65 rsync

If you want to re-direct the output of any of these commands use  "  > filename ".

Example :

vmstat 60 120 > Sampleyloadtest.txt   ( Sampleyloadtest.txt file will be created on current directory and output will be saved for future analysis ).

Netstat:

The netstat command is a Command Prompt command used to display very detailed information about how your computer is communicating with other computers or network devices.

Specifically, the netstat command can show details about individual network connections, overall and protocol-specific networking statistics, and much more, all of which could help troubleshoot certain kinds of networking issues

For example:

netstat -an | head -10  -> will give top 10 connection details


netstat -an | grep "CONNECTED"  -> will give all connected status network details


PS

ps command will give us which process running on that server , will would help to find out process details on server.

most useful command is

ps -aef | grep java  [ will give all java process running on that server ].

Java Memory Leaks-Performance testing

My following posts explains about How to find Java memory leaks during performance testing , for now this post will explain about what is Java memory leak and how Java memory management will be ?

1.          About Java Memory Leak & why they happen?

Have you ever seen this situation?

  If the answer is yes, you may have a case of memory leak induced in your application, but fortunately we've got a cure for what ails you.

Above Error message Thrown when the Java Virtual Machine cannot allocate an object because it is out of memory and no more memory could be made available by the garbage collector.

An OOM or OOME (OutOfMemoryError) simply means that the JVM ran out of memory. When this occurs, you basically have 2 choices:

• Allow the JVM to use more memory using the -Xmx VM argument. For instance, to allow the JVM to use 1 GB (1024 MB) of memory:

Java -Xmx1024m...

• Improve/Fix the application so that it uses less memory

In many cases, like in the case of a memory leak, the second option is the only sound choice. A memory leak happens when the application keeps more and more references to objects and never releases them. The garbage collector will therefore never collect those objects and less and less free memory will be available until we reach the point where not enough free memory is available for the application to function normally. At this point, the JVM will throw an OOM.

How JVM Allocates Memory:

Let’s have a look at the Sun Hotspot JVM implementation as an example:

Java Heap space is divided into three regions or generation for sake of garbage collection - New Generation, Old or tenured Generation or Perm (permanent) Space. JVM allocates every generation its own memory pool. The JVM has at least one memory pool and it may create or remove memory pools during execution.

1. Eden Space (heap - young generation):
1. Pool from which memory is INITIALLY allocated for most objects.
2. Survivor Space (heap - young generation):
1. Pool containing objects that have survived GC of Eden space.
3. Tenured Generation (heap - old generation):
1. Pool containing objects that have existed for some time in the survivor space.
4. Permanent Generation (non-heap - stack):
1. Holds all the reflective data of the virtual machine itself, stores class level details, loading and unloading classes (e.g. JSPs), methods, String pool.
5. Code Cache (non-heap):
1. Hotspot JVM also includes a "code cache" containing memory used for compilation and storage of native code.

My next post will have how to find memory leak using various JDK Tools....

performance testing requirement gathering questionnaire

As a performance tester or engineer , you should interact with various stakeholders to gather performance testing requirements ( also called as NFR’s –Non Functional requirements ).

I am going to provide an inside about my experience during this activity what kind of questions you should ask for various stakeholders.

Performance testing stakeholders (mainly below list of people you may contact for NFR’s ) :

1)      Developers

2)      Project Managers

3)      Business architects

4)      System architects

5)      DBA’s

6)      Capacity Manager

7)      Production monitoring teams if any?

Setting up a testing environment, which should be replica of the production environment is most important and fundamental aspect in performance testing and results will be based on that environment. Accurate performance results can never be achieved unless the production environment is not replicated.  Like it should scalable to PROD ( 50%, or 80% or 100% ). Adjust the load numbers based on the scaling factor .

Ask what kind of application it is ( web , Client/server model, back end jobs, middle ware calls , etc …)?

How that application being used on LIVE (end users, admins , stores managers , etc…)?

Find out the key Business scenarios  (based on the traffic on the site) ?

Find out the max number of concurrent users it can support ?

Find out the SLA’s for those  Business scenarios ? Users ,Response times, throughput levels ( TPS, etc) , Servers utilization .

Type of Performance tests needs to be required ( Load , Soak , Stress tests , etc…  it depends on what is your performance goal) , for example if you are looking for to check if any memory leaks exists on your application than go for soak test , if you want to find out break even point (How many concurrent users your application supports ) than go for Stress tests, etc….

As per my experience you can ask n number of questions during requirements gathering but mainly focus on key areas which are below

Performance SLA’s

Application architecture

Load Model

Servers (App/DB/Web and Middle wares) access

Servers resource utilization threshold values ( <70% generally )

Tools & Environments ( which tool needs to use and performance test environment details ).

Monitoring tools available ( Site scope, Wily , Dyna trace , etc..)

 A typical Application architecture looks like this for 3-tier :

Performance Stress Testing Approach

Approach for Stress Testing

Stress testing will be conducted basically to find out the Break even point of your application , which is the point where your system starts degrading the performance. try to compare the number of users and response time graphs to find out the maximum number of users your application can support without deviating performance SLA's.

The following steps are involved in stress-testing a Web application:

1. Step1 - Identify stress test objectives.  Identify the objectives of stress testing in terms of the desired outcomes of the testing activity.
2. Step 2 - Identify key scenario(s).  Identify the application scenario or cases that need to be stress-tested to identify potential problems.
3. Step 3 - Identify the workload.  Identify the workload that you want to apply to the scenarios identified during the “Identify objectives” step. This is based on the workload and peak load capacity inputs.

         Generally work load would be double of your normal baseline numbers , however start gradually increasing the load to check the break point ( Multilevel Ram up scenario on HP Load runner ).

1. Step 4 - Identify metrics.  Identify the metrics that you want to collect about the application’s performance. Base these metrics on the potential problems identified for the scenarios you identified during the “Identify objectives” step.
2. Step 5 - Create test cases.  Create the test cases in which you define steps for running a single test, as well as your expected results.
3. Step 6 - Simulate load.  Use test tools to simulate the required load for each test case and capture the metric data results.
4. Step 7 - Analyze results.  Analyze the metric data captured during the test.

Please find correlated graph for number of users and average response times for the test. this would help you to find out how many number of users your application can support without crossing your SLA's.

SQL's Performance testing using Jmeter

Testing SQL's performance using Jmeter is quite simple , below steps would help for the same.

I have used Oracle 11g for testing purpose.

1) Create a JDBC request Sampler on Jmeter thead group

JDBC Request -> Provide Unique variable name ( this variable name will be refereed by JDBC connection config ) and Select a SQL Type (update or insert , etc...).

2) Create a JDBC Connection Configuration as Config element.

Make sure you you provide correct Driver class and Database URL , below are for Oracle 11g.

DataBase URL-> jdbc:oracle:thin:@Hostname:1521:InstanceName
JDBC Driver Class -> oracle.jdbc.driver.OracleDriver


3) Add Response Assertion for validate the correct response of the SQL output ( check point on Load runner (web_reg_find)).

4) Add Listeners based on your needs like summary reports , Response time graphs , etc...

Have a look at below screen for JDBC Configurations.

5) Run the thread group after setting the number of users ( threads).                                                        

If you have access to OEM ( Oracle enterprise manager console) try to monitor the SQL's after you run the jmeter thread group so that you can analyze how the SQL is performing.

Hope this helps :)