Chapter 4. ORACLE Response-Time Agent

The PCP for ORACLE package includes a second ORACLE-specific agent, oraping. The oraping agent periodically executes an SQL query against some ORACLE database, measures the response time from the ORACLE server, and exports that response time as a performance metric into the PCP infrastructure.

Installing oraping

Because oraping is a PCP PMDA, it must be installed on a PCP collector system, where a pmcd process is running. Prerequisites are as follows:

  • The pcp.sw and pcp_ora.sw.collector product images must have been installed, using either swmgr or inst; see Chapter 2.

  • The pmcd daemon must be running on the PCP collector system.

  • You must have superuser (root) permission to run Install.

  • You must know the ORACLE_HOME and ORACLE_SID environment variables for the desired database instance.

  • The ORACLE database instance must be up and running.

  • You must have DBA access to the database instance as the SYS database user.

  • ORACLE Pro*C must be installed (oraping is supplied as Pro*C source code).

  • If the database instance to be probed by oraping is remote, ORACLE SQL*Net must be installed (oraping executes as a client of the ORACLE server instance).

The installation procedure is as follows:

  1. Become superuser using the su command or by logging in as root.

  2. Enter the following commands:

    # cd /var/pcp/pmdas/oraping 
    # ./Install 

  3. Answer questions from the Install script as follows:

    Use ORACLE_HOME=/oracle/home? y 
    ORACLE_SID is not set in the environment. Please enter it now: demo 
    Do you want to customize the ping interval? [n] y 
    How many seconds between pings? 30 
    The oraping agent requires an ORACLE username (and password) to connect to the database, create a table, and add some rows to it.  Please enter the username and password for a suitable user.
    ORACLE user/password: username/password 
    Making the oraping agent...
    You need to choose an appropriate configuration for installation
    of the “oraping” Performance Metrics Domain Agent (PMDA).
     collector collect performance statistics on this system
     monitor   allow this system to monitor local and/or remote systems
     both      collector and monitor configuration for this system
    Please enter c(ollector) or m(onitor) or b(oth) b 
    Updating the Performance Metrics Name Space (PMNS)...
    Installing pmchart view(s)...
    Terminate PMDA if already installed...
    Installing files...
            make -f Makefile.ora install
    Updating the PMCD control file, and notifying PMCD...
    * You must run SQL script /var/pcp/pmdas/oraping/setup.sql as ORACLE
    * user SYS on the demo database to grant the oraping PMDA access.
    * For example as the ORACLE DBA:
    *    $ $ORACLE_HOME/bin/svrmgrl
    *    SVRMGR> connect sys
    *    Password: ....
    *    SVRMGR> @/var/pcp/pmdas/oraping/setup.sql
    * Once this has been done, start the oraping PMDA:
    *    # /etc/init.d/pcp start

  4. Follow the commands shown at the end of the installation messages, which provide the necessary database access privileges for the oraping PMDA.

Once started, the oraping agent creates an activity log in /var/adm/pcplog/oraping.log to record unusual conditions or errors that might arise, such as failed SQL commands.

Customizing oraping

The oraping agent may be deployed differently to quantify the following ORACLE quality of service measures:

  • Local ORACLE server performance (oraping running on the same system as the ORACLE server).

  • ORACLE server performance as seen from remote client applications (use oraping where the client applications run; for example, on a workstation connected to the LAN, and on a system connected to the ORACLE server by means of the WAN).

  • ORACLE server performance on platforms other than Silicon Graphics systems (use oraping on Silicon Graphics systems to probe ORACLE on another platform).

The oraping agent is distributed in source code format to encourage the following customizations:

  • Replace the generic SQL query by something that is more representative of the workload in the target environment.

  • Add further query types and export response times for each query type.

Refer to the Performance Co-Pilot Programmer's Guide for a complete description of how to create and integrate new PMDAs into the PCP infrastructure.