Friday, 30 September 2011

Posted by Precise Testing Solution | 00:01 | 1 comment
Introduction to Database Resource Monitoring
You monitor DB2, Oracle, SQL Server, or Sybase database resource usage
during a scenario run using LoadRunner’s Database Server Resource
monitors.
The DB2, Oracle, SQL Server, or Sybase database server resource monitors
measure statistics for DB2, Oracle, SQL Server, or Sybase database servers.
During a scenario run, you use these monitors to isolate database server
performance bottlenecks.
For each database server, you configure the measurements you want to
monitor before running your scenario. To run the DB2, Oracle, and Sybase
monitors, you must also install the client libraries on the database server
you want to monitor.

Oracle Monitoring
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.
The SiteScope Oracle JDBC Monitor monitors the server performance
statistics from Oracle Database servers. You can monitor multiple
parameters or counters with a single monitor instance. This allows you to
watch server loading for performance, availability, and capacity planning.
You can create a separate Oracle JDBC Monitor instance for each Oracle
database server in your environment.
To obtain data for this graph, you must first set up the monitoring
environment. You then configure the Oracle online monitor (from the
Controller) and select the measurements you want to display, before
running the scenario.
This chapter includes:
➤ Setting Up the Monitoring Environment on page 
➤ Adding a Machine to Monitor on page 
➤ Configuring the Oracle Monitor on page 
➤ Oracle Performance Counters on page 
➤ Custom Queries on page 

Note: The port you use to monitor an Oracle server through a firewall
depends on the configuration of the Oracle server. Configuration
information for the connection between the client and server is located in
the Oracle client tnsnames.ora file.
Setting Up the Monitoring Environment
➤ If you are using the SiteScope monitor engine, ensure that SiteScope has
been installed on a server. You can install SiteScope on the same machine as
the Controller, or on a dedicated server.
➤ The Oracle server measures information from the V$SESSTAT and
V$SYSSTAT Oracle V$ tables, and other table counters defined by the user in
the custom query. In order to monitor the Oracle server, you must set up the
monitoring environment as described below before you can configure the
monitor.
To set up the native LoadRunner Oracle monitor environment:
1 Ensure that the Oracle client libraries are installed on the Controller
machine.
2 Verify that %OracleHome%\bin is included in the path environment
variable. If it is not, add it.
3 Configure the tnsnames.ora file on the Controller machine so that the
Oracle client can communicate with the Oracle server(s) you plan to
monitor.
You can configure connection parameters either manually, by editing the
tnsnames.ora file in a text editor, or using the Oracle service configuration
tool (for example, select Start > Programs > Oracle for Windows NT >
Oracle Net8 Easy Config).
You specify:
➤ a new 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)
For example:


Note: Only the 32-bit Oracle client should be installed 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, the 16-bit installation
should be uninstalled.

4 Obtain a username and password for the service from your database
administrator, and ensure that the Controller has database administrator
privileges for the Oracle V$tables (V$SESSTAT, V$SYSSTAT, V$STATNAME,
V$INSTANCE, V$SESSION).
5 Verify connection with the Oracle server by performing tns ping from the
Controller machine.
Note: here may be a problem connecting if the Oracle server is behind a
DMZ/firewall that limits its communication to application servers accessing
it.


6 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
7 Verify that the Oracle server you want to monitor is up and running.
Note: It is possible to monitor several Oracle database servers concurrently.
8 Run SQL*Plus from the Controller and attempt to log in to the Oracle
server(s) with the desired username/password/server combination.
9 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. Make sure that the Oracle bin directory is
in the search path.
10 To change the length of each monitoring sample (in seconds), you need to
edit the dat\monitors\vmon.cfg file in the LoadRunner root folder. The
default rate is 10 seconds.
Note: 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 will
continue to monitor at 10 second intervals.
If a problem occurs in setting up the Oracle environment, view the error
message issued by the Oracle server.

