Monitoring Real-Time Database Performance

4 Monitoring Real-Time Database Performance

The Automatic Database Diagnostic Monitor (ADDM) automatically identifies performance problems with the database, as described in Automatic Database Performance Monitoring. Information on performance appears on the Performance page in Oracle Enterprise Manager Cloud Control (Cloud Control).

By drilling down to other pages from the Performance page, you can identify database performance problems in real time. If you find a problem, then you can run ADDM manually to analyze it immediately without having to wait until the next ADDM analysis. To learn how to run ADDM manually, see "Manually Running ADDM to Analyze Current Database Performance".

This chapter describes how to monitor some aspects of database activity. It contains the following sections:

Monitoring User Activity

As described in Oracle Database Performance Method , database time (DB time) is an indicator of the total database instance workload. The average active sessions for a time period equals the total database time of all user sessions during the period divided by the elapsed time (wall-clock time) for the period.

The Average Active Sessions graph on the Performance Hub page shows the average active sessions for CPU usage and wait classes in the time period. You can drill down by clicking on the graph to identify the causes of instance-related performance issues and resolve them.

To monitor user activity:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Hub and ASH Analytics.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance Hub page appears.
Locate any spikes or other areas of interest in the Average Active Sessions stacked area chart.

Figure 4-1 shows an example of one dimension of the Average Active Sessions chart on the Performance Hub page.

Figure 4-1 Average Active Sessions by Wait Class

Description of "Figure 4-1 Average Active Sessions by Wait Class"

Each color-filled area on the stacked area chart shows the average active sessions for the specified event at the specified time. In the chart, the average active sessions amount for each event is stacked upon the one below it. The events appear on the chart in the order shown in the legend, with CPU starting at zero on the y-axis and the other events stacked in ascending order, from CPU Wait to Other. The wait classes show how much database activity is consumed by waiting for a resource such as disk I/O.

The CPU Cores line at 2 on the y-axis indicates the number of CPUs on the host system. When the CPU value reaches the CPU Cores line, the database instance is consuming 100 percent of CPU time on the system.

The last item in the graph title Average Active Sessions by Wait Class is a drop-down menu for other dimensions to view the performance. Wait Class is the default.

The main dimensions are:
- Wait Class
- Wait Event
- Instance
- Service
- Module
- Action
- User Session
- SQL ID
The Active Sessions Working page shows a 1-hour timeline. Details for each wait class are shown in 5-minute intervals.

You can view the details of wait classes in different dimensions by proceeding to one of the following sections:
To change the selected time interval, use the Performance Hub view and drag start and end times to a different interval.

Figure 4-2 Average Active Sessions by "Wait Class"

Description of "Figure 4-2 Average Active Sessions by "Wait Class""

The information contained in the Average Active Sessions is automatically updated to display the selected time period.

If you discover a performance problem, then you can attempt to resolve it in real time. On the Performance page, do one of the following:

Below the Average Active Sessions chart, click the snapshot corresponding to the time when the performance problem occurred to run ADDM for this time period.

For information about ADDM analysis, see "Reviewing the Automatic Database Diagnostic Monitor Analysis".
Click Run ADDM Now to create a snapshot manually.

For information about creating snapshots manually, see "Creating Snapshots". For information about running ADDM manually, see "Manually Running ADDM to Analyze Current Database Performance".
Click Run ASH Report to create an Active Session History (ASH) report to analyze transient, short-lived performance problems.

For information about ASH reports, see "Active Session History Reports".

Monitoring Top Dimensions

The Average Active Sessions by drop-down contains a Top Dimensions fly-out with the following common views:

Wait Class
Wait Event
Instance
Service
Module
Action
User Session
SQL ID

To monitor a Top Dimension:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto Top Dimensions, and select the desired view.
- Wait Class
- Wait Event
- Instance
- Service
- Module
- Action
- User Session
- SQL ID
Graphs, tables, and information on the page are updated to reflect the selected criteria.

Monitoring SQL

The Average Active Sessions by drop-down contains a SQL fly-out with the following common views:

