Release 1 (9.0.1)
Part Number A89870-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Real Application Clusters Deployment and Performance Release 1 (9.0.1) Part Number A89870-02 |
|
This chapter presents the Oracle Performance Manager performance and tuning charts for Oracle Real Application Clusters. You must install and configure Oracle Performance Manager to display the charts. This chapter describes only Oracle Performance Manager features specific to Real Application Clusters. Use this chapter as a supplement to general information contained in the Getting Started with the Oracle Standard Management Pack.
This chapter covers the following topics:
See Also:
Oracle stores tuning and performance information for a Real Application Clusters database in a set of dynamic performance tables known as V$ fixed views. Each active instance has its own set of fixed views. You can use Oracle Performance Manager to 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 charts for Real Application Clusters. The statistics represent the aggregate performance of all instances of a cluster database running on cluster. 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 several key statistics on one chart.
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 Real Application Clusters in peak operating condition. The Oracle Performance Manager, part of the Diagnostics Pack, is an available option to Oracle Enterprise Manager. It is an application designed to capture, compute, and present performance data that help database administrators analyze key performance metrics.
Oracle Performance Manager can be run with or without Oracle Enterprise Manager. If you choose to run it as a standalone product, Oracle Enterprise Manager does not have to be configured.
Real Application Clusters performance metrics are compiled into charts that are viewable with Oracle Performance Manager, as shown in Table 7-1.
Table 7-1 Performance Charts
| Chart | Description |
|---|---|
|
Displays the ping count on all instances of the cluster database |
|
| Displays three time out and wait statistics | |
| Shows the average global cache consistent read (CR) request time, the global cache cr timeouts, and the Global Cache Service Processes (LMSn) use | |
| Shows the global cache lock converts for a cluster database | |
|
Identifies the instance that is contributing the most to the ping count |
|
| Displays global cache consistent read (CR) timeouts for all instances of a cluster database | |
| Measures global cache freelist waits for all instances of a cluster database | |
| Displays global cache consistent read (CR) requests for all instances of a cluster database | |
| Displays row cache lock converts for all instances of a cluster database | |
| Identifies Ping count for all database files of the cluster database | |
| Shows the total number of pings for a given file from each instance of a cluster database | |
| Shows the ping count for all block classes of a cluster database | |
| Shows the ping count for all database objects in a cluster database | |
| Shows the ping count for a given database objects by each instance of a cluster database | |
| Displays the most frequently pinged block numbers | |
| Displays the waiting time for library cache lock for the entire cluster database | |
| Shows library cache locks for all instances of a cluster database | |
|
This chart displays row cache locks for a cluster database (Data dictionary contention) |
|
| Displays row cache locks for all instances of a cluster database | |
| Displays the average global cache current block request time and average global cache current block serve time | |
| Displays the same information as the Global Cache Current Block Request Chart, but on a per instance basis | |
| Shows global cache current block requests for all instances of a cluster database. (Identifies the instance that is causing the maximum pinging activity through the interconnect) | |
|
Displays the rate of physical reads and writes for all files in the cluster database |
|
|
Displays the rate of reads and writes per datafile in the cluster database |
|
|
Displays the rate of reads and writes per instance in the cluster database |
|
|
Displays the statistics on the lock activity rate for all the different lock types across all instances of the cluster database |
|
|
Displays the sessions attached to the cluster database and related information |
|
|
Displays the total number of user sessions logged on to the cluster database |
|
|
Displays the number of users logged on to the cluster for each instance |
To use the Oracle Enterprise Manager Console, start the following components:
To display charts:
The chart is displayed in a separate window.
The following describes the use of the charts available in the current release. Note that if you are running software version 8.1.7 or earlier, you will have access to a different subset of these charts:
This chart is used to get the forced disk write or ping count for a cluster database. When the ping count is converted to ping rate, an event is defined against the ping rate with a specified threshold limit. When ping rate exceeds this limit, the event will be triggered to notify you about excessive forced disk writing activity in the system.
This chart displays three time out and wait statistics on:
This chart shows, in milliseconds, for the entire cluster database:
The average CR request time is defined as the ratio of sum of global cache CR block receive time and multiplied by 10 to the sum of global cache cr blocks received. The global cache CR block receive time is defined as total time taken for a consistent read request to complete. The global cache CR blocks received is defined as the count of CR blocks received from another instance when it cannot satisfy the CR block request from its local cache. The global cache CR timeouts refer to CR requests that have long delays and have timed out. The Global Cache Service (GCS) use is defined as the ratio of global cache CR block serve time to the global cache current blocks served. These statistics are defined as follows: The global cache CR block serve time represents the accumulated time that it took the Global Cache Service process to serve a consistent read block request. For each request, the start time is recorded immediately after the Global Cache Service process takes a request off the request queue. The interval is completed after the block is sent. The global cache cr blocks served is the number of requests for a consistent read block served by the Global Cache Service process.
This chart displays the average global cache convert time and average global cache get time, in milliseconds, across the entire cluster database. The global cache gets is defined as the count of new locks opened by an instance. The global cache get time is defined as the total amount of time including any waiting time taken to process a global cache get request. The global cache converts is defined as the count of lock conversions for existing locks for an instance. The global cache convert time is defined as the total amount of time including any waiting time taken to process a global cache convert request. The average convert time is defined as the ratio of sum of global cache convert time and multiplied by 10 to the sum of global cache converts for all instances of a cluster database. The average get time is defined as the ratio of sum of global cache get time and multiplied by 10 to the sum of global cache gets for all instances of a cluster database.
This chart is a drilldown from the Total Ping Chart. It is used to identify the instance that is contributing the most to the ping count. If certain instances generate more pings than others, this can indicate that there is contention among their workloads.
This chart measures the number of requests for a consistent read (CR) block that have long delays and have timed out. If the rate of change of global cache CR timeouts is more than zero for any instance, then this indicates that it is experiencing high IPC, slow interconnect network, or dropped network packets. When this happens, an EM event related to IPC issues will be generated.
This chart displays the number of times a lock convert request by an instance in the Global Cache Service is timed out. This is useful for measuring high contention or I/O workload on the disk. If the rate of change of global cache convert timeouts is more than zero for any instance, then this indicates that it is experiencing high I/O workload activity on the disk. When this happens you will be alerted to look into disk problems, deadlocks, and so forth.
This chart measures the usage of releasable locks. If the rate of change of global cache freelist waits is more than zero for any instance, then this indicates that more locks are consumed than are released by LMS process.
This chart displays the same information as in Global Cache CR Request Chart, on per instance basis. The only difference between this chart and the Global Cache CR Request Chart is that these statistics should be read per instance and not for the entire cluster database.
This chart displays the same information as in Global Cache Lock Convert chart, on a per instance basis. Here the average convert time is defined as the ratio of sum of global cache convert time and multiplied by 10 to the sum of global cache converts for each instance of a cluster database. Similarly, the average get time is defined as the ratio of sum of global cache get time and multiplied by 10 to the sum of global cache gets for each instance of a cluster database.
This chart displays the total number of pings for all datafiles of the cluster database. By using this chart you can identify the database file that has the maximum ping count. You should consider physically distributing these files to reduce I/O delays. Another option would be to partition the application to increase the percentage of local work on the objects contained in the files to reduce the excessive pinging.
This chart is a drilldown from the Ping by File chart. Having identified the file with highest ping count, by using this chart you can determine how much each instance is contributing to this file's ping count. Once the instance has been identified, you should consider localizing the contents of this file to the node on which this instance runs for reducing the pings.
This chart displays the total number of pings experienced by each block class for all instances of a cluster database. Once the top pinged block class has been identified, use the V$PING view to obtain details about a particular table or index and the file and block numbers on which there is significant lock conversion activity. An additional drilldown chart (not visible at the top level) is available. It displays the total number of pings experienced by each block class for a given instance of a cluster database.
This chart is displayed as a drill-down from Instance Ping chart. This chart displays the number of pings for all database objects in a given file by each instance of a cluster database. The database objects with the most pings are considered hot spots and sources of performance problems. You should consider identifying the transactions that are accessing these hot spot objects by querying V$SQLAREA view to get the sql_text. After the transactions are identified, you should consider binding these transactions to a single instance to reduce cross-pinging from other instances. Another option would be to partition the data in this object by using hashing so that each transaction gets its data from its partitioned data range.
This chart is a drilldown from Ping by Object chart. This chart displays the total number of pings contributed by each instance of a cluster database to the given database object. This helps in identifying the instance that is causing the most pings for a given database object. Once the instance has been identified, you should consider making this object data locally available to the instance.
This chart displays the most frequently pinged block numbers for a given database object in a database file. The most pinged block numbers are the hot spots and sources of contention between instances. After identifying these block numbers, you should consider reducing the number of rows contained in these blocks and redistributing the data. One way to do this would be to reduce the database block size.
DLM_LOCK_REQUESTS, DLM_PIN_REQUESTS, and DLM_INVALIDATIONS statistics for the entire cluster database. These statistics are defined as follows: The DLM_LOCK_REQUESTS statistics represents the number of times a lock was requested for a database object. The DLM_PIN_REQUESTS statistics represents the number of times a pin was requested for a database object. The DLM_INVALIDATIONS statistic represents the number of invalidation pings received from other instances.
This chart displays the same information as the Library Cache Lock chart, on per instance basis. The only important difference in the definition is that here these statistics should be read as per instance and not for the entire cluster database.
This chart displays DLM_REQUESTS, DLM_CONFLICTS, and percentage of DLM_REQUESTS that result in DLM_CONFLICTS for the whole cluster database. These are useful for analyzing dictionary contention. The DLM_REQUESTS represents the number of DLM requests. A DLM request for a lock is issued for each consistent-read block request. The DLM_CONFLICTS represents the number of DLM lock request conflicts. A DLM conflict occurs when another instance already owned a lock on a CR block in mode that conflicts with the mode requested by this instance. The percentage value is computed as the ratio of total number of DLM_CONFLICTS to the total number of DLM_REQUESTS for all instances of a cluster database.
This chart displays the same information as Row Cache Lock Chart, but on a per instance basis. The only important difference in the definition is that here these statistics should be read as per instance and not for the entire cluster database.
This chart displays the average global cache current block request time and average global cache current block serve time, in milliseconds, for the entire cluster database. The average global cache current block request time is defined as the ratio of global cache current block receive time and multiplied by 10 to the global cache current blocks received. The average global cache current block serve time is defined as the ratio of global cache current block serve time and multiplied by 10 to the global cache current blocks served.
This chart displays the same information as Global Cache Current Block Request Chart, but on a per instance basis. The only important difference in the definition is that here these statistics should be read as per instance and not for the entire cluster database.
This chart can be used to determine how this instance's pings are themselves distributed across the interconnect. This chart displays global cache current block pin time, global cache current block flush time, and global cache current block send time, in milliseconds, for the given instance. This helps in pin pointing the cause of excessive pinging activity for the given instance, such as whether the given instance is spending too much time in block pinning to the local cache, block flushing to the disk, or block sending across the interconnect.
This chart displays the rate of physical reads and writes for all files in the database. You can drill down to obtain the same information either at the instance level or at the file level
This chart displays the rate of reads and writes per datafile in the database
This chart displays the rate of reads and writes per instance in the database
This chart displays the statistics on the lock activity rate for all the different lock types across the cluster. You can drill down to obtain lock activity information for a particular lock type at the instance level. The global cache usability enhancements in Oracle9i Release 1 (9.0.1) simplified the lock activity histograms.
This chart displays the sessions attached to the cluster database and related information, such as instance name, session ID, session serial number, process ID, status, and user name
This chart displays the total number of user sessions logged on to the cluster database, regardless of whether activity is generated. This information is also available for each instance.
This chart displays the number of users logged on to each instance
This chart displays the total number of active users on the cluster database
This chart displays the number of active users on each instance of the cluster database.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|