About Monitoring Real-Time Database Performance

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

If you find a performance 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,Manually Running ADDM to Analyze Current Database Performance

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

 4.1 Monitoring User Activity

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 chart on the Performance page shows the average active sessions for CPU usage and wait classes in the time period.you can drill down from the chart to identify the causes of instance-related performance issues and resolve them.

To monitor user activity:

1. Access the Database Home page.

To access the Database Home page in Cloud Control:

1. Start Cloud Control.

   The URL for accessing Cloud Control has the following syntax:

   http://

   hostname.domain:portnumber

   /em

2. In the Welcome page, enter your Cloud Control user name and password, and then click Login.

3. From the Targets menu, select Databases.

   The Databases page appears.

4. In the Databases page, select Search List to display a list of the available target databases.

5. In the Name column, select the target database that you want to observe or modify.

   The home page for the target database appears. The first time that you select an option from some of the menus, such as the Performance menu, the Database Login page appears.

6. In the login page for the target database, log in as a user with the appropriate privileges. For example, to log in as user 

   SYS

    with the 

   SYSDBA

    privilege:
   • User Name: Enter 

     SYS

     .
   • Password: Enter the password for the 

     SYS

      user.
   • Connect As: From the Role list, select SYSDBA.

 2.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.

  https://oracle-db-19c:5501/em/

3.Locate any spikes or other areas of interest in the Average Active Sessions stacked area chart.

4.To identify each wait class, move your cursor over the area in the Average Active Sessions chart that corresponds to the class.

The corresponding wait class is highlighted in the chart legend.

5.Click the largest area of color on the chart or the corresponding wait class in the legend to drill down to the wait class with the most average active sessions.

If you click CPU or CPU Wait, then the Active Sessions Working: CPU + CPU Wait page appears. If you click a different wait class, such as User I/O, then an Active Sessions Waiting page for that wait class appears.

You can view the details of wait classes in different dimensions by proceeding to one of the following sections:

• "Monitoring Top SQL"
• "Monitoring Top Sessions"
• "Monitoring Top Services"
• "Monitoring Top Modules"
• "Monitoring Top Actions"
• "Monitoring Top Clients"
• "Monitoring Top PL/SQL"
• "Monitoring Top Files"
• "Monitoring Top Objects"

6.To change the selected time interval, drag the shaded area on the chart to a different interval.The information contained in the Detail for Selected 5 Minute Interval section is automatically updated to display the selected time period.

7.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.
• Click Run ADDM Now to create a snapshot manually.
• Click Run ASH Report to create an Active Session History (ASH) report to analyze transient, short-lived performance problems.

4.1.1 Monitoring Top SQL

On the Active Sessions Working page, the Top Working SQL table shows the database activity for actively running SQL statements that are consuming CPU resources. The Activity (%) column shows the percentage of this activity consumed by each SQL statement. If one or several SQL statements are consuming most of the activity, then you should investigate them.

To monitor the top working SQL statements:

1. Access the Performance page, as explained in "Monitoring User Activity".

2. In the Average Active Sessions chart, click the CPU or CPU Wait area on the chart or the corresponding wait class in the legend.

   The Active Sessions Working page appears.

3. In the Top Working SQL table, click the SQL ID link of the most active SQL statement. For example, in Figure 4-2, the SQL ID of the most active SQL statement is 

   ddthrb7j9a63f

   .

   The SQL Details page appears.

   For SQL statements that are consuming the majority of the wait time, use SQL Tuning Advisor or create a SQL tuning set to tune the problematic SQL statements.

 4.1.2 Monitoring Top Sessions

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. If a single session is consuming the majority of database activity, then you should investigate it.

To monitor the top working sessions:

Access the Performance page

In the Average Active Sessions chart, click the CPU or CPU Wait area on the chart or the corresponding wait class in the legend.

The Active Sessions Working: CPU + CPU Wait page appears

Under Detail for Selected 5 Minute Interval, in the Top Working Sessions section, from the View list select Top Sessions.

The Top Working Sessions table appears. The table displays the top sessions waiting for the corresponding wait class during the selected time period.

In the Top Working Sessions table, click the Session ID link of the session consuming the most database activity.

The Session Details page appears.

This page contains information such as session activity, session statistics, open cursors, blocking sessions, wait event history, and parallel SQL for the selected session.

If a session is consuming too much database activity, then consider clicking Kill Session, and then tuning the SQL statement.

4.1.3 Monitoring Top Services

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

 4.1.4 Monitoring Top Modules

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.

4.1.5 Monitoring Top Actions 

Actions represent the jobs that are performed by a module. For example, the 

DBMS_SCHEDULER

 module can run the 

GATHER_STATS_JOB

 action to gather statistics on all database objects. If a single action is using the majority of the wait time, then you should investigate it.

 4.1.6 Monitoring Top Clients

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.

 4.1.7 Monitoring Top PL/SQL

If a single PL/SQL subprogram is using the majority of the wait time, then you should investigate it.

 4.1.8 Monitoring Top Files

Data on the average wait time for specific files is available from the Active Sessions Waiting: User I/O page.

4.1.9 Monitoring Top Objects

Data on the top database objects waiting for resources is available from the Active Sessions Waiting: User I/O page.

4.2 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. you can also customize the Performance page so that the most useful instance activity charts are displayed by default.

4.2.1 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.

 4.2.2 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.

4.2.2.1 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, Streams 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.

4.2.2.2 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.

4.2.2.3 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.

4.2.3 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.

4.2.4 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.

4.3 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.

4.3.1 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.

4.3.2 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.

 4.3.3 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.

4.4 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).

4.5 Customizing the Database Performance page

You can customize the Performance page so that it specifically addresses your requirements. As explained in "Monitoring Instance Activity", you can specify which charts you want to appear by default in the Performance page, and how you want them to appear. You can also decide whether to include baseline values in the Throughput and Services charts.

Cloud Control stores persistent customization information for each user in the repository. Cloud Control retrieves the customization data when you access the Performance page and caches it for the remainder of the browser session until you change the settings.