Oracle database server monitor

Use this monitor to measure statistics related to Oracle databases during the performance test run.

In this topic:

Oracle monitoring overview

The Oracle monitor displays information from Oracle V$ tables: Session statistics, V$SESSTAT, system statistics, V$SYSSTAT, and other table counters defined by the user in the custom query.

Before defining the monitoring measurements for the Oracle monitors in the Controller, you must set up the monitoring environment on the database server.

Back to top

Configure the Oracle monitoring environment

This task describes how to configure the monitor environment before monitoring an Oracle database server.

1. Prerequisites

   • Ensure that the Oracle client libraries are installed on the Controller machine.

   • Verify that %OracleHome%\bin is included in the path environment variable. If it is not, add it.

   • Ensure that the registries are updated for the version of Oracle that you are using and that they have the following key: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

   • Ensure that the LoadRunner Enterprise Server is installed in a directory whose path does not include any of the following characters: ( ) : ; * \ / " ~ & ? { } $ % | < > + = ^ [ ].

   • Verify that the Oracle server you want to monitor is up and running. Note that it is possible to monitor several Oracle database servers concurrently.

   Note: Install only the 32-bit Oracle client on the Controller machine running the Oracle monitor. If you have a 16-bit and a 32-bit Oracle client installation on the Controller machine, uninstall the 16-bit installation.

   • If your monitor accesses a server outside of your internal network, you may need to set up a web proxy to collect data from it. For details, see Use a proxy server.

2. Configure the Oracle client/server connection

   Set the connection parameters so the Oracle client (Controller machine) can communicate with the Oracle server(s) you plan to monitor.

   On the Controller machine, set the following configuration parameters either by editing the tnsnames.ora file in a text editor, or using the Oracle service configuration tool.

   Example:  

   Start > Programs > Oracle for Windows NT > Oracle Net8 Easy Config

   • A new net service name (TNS name) for the Oracle instance

   • TCP protocol

   • The host name (name of monitored server machine)

   • The port number (usually 1521)

   • The database SID (the default SID is ORCL)

   Example:  

   

3. Connect to the monitored server machine and verify the connection

   1. Obtain a user name and password for the service from your database administrator, and ensure that the Controller has database administrator permissions for the Oracle V$ tables (V$SESSTAT, V$SYSSTAT, V$STATNAME, V$INSTANCE, V$SESSION).

   2. Verify connection with the Oracle server by performing tns ping from the Controller machine.

   Note: There may be a problem connecting if the Oracle server is behind a DMZ/firewall that limits its communication to application servers accessing it.

   3. Run SQL*Plus from the Controller and attempt to log in to the Oracle server(s) with the desired user name/password/server combination.

   4. Type SELECT * FROM V$SYSSTAT to verify that you can view the V$SYSSTAT table on the Oracle server. Use similar queries to verify that you can view the V$SESSTAT, V$SESSION, V$INSTANCE, V$STATNAME, and V$PROCESS tables on the server.

4. Modify the monitoring sample rate (optional)

   To change the length of each monitoring sample (in seconds), edit the dat\monitors\vmon.cfg file in the LoadRunner Enterprise root folder. The default rate is 10 seconds.

   The minimum sampling rate for the Oracle Monitor is 10 seconds. If you set the sampling rate at less than 10 seconds, the Oracle Monitor continues to monitor at 10 second intervals.

5. Configure the monitor measurements as described in Set up the Oracle monitor.

Back to top

Set up the Oracle monitor

This task describes how to set up the Oracle monitor.

1. Select a resources folder.

   Select a resources folder for the monitor profile in the resources tree, or create a new folder as follows:

   1. In the LoadRunner Enterprise masthead, click the module name or the dropdown arrow and select Monitors (under Assets).

   2. Click New Folder and enter a name for the monitor resources folder.

2. Create a monitor profile.

   1. Click New Monitor Profile , and make sure Monitor Profile is selected.

   2. Enter a name and optional description for the monitor profile, and click Save. An empty monitor profile is added to the selected resources folder.

   3. Select the monitor profile you just added and click Add Monitor .

   4. In the Monitor Type Selection tab, select the Oracle monitor type and click Next.

   5. In the Monitor Definition tab, enter the server details:

      UI Elements	Description
      Server	The name or IP address of the server whose resources you want to monitor.
      User Name	The monitored server's user name, if relevant.
      Password	The monitored server's password, if relevant.

   6. Click Get Counters to display a list of available metrics and counters, and select the relevant measurements that you want to monitor.

      Select the measurements that you want to monitor, and then click to add the counters to the Selected Counters table.

      The following measurements are most commonly used when monitoring the Oracle server (from the V$SYSSTAT table):

      Measurement	Description
      CPU used by this session	The amount of CPU time (in 10s of milliseconds) used by a session between the time a user call started and ended. Some user calls can be completed within 10 milliseconds and, as a result, the start and end-user call time can be the same. In this case, 0 milliseconds are added to the statistic. A similar problem can exist in the operating system reporting, especially on systems that suffer from many context switches.
      Bytes received via SQL*Net from client	The total number of bytes received from the client over Net8.
      Logons current	The total number of current logons.
      Opens of replaced files	The total number of files that needed to be reopened because they were no longer in the process file cache.
      User calls	Oracle allocates resources (Call State Objects) to keep track of relevant user call data structures every time you log in, parse, or execute. When determining activity, the ratio of user calls to RPI calls gives you an indication of how much internal work is generated as a result of the type of requests the user is sending to Oracle.
      SQL*Net roundtrips to/from client	The total number of Net8 messages sent to, and received from, the client.
      Bytes sent via SQL*Net to client	The total number of bytes sent to the client from the foreground processes.
      Opened cursors current	The total number of current open cursors.
      DB block changes	Closely related to consistent changes, this statistic counts the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation. These are changes that generate redo log entries and cause permanent changes to the database if the transaction is committed. This statistic is a rough indication of total database work and indicates (possibly on a per-transaction level) the rate at which buffers are being dirtied.
      Total file opens	The total number of file opens being performed by the instance. Each process needs a number of files (control file, log file, database file) to work against the database.

   7. Click Save. The monitor details are displayed in the Monitors tab for the selected monitor profile.

3. Associate the monitor with your test.

   For details, see Step 3: Associate monitors with your test.

Back to top

See also:

• Monitors