Monitoring Oracle Database

Monitoring Oracle Databases and Analysing Bottlenecks with AgileLoad

This video describes the set up and use of the Oracle monitor / collector and anomaly detector in Agile Load. In this case we set up the Oracle collector template and use another install of AgileLoad to collect the data from the database. This distributed implementation can be useful for monitoring databases where a firewall is between your machine and the target, or like in this case, the Oracle client libraries provided by Oracle do not work on the operating system you are using on your machine and you need to choose another machine with a supported OS to do the monitoring.

This video is focused on Monitoring Oracle with AgileLoad.  AgileLoad is more than just a performance testing tool.  Of course, it has a powerful monitoring capability for performance engineering.

You need an Oracle server of course and the Oracle client installed in the machine where you want to do the monitoring.

The information you are going to need about the Oracle instance is the Alias, the Host, Oracle version, Service Name, Login and Password, and a port which is usually 1521.  The Login must have system privileges for this to work correctly.

How to setup a performance monitor for Oracle

The monitor in this case isn’t going to be be running from the AgileLoad Center.  What we are going to do is run the monitor from a different place in the network.  You will see how to set the monitor up and then we will choose a different IP address to monitor from.  So in fact, you can think of this as using a Oracle Monitoring Probe on a network to get the information back; and that can be useful to bypass problems like firewalls between you and the target that you want to monitor; or in this case, we are monitoring Oracle 8 hence the client libraries don’t work on Windows 7 so we needed to install our collector, our monitor on an XP machine that we then thought you to relay the information back from.

In my test center machine, I am going to :

  • go through all programs and launch AgileLoad Test Center
  • to the repository tab
  • up to the Oracle folder and create a new Oracle monitoring profile.  
  • call this MyDB, then double clicking on that will open up the parameters for the monitoring profile where I need to put in my Alias, Host, port, Oracle version, Service Name, Login and Password, and so on.  
  • save this and close it 
  • create a job to do the monitoring. 
  • scrolling down and into the jobs folder, I can right click and create new job.  In this case, I am going to call it ORA and then I am not going to add any scripts at this point.  

I am going to drag and drop my Oracle database monitoring profile and I am going to call this CoreDB, just to give it a name for the results that come back with.  

The injector I am going to use is in fact a remote machine.  It is another machine where I have AgileLoad installed.  So I save that and then we start to run.  

How to make sense of what the Oracle monitor is telling you

Go to the Oracle folder and look at the Oracle Overview.  

On the overview screen I can see the monitoring summary and going across to the most commonly called sequel, I can see the most commonly called sequel statements.  

Using Oracle for my anomaly list,  I just click on okay and then we can see there are a number of anomalies relating to this Oracle database.  So there were some monitoring graphs that we can looked at; and if you are not an Oracle expert, this can really help you.  

So in this case, we can see there is an issue with a Oracle buffer cache ratio and we have a description down below of what you can do to improve that.  We also have an issue with direct path IO wait time; and then scrolling down ever further, we have an issue with the index fetch ratio and what we could see there is a low value.  It might mean there are a lot of table scans happening.  So this is valuable information with problems on the DBA, which help you tune your Oracle database to make sure that you are getting the best out of it.

Advanced Tutorials > Video Tutorial > Monitoring Oracle Database
Print this page  -  Go to top of page