SQL ID
Top Level SQL ID
SQL Force Matching Signature
SQL Plan Hash Value
SQL Full Plan Hash Value
SQL Plan Operation
SQL Plan Operation Line
SQL Opcode
Top Level SQL Opcode

To monitor SQL:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto SQL, and select the desired view from the fly-out.
- SQL ID
- Top Level SQL ID
- SQL Force Matching Signature
- SQL Plan Hash Value
- SQL Full Plan Hash Value
- SQL Plan Operation
- SQL Plan Operation Line
- SQL Opcode
- Top Level SQL Opcode
Graphs, tables, and information on the page are updated to reflect the selected criteria.

See Also:

Monitoring PL/SQL

The Average Active Sessions by drop-down contains a PL/SQL fly-out with the following common views:

PL/SQL
Top Level PL/SQL

To monitor PL/SQL:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto PL/SQL, and select the desired view from the fly-out.
- PL/SQL
- Top Level PL/SQL
Graphs, tables, and information on the page are updated to reflect the selected criteria.

Monitoring Resource Consumption

The Average Active Sessions by drop-down contains a Resource Consumption fly-out with the following common views:

Wait Class
Wait Event
Object
Blocking Session

A session is a logical entity in the database instance memory that represents the state of a current user login to the database. A session lasts from the time a user logs in to the database until the user disconnects. For example, when a user starts SQL*Plus, the user must provide a valid database user name and password to establish a session. If a single session is consuming the majority of database activity, then you should investigate it.

To monitor resource consumption:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto Resource Consumption, and select the desired view from the fly-out.
- Wait Class
- Wait Event
- Object
- Blocking Session
Graphs, tables, and information on the page are updated to reflect the selected criteria.

Monitoring Session Identifiers

The Average Active Sessions by drop-down contains a Sessions Identifiers fly-out with the following common views:

Instance
Service
User Session
Parallel Process
User Name
Program
Session Type

A service is a group of applications with common attributes, service-level thresholds, and priorities. For example, the SYS$USERS service is the default service name used when a user session is established without explicitly identifying a service name. The SYS$BACKGROUND service consists of all database background processes. If a service is using the majority of the wait time, then you should investigate it.

To monitor session identifiers:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto Session Identifiers, and select the desired view from the fly-out.
- Instance
- Service
- User Session
- Parallel Process
- User Name
- Program
- Session Type
Graphs, tables, and information on the page are updated to reflect the selected criteria.

Monitoring Session Attributes

The Average Active Sessions by drop-down contains a Sessions Attributes fly-out with the following common views:

Consumer Group
Module
Action
Client
Client Host Name
Client Host Port
Transaction ID
Execution Context ID
Database Operation

A client can be a web browser or any client process that initiates a request for the database to perform an operation. If a single client is using the majority of the wait time, then you should investigate it.

Modules represent the applications that set the service name as part of the workload definition. For example, the DBMS_SCHEDULER module may assign jobs that run within the SYS$BACKGROUND service. If a single module is using the majority of the wait time, then it should be investigated.

To monitor session attributes:

Access the Performance Hub page, as explained in "Monitoring User Activity".
Click on the drop-down in Average Active Sessions by, mouse onto Sessions Attributes, and select the desired view from the fly-out.
- Consumer Group
- Module
- Action
- Client
- Client Host Name
- Client Host Port
- Transaction ID
- Execution Context ID
- Database Operation
Graphs, tables, and information on the page are updated to reflect the selected criteria.

Monitoring Instance Activity

Below the Average Active Sessions chart on the Performance page are other charts that you can use to monitor database instance activity. As explained in "Customizing the Database Performance page", you can also customize the Performance page so that the most useful instance activity charts are displayed by default.

You can use the instance activity charts to perform the following tasks:

Monitoring Throughput

Database throughput measures the amount of work the database performs in a unit of time. The Throughput charts show any contention that appears in the Average Active Sessions chart.

