How to setup Oracle monitoring in WAPT Pro

December 7, 2013 11:18 am 2 comments

WAPT Pro can monitor the performance of database servers during the test. This is useful in case your web application uses a database running on a separate server and you want to check if that database can be a performance bottleneck.

Each database has a special table with performance parameters (such as the number of transactions completed per second, number of threads, etc.). To retrieve any specific parameter you need to execute the corresponding SQL statement. So, this is done the same way as any other data retrieval. The only difference is that performance parameters are calculated by the database server itself. You do not need to create the performance table before using data from it.

WAPT Pro has a number of predefined performance counters for MS SQL, MySQL and Oracle databases, which means that the corresponding SQL statements are already provided in the product. You only need to setup the connection to your database server from the system where you run the WAPT Pro workplace.

This is done through ODBC and usually it is very easy to configure… with one exception called Oracle. That is why I decided that the following step by step instruction will be useful for our customers. If you have an Oracle server and want to monitor its performance during the load test, this is what you need to do.

1. Download instantclient-basic-nt-11.2.0.3.0.zip and instantclient-odbc-nt-11.2.0.3.0.zip files from this page: http://www.oracle.com/technetwork/topics/winsoft-085727.html

2. Unzip both files to a folder, for example “C:\Oracle\”.

3. Run odbc_install.exe as Administrator.

oracle_1

4. Use any text editor to create the following file: “C:\Oracle\network\ADMIN\tnsnames.ora”. It should be a plain ASCII file with the following text:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = {server IP})(PORT = {server port}))
(CONNECT_DATA =
(SERVICE_NAME = {service name})
)
)

You should insert the proper {server IP}, {server port} and {service name} values. Default port number is 1521. You can copy service name from the same file on your Oracle server.

5. Go to Control Panel\All Control Panel Items\System and click the “Advanced system settings” link. On a different OS version the below dialog may be different, but this functionality should be present.

oracle_2

6. Click the “Environment Variables” button in the next window to get to the following dialog.

oracle_3

7. Add the following variables to the System variables section:
ORACLE_HOME = C:\Oracle
TNS_ADMIN = C:\Oracle\network\ADMIN

Append thethe PATH variable with the following path: “C:\Oracle”.

8. Since WAPT Pro workplace is a 32 bit application, you need to use the 32 bit version of ODBC. So, if you run it on a 32 bit OS, you can start the default ODBC from Control Panel. If you use a 64 bit OS, run the following file: “C:\Windows\SysWOW64\odbcad32.exe”.

oracle_4

9. Click the “Add…” button and choose “Oracle in instantclient_11_2” in the list. Click “Finish”.

oracle_5

10. Specify the parameters in the following dialog.

oracle_6

Data Source Name – Any name you would like to use for this DSN (“oracle11” in the example below);
TNS Service Name – select “ORCL” from the list.
Click the “Test Connection” button and enter the credentials.

11. In WAPT Pro choose the “Performance Counters” item in the left view and click the “Add…” button under the “SQL Performance” list. The following dialog will appear.

oracle_7

Specify DSN and credentials. Click the “Test ODBC connection” button, then the “Test…” button under the list of counters. If you experience any problem, please contact us at support@loadtestingtool.com.

2 Comments

Leave a Reply