8Monitoring Performance with Oracle Performance Manager

This chapter presents the Oracle Performance Manager performance and tuning charts specific to Oracle8i Parallel Server. You must have Oracle Performance Manager installed and configured to display the charts.

Oracle8i Parallel Server Administration, Deployment, and Performance for further information about additional views not available with Oracle Enterprise Manager, and for more detail on monitoring and tuning Oracle Parallel Server

Oracle Performance Manager Overview

Oracle stores tuning and performance information for the Oracle Parallel Server in a set of dynamic performance tables known as the "V$ fixed views" Each active instance has its own set of fixed views. In Oracle Parallel Server, you can have Oracle Performance Manager query a global dynamic performance (GV$) view to retrieve the related V$ view information from all instances.

Oracle Performance Manager displays the retrieved information in a variety of tabular and graphic performance statistics for Oracle Parallel Server. The statistics represent the aggregate performance of all instances running on an Oracle Parallel Server. The statistics are displayed in individual charts and include information about data block pings, lock activity, file I/O, and session and user information. You can also use the Performance Manager to display an overview of all of these statistics on one chart.

Oracle Parallel Server performance monitoring is crucial for realizing the full potential of the system. There are several key performance metrics that you should constantly monitor to keep the Oracle Parallel Server in peak operating condition. The Oracle Performance Manager, available as an applet within Oracle Enterprise Manager, is an application designed to capture, compute, and present performance data that help database administrators focus on key performance metrics.

Oracle Parallel Server performance metrics are compiled into charts that are viewable with Oracle Performance Manager:

Displays the statistics on the lock activity rate for all the different lock types across all Oracle Parallel Servers. You can drill down to obtain lock activity information for a particular lock type at the instance level

Parallel Server Data Block Ping by Tablespace Chart

The Parallel Server Block Ping by Tablespace chart displays statistics from the GV$PING view on the number of block pings by tablespace:

Figure 8-2 Parallel Server Block Ping by Tablespace Chart

To display block pings at the object or instance level:

Click on an individual tablespace.

Right-click and select the chart from the Drilldown menu:

Option

Description

Parallel Server Block Ping Tablespace Object Chart

Displays the objects for the selected tablespace. Objects represent tables, indexes, free extents, clusters of tables, and so on. Listed for each object is the number of pings in the tablespace in which that object resides.

Parallel Server Block Ping Tablespace Instance Chart

Displays the instances within the selected tablespace. Listed for each instance is the number of pings in the tablespace in which that instance resides.

Figure 8-3 OPS Block Ping Tablespace Object Chart

Figure 8-4 OPS Block Ping Tablespace Instance Chart

Parallel Server Data Block Ping by Instance Chart

The Parallel Server Block Ping by Instance chart displays statistics from the GV$PING view on the number of block pings per instance:

Figure 8-5 Parallel Server Block Ping by Instance Chart

Parallel Server File I/O Rate Chart

The Parallel Server File I/O Rate chart displays physical files reads and writes for all files from the GV$FILESTAT view.

Figure 8-6 Parallel Server File IO Rate Chart

To display the file I/O rate at the instance level or at the file level:

Figure 8-8 Parallel Server File IO Rate by Instance Chart

Parallel Server Lock Activity Chart

The Parallel Server Lock Activity chart shows the number of lock converts of various types (exclusive to null) from the GV$LOCK_ACTIVITY view:

Figure 8-9 Parallel Server Lock Activity Chart

Column

Description

From

PCM lock initial state:

NULL; S; X; SSX

To

PCM lock final state:

NULL; S; X; SSX

Sum

Displays the number of times the lock operation executed

Parallel Server Sessions Chart

The Parallel Server Sessions chart displays a table of Session IDs. This chart displays statistics from the GV$PROCESS and GV$SESSION and GV$PROCESS views.

Figure 8-10 Parallel Server Sessions Chart

Column

Description

SID

Displays the session identifier

Saddr

Displays the session address

Serial

Displays the session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.

PID

Displays the operating system client process ID

Status

Displays the status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client)

User Name

Displays the Oracle user name

Parallel Server Users Logged On Chart

The Parallel Server Users Logged On chart displays the total number of user sessions currently logged on to the Oracle Parallel Server, whether or not activity is being generated. This chart displays statistics from the GV$LICENSE view:

Figure 8-11 Parallel Server Users Logged On Chart

Parallel Server Users Logged On by Instance Chart

The Parallel Server Users Logged On by Instance chart displays the number of users logged on to each instance in the Oracle Parallel Server. This chart displays statistics from the GV$LICENSE view.

Figure 8-12 Parallel Server Users Logged On by Instance Chart

Parallel Server Active Users Chart

The Parallel Server Active Users chart displays the total number of user active sessions. This chart displays statistics from the GV$LICENSE view:

Figure 8-13 Parallel Server Active Users Chart

Parallel Server Active Users by Instance Chart

The Parallel Server Active Users by Instance chart displays the number of active user sessions logged on to each instance in the Oracle Parallel Server. This chart displays statistics from the GV$LICENSE view.