To set up the SiteScope Oracle JDBC monitor environment:
1 You must have a copy of the applicable Oracle JDBC database driver file (for
example, classes12.zip) on the SiteScope server.
Copy the downloaded driver file into the <SiteScope install
path>\SiteScope\java\lib\ext subdirectory. DO NOT unzip the file.
Stop and restart the SiteScope service after copying the driver file to the
SiteScope machine.
Note: More than one driver file is available for download. Some drivers
support more than one version of Oracle database (for example, the
classes12.zip Oracle JDBC thin driver) while others only support a particular
version. If you are monitoring a recent version of Oracle database, you
should download the latest version of the database driver.
2 You must supply the correct Database Connection URL, a database
username and password when setting up the monitor. The syntax of the
Database Connection URL usually has the form of:
jdbc:oracle:thin:@<tcp address>:<tcp port>:<database sid>.
For example to connect to the ORCL database on a machine using port 1521
you would use:
jdbc:oracle:thin:@206.168.191.19:1521:ORCL
Note: The colon and @ symbols must be included as shown.
3 You must specify the Oracle Database Driver that was installed on the
SiteScope server when setting up the monitor. The Database Driver for the
Oracle thin JDBC driver is:
oracle.jdbc.driver.OracleDriver

4 You should only have one Oracle client installed on the SiteScope machine.
If there is more that one client installed, SiteScope may report an error and
be unable to connect to the database.
5 You must have an Oracle user login that SiteScope will use to access the
Oracle server. In order to retrieve the Oracle database counters, the user that
SiteScope will use for the Oracle JDBC Monitor should be able to execute all
the SQL statements as found in the file
SiteScope\templates.applications\commands.oraclejdbc.
Adding a Machine to Monitor
In order to monitor the Oracle database resources of a particular machine
from the Controller, you need to add the machine and the measurements
that you want to monitor.
To add a machine to the Controller
1 Click the Oracle graph in the graph tree, and drag it into the right pane of
the Run view.
2 Right-click the graph and select Add Measurements, or click anywhere on
graph and choose Monitors > Add Measurements. The Oracle dialog box
opens.
3 By default, LoadRunner monitors Oracle database resources using the native
LoadRunner monitor engine.
If you want to monitor the Oracle database using a SiteScope monitor, click
Advanced.
In the Choose Monitor Engine dialog box, choose SiteScope, and click OK
to close the Monitor Engine dialog box.

4 In the Monitored Server Machines section, click Add. The Add Machine
dialog box opens.
➤ In the Monitored Machine Information section, enter the server name or
IP address of the machine you want to monitor. Select the platform on
which the machine runs.
➤ In the SiteScope Server Information section, enter the SiteScope machine
name, and port (default: 8888), and specify whether you are using a
Secure HTTP connection. If you are using a SiteScope account, fill in the
relevant account information.
Note: If you are adding a native LoadRunner monitor, the SiteScope
Server Information section does not appear.
Click OK to close the Add Machine dialog box.
5 In the Resource Measurements on: <machine> section of the Oracle dialog
box, click Add.
6 Continue with Configuring the Oracle Monitor below.
Configuring the Oracle Monitor
The configuration for this monitor differs slightly depending on whether
you are adding a SiteScope or native LoadRunner monitor.
This section describes:
➤ Configuring the Native LoadRunner Oracle Database Monitor
➤ Configuring the SiteScope Oracle JDBC Monitor

Configuring the Native LoadRunner Oracle Database Monitor
After you have added the machine that you are monitoring, you configure
the monitor by choosing which objects to monitor on the machine.
To configure the Oracle Database monitor:
1 When you click Add to add a measurement, the Oracle Logon dialog box
opens.
Enter the following information, and then click OK.
➤ Login Name. Enter your login name.
➤ Password. Enter your password.
➤ Server Name. Enter the name of the server.
2 The Add Oracle Measurements dialog box opens, displaying the available
measurements.

3 For each measurement, select an object, measurement, and instance, and
then click Add, as described in “Understanding the Add Oracle
Measurements Dialog Box” below.
For a description of the available measurements, see “Oracle Performance
Counters” on page 247.
4 Click Close. The measurements that you selected appear in the Resource
Measurements on: <machine> section of the Oracle dialog box.
5 Click OK in the Oracle dialog box to activate the monitor.
Note: By default, the database returns the absolute value of a counter.
However, by changing the IsRate setting in the dat\monitors\vmon.cfg file
to 1, you can instruct the database to report a counter’s rate value—the
change in the counter per unit time.
Understanding the Add Oracle Measurements Dialog Box
The Add Oracle Measurements dialog box lets you select the resources to
monitor from the V$SESSTAT and V$SYSSTAT Oracle V$ tables.
➤ Object. Select the object being monitored on the specified machine.
➤ Measurements. Select a resource measurement to monitor. Select multiple
measurements using the CTRL key. For an explanation of each
measurement, click Explain.
➤ Instances. If multiple instances of the selected measurement are running,
select one or more instances to monitor for the selected measurement.
➤ Explain. Displays a description of the selected measurement.
Configuring the SiteScope Oracle JDBC Monitor
After you have added the machine that you are monitoring, you choose the
measurements to monitor on the machine.
To configure the Oracle JDBC monitor:
1 The first time you add a measurement to the monitor, you need to configure
the monitor properties. When you click Add to add a measurement, the
Configuring Oracle JDBC Monitor dialog box opens.
Enter values for the monitor properties as described in “Understanding the
Configuring Oracle JDBC Monitor Dialog Box” on page 246, and click OK.