Compare the peaks on the Throughput charts with the peaks on the Average Active Sessions chart. If the Average Active Sessions chart displays a large number of sessions waiting, indicating internal contention, but throughput is high, then the situation may be acceptable. The database is probably also performing efficiently if internal contention is low but throughput is high. However, if internal contention is high but throughput is low, then consider tuning the database.

To monitor throughput:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Home.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
Click the Throughput tab.
Select one of the following Instance Throughput Rate options.
- Per Second
  
  Two charts appear. One shows the number of logons and transactions per second and the other shows the physical reads and redo size per second.
  
  Figure 4-3 shows the Throughput charts with the Instance Throughput Rate of Per Second selected. The bar in the middle of the figure indicates a portion of the charts (from approximately 1:37 to 1:52) that has been removed for space considerations. In Figure 4-3, the most transactions occurred from 1:15 to 1:27 p.m. and from 2:08 to 2:12 p.m.
- Per Transaction
  
  One chart appears that shows the number of physical reads and redo size per transaction.

Figure 4-3 Monitoring Throughput

Description of "Figure 4-3 Monitoring Throughput"

Monitoring I/O

The I/O charts show I/O statistics collected from all database clients. The I/O wait time for a database process represents the amount of time that the process could have been doing useful work if a pending I/O had completed. Oracle Database captures the I/O wait times for all important I/O components in a uniform fashion so that every I/O wait by any Oracle process can be derived from the I/O statistics.

Figure 4-4 Monitoring I/O

Description of "Figure 4-4 Monitoring I/O"

The Latency for Synchronous Single Block Reads chart shows the total perceived I/O latency for a block read, which is the time difference between when an I/O request is submitted and when the first byte of the transfer arrives. Most systems are performing satisfactorily if latency is fewer than 10 milliseconds. This type of I/O request is the best indicator of I/O performance for the following reasons:

Write operations may exhibit good performance because of write caches in storage.
Because multiblock I/O requests have varying sizes, they can take different amounts of time.
The latency of asynchronous I/O requests does not represent the full I/O wait time.

The other charts shown depend on your selection for I/O Breakdown, as described in the following sections:

Monitoring I/O by Function

The I/O Function charts determine I/O usage level by application or job. The component-level statistics give a detailed view of the I/O bandwidth usage, which you can then use in scheduling jobs and I/O provisioning. The component-level statistics fall in the following categories:

Background type

This category includes ARCH, LGWR, and DBWR.
Activity

This category includes XDB, Advanced Queuing (AQ), Data Pump, Recovery, and RMAN.
I/O type

The category includes the following:
- Direct Writes
  
  This write is made by a foreground process and is not from the buffer cache.
- Direct Reads
  
  This read is physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.
- Buffer Cache Reads
Others

This category includes I/Os such as control file I/Os.

To monitor I/O by function:

Access the Performance page, as explained in "Monitoring User Activity".
In the instance activity chart, click I/O.

The Latency for Synchronous Single Block Reads, I/O Megabytes per Second, and I/O Requests per Second charts appear.
For I/O Breakdown, select I/O Function.

The I/O Megabytes per Second by I/O Function and I/O Requests per Second by I/O Function charts appear.

The example in Figure 4-4 shows that a significant amount of I/O is being performed by the log writer. The log writer activity peaked at approximately 600 I/O requests per second.
Click the largest colored area on the chart or the corresponding function in the legend to drill down to the function with the highest I/O rate.

An I/O Throughput by I/O Function page appears with details about the selected category.

You can view real-time or historical data for details on I/O megabytes or I/O requests.

See Also:

Oracle Database Concepts to learn about database background processes such as ARCH, LGWR, and DBWR

Monitoring I/O by Type

The I/O Type charts enable you to monitor I/O by the types of read and write operations. Small I/Os are requests smaller than 128 KB and are typically single database block I/O operations. Large I/Os are requests greater than or equal to 128 KB. Large I/Os are generated by database operations such as table/index scans, direct data loads, backups, restores, and archiving.

Figure 4-5 Performance Monitoring I/O by Type

Description of "Figure 4-5 Performance Monitoring I/O by Type"

