Set up an Oracle Monitor in LoadRunner Using a Baseball Bat

Recently, I needed to setup an Oracle monitor in LoadRunner so I decided to take some screen shots along the way so that I could post them along with some instructions. I do this because I know I will forget how to do this in the future, so when the next project rolls around that requires it, I can search Google and find my own article on the topic. It’s a great ego booster. Maybe you are looking to do this, and it will help you too.

Whenever I go into a project where previous LoadRunner testing did not include proper monitoring (by some other jive talking turkey who had no clue), there is always a concern by the database administrator when we start asking for permissions to monitor the database server within LoadRunner. After an offline conversation that last about five minutes, they usually understand what we are doing and have no problem giving us access. However, I always keep a baseball bat handy in case that doesn’t work.

Setting up an Oracle monitor in LoadRunner allows you to pull information from the V$SESSSTAT and V$SYSSTAT tables (by default). You can see information from other “V$” tables using the custom query feature, but that is a discussion for another day. Both of these tables are similar (one gives stats on the current session, while the other give stats since the startup of the database). Do a Google search and take a look at all the V$ tables available. Use the LoadRunner monitor reference guide to look at all of the typical counters available to you.

Done with your research? Great! Now that you know that none of that stuff makes any sense, we need to go back to the DBA and have them tell us what THEY think we need to monitor, as we don’t want them all. I usually print out a hard copy of the list of Oracle counters in the LoadRunner monitor reference and give the DBA a highlighter pen. I would rather have them select which ones they want, than trying to figure out some “best practices” guide to Oracle counters. Then I go implement them. If the DBA does not know what monitor for their server, talk to their boss and request that they be fired with no severance, and replaced with a competent DBA. Then go get your baseball bat….

There are two things needed to make the Oracle monitor work:

1. You need the DBA to set up an account for LoadRunner to use on the Oracle database server. The only access the account needs is access to the V$ tables. If the DBA is still concerned, you can have them type in the password when you get to the point of setting up the monitor in the LoadRunner Controller. It may help if you tie a red bandana around your head, Rambo style, and pull it over your eyes. Then go stand in the corner facing opposite them with your fingers in your ears, while humming the Star Spangled Banner or the Theme from Shaft. This is usually a “set it and forget it” type of thing, so this way you never have to know the password.
2. You have to install the Oracle CLIENT files on the LoadRunner Controller. I tend to install the most stripped down version of the Oracle client that just has the basic DLL’s that will get me connected to the database, and make sure you have SQL+ as well. You should be able to do everything you need with that.

After installing the Oracle client, it’s time to configure it. In this example I am installing Oracle 10.2

In the Oracle configuration menu, select “Service Naming”. In my example I am adding a server called “Prod19”. The host name is the name/ip of the Oracle server.

Verify that the tnsnames.ora file is in place and has the right information.

Now make sure your Windows OS Environment settings are correct. You want to make sure you have the bin directory for the Oracle client in your PATH setting.

That should be it for setup outside LoadRunner. Now it’s time to add the monitor in the Controller. Add a new Oracle monitor and fill in the appropriate fields.

At the Oracle Logon prompt, reference the name you set up in the Oracle configuration (in my case Prod19). This is where you might have the DBA fill this out.

If that works, you should see a listing of available Oracle counters.

If that doesn’t work, there could be many things wrong so you will want to check the documentation and possibly place a call to support (meaning, don’t e-mail me because I don’t care if you get it working or not). However, this is one reason why I want SQL+ at my disposal. Most issues have to do with your permissions to get to the V$ tables. If you cannot get connected in SQL+, then you won’t get LoadRunner to work either. Many times, the DBA will try to lock things down too tight. To verify this, run SQL plus and run the query:

select * from v$sysstat;

If that did not work in that you cannot get connected or the query throws an error, either you don’t have the right permissions, or something isn’t configured right on the Controller. Check everything again and see if the DBA can jump in and help. If they can’t help you, then….you guessed it…get out the baseball bat… 🙂