Performance Co-Pilot for ORACLE offers several visualization tools, including orachart to display various performance graphs for ORACLE instances, pmgora to continuously monitor disk and ORACLE file performance, and pmgoraping to monitor ORACLE server response time (quality of service).
The orachart command (see orachart(1)) displays a number of two-dimensional graphs showing performance of a given ORACLE database instance that is being (or has been) monitored with pmdaoracle (see pmdaoracle(1)).
For orachart you must specify a configuration file (see orachart(1) for the syntax), which orachart uses to generate a pmchart configuration file (see pmchart(1) for the syntax) for a specific ORACLE instance.The default configuration directory is /var/pcp/config/orachart, and configurations supplied with the product are shown in Table 5-1.
Table 5-1. Default orachart Configuration Files
Dirty queue length, database gets and changes, disk reads and writes
Gets and immediate gets (from V$LATCH view), most recent and average
Important statistics (from V$SYSSTAT) for database writer, DBWR
Number of messages sent and received between ORACLE processes
Number of SELECT, DDL, and DML statements parallelized
Memory and disk sorts, parses, executions, and cache hits by SQL commands
Table scans (short and long), table rows scanned, and table blocks scanned
User commits, user rollbacks, redo writes by LGWR, and transaction rollbacks
To monitor database activity for database instance CTC2 on host thebeast, the following command produces a window similar to that shown in Figure 5-1:
$ orachart -i CTC2 -h thebeast Activity
The -i option is needed only for systems running multiple database instances, and then only if the environment variable ORACLE_SID is not set. The -h option is required if the database instance resides on a remote host.
If you want to create a custom database performance view, use the standard pmchart facilities: choose Edit > New Chart and then use the Metric Selection window to select the exact combination of monitored data that you want graphed.
Like pmgirix (see pmgirix(1)), pmgora displays a window showing continuously updated performance symbols for a given ORACLE database instance that is being monitored with pmdaoracle (see pmdaoracle(1)).
Actually, pmgora generates a pmgadgets configuration file for monitoring throughput in ORACLE database files, then calls pmgadgets with that configuration; see pmgadgets(1). The pmgora window displays two LEDs for each ORACLE file showing read/write activity by the monitored ORACLE instance on that file. Beneath the file list, a single LED shows the actual I/Os per second (IOPs) for each disk identified in the system. The connecting lines between disk LEDs represent disk controllers, giving an indication of I/O parallelization across both disks and controllers.
To monitor file activity for database instance Two on host moomba, the following command produces a window similar to that shown in Figure 5-2:
$ pmgora -i Two -h moomba
For complete information about pmgora, including how to customize gadget options, see the pmgora(1) and pmgadgets(1) reference pages.
Like pmgora, pmgoraping generates a pmgadgets configuration file for monitoring ORACLE response time. This is a useful tool for monitoring performance as perceived by end users, and provides insight into the quality of service provided by a particular ORACLE database instance.
|Note: pmgoraping requires the oraping PMDA (described in Chapter 4) to be installed and running.|
To monitor response time for database instance Two on host moomba, the following command produces a window similar to that shown in Figure 5-3:
$ pmgoraping -i Two -h moomba
The moving ORACLE graph displays response latency over time, in proportion to the maximum response time recorded, or the maximum value if you specified the -m option. The LED light shows green for good performance, yellow for borderline conditions, and red for poor performance. “Good, borderline, and poor” are ratios derived from the expected response time specified by the -m option.
Beneath the response-time graph, pmgoraping shows CPU and Memory meters for the overall system, to help you judge whether problems might be caused by shortage of system resources. The default ping update time is every 10 seconds, but this can be changed with the -t option. Note that this update time is independent of the update interval for the oraping PMDA.
If you position the mouse pointer over the LED, click the Right mouse button, and choose Info, pmgoraping displays a window similar to that shown in Figure 5-4.
This shows the response time thresholds at which the LED changes state and color.