When optimizing for short transaction times, such as in an OLTP environment, monitor latency for small I/Os. High latencies typically indicate that the storage system is a bottleneck.

When optimizing for large queries, such as in a data warehouse, performance depends on the maximum throughput the storage system can achieve rather than the latency of the I/O requests. In this case, monitor the I/O megabytes per second rather than the synchronous single-block I/O latencies.

To monitor I/O by type:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Home.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
In the instance activity area, click I/O tab.

The I/O Megabytes per Second and I/O Requests per Second graphs appear.
For I/O Breakdown, select I/O Type.

The I/O Megabytes per Second by I/O Type and I/O Requests per Second by I/O Type charts appear.
Click the largest colored area on the chart or the corresponding function in the legend to drill down to the function with the highest I/O rate.

The I/O Details page appears.

You can view real-time or historical data for details on I/O megabytes or I/O requests.

Monitoring I/O by Consumer Group

When Oracle Database Resource Manager is enabled, the database collects I/O statistics for all consumer groups that are part of the currently enabled resource plan. The Consumer Group charts enable you to monitor I/O by consumer group.

A resource plan specifies how the resources are to be distributed among various users (resource consumer groups). Resource consumer groups enable you to organize user sessions by resource requirements. Note that the _ORACLE_BACKGROUND_GROUP_ consumer group contains I/O requests issued by background processes.

Description of perf_io_consum.png follows

Description of the illustration perf_io_consum.png

To monitor I/O requests by consumer group:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Home.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
In the instance activity area, click I/O tab.

The I/O Megabytes per Second and I/O Requests per Second graphs appear.
For I/O Breakdown, select Consumer Group.

The I/O Megabytes per Second by Consumer Group and I/O Requests per Second by Consumer Group graphs appear.

Monitoring Parallel Execution

The Parallel Execution charts show system metrics related to parallel queries. Metrics are statistical counts per unit. The unit could be a time measure, such as seconds, or per transaction, or session.

A parallel query divides the work of executing a SQL statement across multiple processes. The charts show parallel queries that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.

Figure 4-6 Monitoring Parallel Execution

Description of "Figure 4-6 Monitoring Parallel Execution"

To monitor parallel execution:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Home.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
In the instance activity chart, click Parallel Execution tab.

The Parallel Execution charts appear.

Two pairs of charts are shown. The first pair shows the number of sessions on the y-axis, whereas the second pair shows the per second rate on the y-axis.

Monitoring Services

Services represent groups of applications with common attributes, service-level thresholds, and priorities. For example, the SYS$USERS service is the default service name used when a user session is established without explicitly identifying a service name.

Figure 4-7 Monitoring Services

Description of "Figure 4-7 Monitoring Services"

To monitor services:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Home.

If the Database Login page appears, then log in as a user with administrator privileges. The Performance page appears.
In the instance activity chart, click Services.

The Services chart appears. The Services chart shows services waiting for the corresponding wait event during the time period shown. Only active services are shown.

In Figure 4-7, the SYS$USERS service has the greatest number of active sessions.
Click the largest colored area on the chart or the corresponding service in the legend to drill down to the service with the highest number of active sessions.

The Service page appears, showing the Activity subpage.

You can view real-time data showing the session load for all wait classes associated with the service.

Monitoring Host Activity

The Host chart on the Performance page displays utilization information about the system hosting the database.

To determine if the host system has enough resources available to run the database, establish appropriate expectations for the amount of CPU, memory, and disk resources that your system should be using. You can then verify that the database is not consuming too many of these resources.

To view details about CPU, memory, and disk utilization:

From the Targets menu, select Hosts.

The Hosts page appears.
In the list of hosts, click the name of the host on which your database resides.

The hostname page appears, where hostname is the name of the host.
Determine whether sufficient resources are available and whether your system is using too many resources.