Understanding the Configuring Oracle JDBC Monitor Dialog Box
You use the Configuring Oracle JDBC Monitor dialog box to configure the
Oracle JDBC monitor.
➤ Instance. Enter the database SID. For example, ORCL.
➤ Database Connection URL. Enter the connection URL to the database you
want to monitor. For example, jdbc:oracle:thin:@206.168.191.19:1521:ORCL
➤ Database User Name. Enter the user name that SiteScope should use to
connect to the database.
➤ Database Password. Enter the password for the user name that SiteScope
should use to connect to the database.
➤ Database Driver. Enter the driver used to connect to the database. For
example, oracle.jdbc.driver.OracleDriver.
➤ Connection Timeout. Enter an optional the time out value, in seconds, that
SiteScope should to wait for a database connection to respond.
➤ Query Timeout. Enter an optional the time out value, in seconds, that
SiteScope should to wait for a response from the database query. If the
database does not respond within the period specified, SiteScope will report
an error.
➤ Update every. Select how often the monitor should read the server statistics.
The default interval is to run or update the monitor once every 10 seconds.
Notes:
➤ The sum of the Connection Timeout value and Query Timeout value
should always be less than the Update every value for the monitor.
➤ Notes:
➤ The sum of the Connection Timeout value and Query Timeout value
should always be less than the Update every value for the monitor.
➤ Some commonly used databases and database drivers do not support the
Query Timeout feature. In these cases the Query Timeout value should be
set to zero.
Understanding the Oracle Configuration Dialog Box
The Oracle Configuration dialog box enables you to select the performance
counters to monitor.
➤ Host. The name of the host machine.
➤ Measured Components. Displays a tree containing all the available
measured components.
➤ Performance Counters. Displays the performance counters for a selected
measured component.
➤ Component/Counter Description. Displays a description of the selected
measured component or performance counter.

Custom Queries
Using the custom query feature, you can define your own query to the
Oracle database and view the result of this query—a single numerical
value—in the Oracle online monitor graph. By defining your own query,
you can monitor not only the V$SYSSTAT and V$SESSTAT table counters
that are currently provided by the Oracle monitor, but other tables that
contain useful performance information as well.

To create a custom query:
1 In the third line of the vmon.cfg file, CustomCounters=, indicate the
number of custom counters you want to create.
2 Create a new section in the vmon.cfg file for the new counter. Each section
has the following format:
[Custom2]
Name=Number of sessions
Description=This counter returns the number of sessions active.
Query=SELECT COUNT(*) FROM V$SESSION
IsRate=1
3 In the [Custom#] line, assign the next number in the sequence of counters
to the new custom counter. The custom counters must be in consecutive
order, beginning with the number 0.
4 In the Name line, enter the name of the new counter.
5 In the Description line, enter the description of the counter that you want
the help message to contain.
6 In the Query line, enter the text of the SQL query (on one line of the
vmon.cfg file) that returns exactly one row from the database. This row
must contain one column, a numerical value. Custom queries should not
exceed 512 characters.
7 In the IsRate line, enter 0 if you want the database to report the counter as
an absolute number. If you want the database to report the change in the
counter per unit time, enter 1. Custom queries cannot return negative
values.

1 comment:

  1. I aam sure this post has touched ɑll thhe internet viewers, іts reallƴ
    really gօod article ߋn bjilding up neww web site.

    Αlso visit mmy wweb blog facebook poster (http://Burtkohl.com)

    ReplyDelete

Bookmark Us

Delicious Digg Facebook Favorites More Stumbleupon Twitter