For example, determine the amount of CPU, memory, and disk resources the database uses in the following scenarios:
- When your system is idle, or when little database and nondatabase activity exists
- At average workloads
- At peak workloads
Workload is an important factor when evaluating the level of resource utilization for your system. During peak workload hours, 90 percent utilization of a resource, such as a CPU with 10 percent idle and waiting time, can be acceptable. However, if your system shows high utilization at normal workload, then there is no room for additional workload.

Perform the following tasks to monitor the host activity for your database:
Set the appropriate threshold values for the performance metrics so the system can automatically generate alerts when these thresholds are exceeded.

For information about setting metric thresholds, see "Setting Metric Thresholds for Performance Alerts".

Monitoring CPU Utilization

To address CPU problems, first establish appropriate expectations for the amount of CPU resources your system should be using. You can then determine whether sufficient CPU resources are available and recognize when your system is consuming too many resources. This section describes how to monitor CPU utilization.

To monitor CPU utilization:

Access the hostname page as explained in "Monitoring Host Activity".
From the Host drop-down menu, select Monitoring, and then CPU Details.

The CPU Details page appears.

This page contains statistics about CPU utilization, I/O wait times, and load gathered over the last hour. The top 10 processes are listed based on CPU utilization.
Verify the current CPU utilization using the CPU Utilization chart.

The CPU Utilization chart shows CPU utilization over the last hour and a half. The current value is displayed below the chart. During standard workload hours, the value should not exceed the critical threshold.
Under the graph for CPU Utilization, click the link CPU Utilization.

This page contains CPU utilization statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then the CPU performance problem should be investigated.
Return to the CPU Details page. From the Host menu, select Monitoring, and then CPU Details.
Verify the current CPU I/O wait time using the CPU I/O Wait chart.

The CPU I/O Wait chart shows CPU I/O wait time over the last hour and a half. The current value is displayed below the chart. During normal workload hours, the value of CPU I/O wait should not exceed the warning threshold.

CPU I/O wait represents the average number of jobs waiting for I/O during an interval.
Under the graph for CPU I/O Wait, click the link CPU I/O Wait.

The CPU in I/O Wait page appears.

This page contains CPU I/O wait statistics and related alerts generated over the last 24 hours.

If you notice an unexpected increase in this value that is sustained through standard workload hours, then a CPU performance problem may exist.
Return to the CPU Details page. From the Host menu, select Monitoring, and then CPU Details.
Verify the current CPU load using the CPU Load chart.

The CPU Load chart shows the CPU load over the last hour and a half. The current value is displayed below the chart. During standard workload hours, the value of CPU load should not exceed the warning threshold.

CPU load represents the average number of processes waiting to be scheduled for CPU resources in the previous minute, or the level of CPU contention time over time.
Under the graph for CPU Load, click the link CPU Load.

The Run Queue Length (5 minute average) page appears.

This page contains CPU load statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a CPU performance problem might exist.
Return to the CPU Details page. From the Host menu, select Monitoring, and then CPU Details.
Review the Top 10 Processes (ordered by CPU) table.

If a process is consuming too much of the CPU utilization percentage, then investigate that process.
If a CPU performance problem is identified, then you can try to resolve the issue by doing the following:
- Use Oracle Database Resource Manager to reduce the impact of peak-load-use patterns by prioritizing CPU resource allocation
- Avoid running too many processes that use a large amount of CPU
- Increase hardware capacity, including changing the system architecture

See Also:

Oracle Database Performance Tuning Guide for information about resolving CPU issues
Oracle Database Administrator's Guide for information about Oracle Database Resource Manager

Monitoring Memory Utilization

Operating system performance issues commonly involve process management, memory management, and scheduling. This section describes how to monitor memory utilization and identify problems such as paging and swapping.

To monitor memory utilization:

Access the hostname page as explained in "Monitoring Host Activity".
From the Host drop-down menu, select Monitoring, and then Memory Details.

The Memory Details page appears.

This page contains statistics about memory utilization, page scan rates, and swap utilization gathered over the last hour. The top 10 processes are also listed ordered by memory utilization. Figure 4-8 shows a portion of the Memory Details page. The Top 10 Processes (ordered by Memory) section is not shown.

Figure 4-8 Memory Details Page

Description of "Figure 4-8 Memory Details Page"
Verify the current memory page scan rate using the Memory Page Scan Rate chart.

The current value of the memory page scan rate is displayed below the chart. On UNIX and Linux, this value represents the number of pages scanned per second. On Microsoft Windows, this value represents the rate at which pages are read from or written to disk to resolve hard page faults. This value is a primary indicator of the types of faults that may be causing system-wide delays.
Click Memory Scan Rate (pages per second).

The Memory Page Scan Rate page appears.

This page contains memory page scan rate statistics and related alerts over the last 24 hours.

If you notice an unexpected increase in this value that is sustained through standard workload hours, then a memory performance problem might exist.
Return to the Memory Details page. From the Host drop-down menu, select Monitoring, and then Memory Details.
Using the Memory Utilization chart, verify the current memory utilization.

The Memory Utilization chart shows how much memory is being used. The current value of memory utilization is displayed below the chart. During standard workload hours, the value should not exceed the warning threshold (shown in yellow).
Click Memory Utilization (%).

The Memory Utilization page appears.

This page contains memory utilization statistics and related alerts generated over the last 24 hours.

Description of the illustration mem_util.gif

In this example, memory utilization has exceeded 80%, so warnings appear in the Metric Alert History table.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a memory performance problem might exist.
Return to the Memory Details page. From the Host drop-down menu, select Monitoring, and then Memory Details.
Using the Swap Utilization chart, verify current swap utilization.

The Swap Utilization chart shows how much swap space is being used. The current value of swap utilization is displayed below the chart. During normal workload hours, the value should not exceed the warning threshold.
Click Swap Utilization (%).

The Swap Utilization page appears.

This page contains swap utilization statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a memory performance problem might exist.
Return to the Memory Details page. From the Host drop-down menu, select Monitoring, and then Memory Details.
Review the top processes in the Top 10 Processes (ordered by Memory) table.

If a process is taking up too much memory, then this process should be investigated.
If a memory performance problem is identified, then you can attempt to resolve the issue by doing the following:
- Use Automatic Memory Management to automatically manage and distribute memory between the System Global Area (SGA) and the aggregate program global area (PGA aggregate).
- Use the Memory Advisor to set SGA and PGA memory target values.
- Use Automatic PGA Management to manage SQL memory execution.
- Avoid running too many processes that consume large amounts of memory.
- Reduce paging or swapping.
- Reduce the number of open cursors and hard parsing with cursor sharing.

See Also:

Oracle Database Administrator's Guide for information about using Automatic Memory Management
Oracle Database Performance Tuning Guide for information about resolving memory issues

Monitoring Disk I/O Utilization

Because the database resides on a set of disks, the performance of the I/O subsystem is very important to database performance. Important disk statistics include the disk I/Os per second and the length of the service times. These statistics show if the disk is performing optimally or if the storage system is being overworked. This section describes how to monitor disk I/O utilization.

To monitor disk I/O utilization:

Access the hostname page as explained in "Monitoring Host Activity".
From the Host drop-down menu, select Monitoring, and then Disk Details.

The Disk Details page appears.

This page contains disk I/O utilization and service time statistics, and the top disk devices ordered by the percentage of time that they were in use.

Figure 4-9 Disk Details

Description of "Figure 4-9 Disk Details"
Verify the current disk I/O utilization using the Total Disk I/O Made Across All Disks chart.

The Total Disk I/O Made Across All Disks chart shows how many disk I/Os are being performed per second. The current value for total disk I/O per second is displayed below the chart. In Figure 4-9 the value is 153.07.
Click Total Disk I/O made across all disks (per second).

The Total Disk I/O Made Across All Disks (Per Second) page appears.

This page contains disk utilization statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through standard workload hours, then a disk I/O performance problem might exist and should be investigated.
Verify the current I/O service time using the Max Average Disk I/O Service Time (ms) Among All Disks chart.

The Max Average Disk I/O Service Time (ms) Among All Disks chart shows the longest service time for disk I/Os in milliseconds. The current value for longest I/O service time is displayed below the chart. In Figure 4-9 the value is 1.79.
Return to the Disk Details page. From the Host drop-down menu, select Monitoring, and then Disk Details.
Click Max Average Disk I/O (ms) Service Time Among All Disks.

This page contains I/O service time statistics and related alerts generated over the last 24 hours.

If you notice an unexpected spike in this value that is sustained through normal workload hours, then a disk I/O performance problem might exist and should be investigated.
Return to the Disk Details page. From the Host drop-down menu, select Monitoring, and then Disk Details.
On the Disk Details page, verify the disk devices in the Top Disk Devices (ordered by % Busy) table.

If a particular disk is busy a high percentage of the time, then this disk should be investigated.
If a disk I/O performance problem is identified, you can attempt to resolve the problem by doing the following:
- Use Oracle Automatic Storage Management (Oracle ASM) to manage database storage.
- Stripe everything across every disk to distribute I/O.
- Move files such as archived redo logs and online redo logs to separate disks.
- Store required data in memory to reduce the number of physical I/Os.

See Also:

Oracle Database Performance Tuning Guide for information about resolving disk I/O issues

Determining the Cause of Spikes in Database Activity

If you see a spike in database activity in the Performance page, then you can access the ASH Analytics page to find out which sessions are consuming the most database time. This page provides stacked area charts to help you visualize the active session activity from various dimensions, such as Wait Class, Module, Actions, SQL ID, Instance, User Session, Consumer Group, and others. You can drill down into specific members of a dimension (vertical zooming), and zoom in and out of any time period (horizontal zooming).

To view active session activity on the ASH Analytics page:

Access the Database Home page.

See "Accessing the Database Home Page" for more information.
From the Performance menu, select Performance Hub and then ASH Analytics.

If the Database Login page appears, then log in as a user with administrator privileges. The ASH Analytics page appears.

Figure 4-10 shows an example of the ASH Analytics page.

Figure 4-10 ASH Analytics Page

Description of "Figure 4-10 ASH Analytics Page"
To view a high-level perspective of top activity during a selected time period, drag the entire shaded slider area in the top chart to the desired time period.

Tip:

You can change the amount of time selected in the shaded slider area by selecting the control at either edge of the slider and dragging it to the left or right.

You can select a time period within the default setting of one hour or you can use the selector buttons above the chart to display time periods of one day, one week, or one month. You can also use the Calendar and Custom buttons to display a time period other than one of the preset choices.
To view a more detailed perspective of your selected time period, use the Activity chart on the page. By default, the chart shows a breakdown of workload activity by wait classes.
Investigate the impact by viewing detailed statistics for the top activity sessions that are adversely affecting the system.

To view detailed statistics for a session:
1. Select the largest spike in the chart or the corresponding wait class in the legend beside the chart. The viewer now filters out everything in the chart except for the wait class of interest.
  
  For example, if the chart shows that the Concurrency wait class has the biggest spike, select the chart area of the legend for Concurrency. The viewer refreshes the chart and now only shows the Concurrency value and displays a Wait Class: Concurrency icon in the Filters bar.
  
  Tip:
  
  You can create an unlimited number of filters.
2. In the Activity section, select Top Dimensions from the dimensions list. The chart refreshes in response to your selection, displaying values for the particular category you selected.
  
  For instance, if you create a filter for Concurrency as described above, then select Top Dimensions from the list, and then select User Session, the chart shows user sessions only for Concurrency.
  
  Figure 4-11 shows the list of activities with Top Dimensions selected.
  
  Figure 4-11 List of Activities
  
  Description of "Figure 4-11 List of Activities"
Optionally, use the Load Map for a graphical view of system activity.

The Load Map is useful for viewing activity in a single- or multi-dimensional layout when you are not interested in seeing how activity has changed over time during the selected period.

Figure 4-12 shows the load map for activity by wait class and wait events.

Figure 4-12 Load Map on the ASH Analytics Page

Description of "Figure 4-12 Load Map on the ASH Analytics Page"