4 Monitoring Real-Time Database Performance

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.

Figure 4-4 Monitoring Top SQL

Description of "Figure 4-4 Monitoring Top SQL"

In the example shown in Figure 4-4, a single SELECT statement is consuming over 47% of database activity, while four modification DML statements are consuming about 35%. These statements should be investigated.

To monitor the top working SQL statements: 

1. On the Performance page, in the Average Active Sessions chart, click the CPU block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. In the Top Working SQL table, click the SQL ID link of the most active SQL statement.

   The SQL Details page appears.

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

Monitoring Top Sessions

On the Active Sessions Working page, the Top Working Sessions table displays the top sessions waiting for the corresponding wait class during the selected time period. A session is a logical entity in the database instance memory that represents the state of a current user login to the database.

Figure 4-5 Monitoring Top Sessions

Description of "Figure 4-5 Monitoring Top Sessions"

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 the top working sessions: 

1. On the Performance page, in the Average Active Sessions chart, click the CPU Used block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

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

   The Top Working Sessions table appears.

3. 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 events, 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 session's SQL statement.

Monitoring Top Services

The Top Services table displays the top services waiting for the corresponding wait event during the selected time period.

A service is a group of 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 its 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 a service: 

1. On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Services from the View list.

   The Top Services table appears.

   Figure 4-6 Monitoring Top Services

   
   Description of "Figure 4-6 Monitoring Top Services"
   
   

   In the example shown in Figure 4-6, the SYS$USERS service is consuming 86.47% of database activity. This service corresponds to the database sessions for users hr and sh shown in Figure 4-5.

3. Click the Service link of the most active service.

   The Service page appears.

   This page contains information about the modules, activity, and statistics for the selected service.

Monitoring Top Modules

The Top Modules table displays the top modules waiting for the corresponding wait event during the selected time period.

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 a module: 

1. On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Modules from the View list.

   The Top Modules table appears.

   Figure 4-7 Monitoring Top Modules

   
   Description of "Figure 4-7 Monitoring Top Modules"
   
   

3. Click the Module link of the module that is showing the highest percentage of activity.

   The Module page appears.

   This page contains information about the actions, activity, and statistics for the selected module.

   In the example shown in Figure 4-7, the SQL*Plus module is consuming over 84% of database activity and should be investigated. As shown in Figure 4-5, the SQL*Plus session for users sh and hr are consuming a huge percentage of database activity.

Monitoring Top Actions

The Top Actions table displays the top actions waiting for the corresponding wait event during the selected time period.

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.

To monitor an action: 

1. On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Actions from the View list.

   The Top Actions table appears.

   Figure 4-8 Monitoring Top Actions

   
   Description of "Figure 4-8 Monitoring Top Actions"
   
   

3. Click the Action link of the most active action.

   The Action page appears.

   This page contains statistics for the selected action.

   In the example shown in Figure 4-8, the SALES_INFO action associated with the SQL*Plus module is consuming 40.3% of the database activity, while EMP_DML is consuming 39.55% and EMP_QUERY is consuming 4.48%. This information is consistent with Figure 4-5, which shows that the two database sessions for users HR and SH are consuming over 84% of database activity.

Monitoring Top Clients

The Top Clients table displays the top clients waiting for the corresponding wait event during the selected time period. A client can be a Web browser or any client process that initiates requests for an operation to be performed by the database. If a single client is using the majority of the wait time, then you should investigate it.

To monitor a client: 

1. On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Clients from the View list.

   The Top Clients table appears.

   Figure 4-9 Monitoring Top Clients

   
   Description of "Figure 4-9 Monitoring Top Clients"
   
   

3. Click the Client ID link of the most active client.

   The Clients page appears.

   This page contains statistics for the selected client process.

Monitoring Top PL/SQL

The Top PL/SQL table displays the top PL/SQL subprograms waiting for the corresponding wait event during the selected time period. If a single PL/SQL subprogram is using the majority of the wait time, then you should investigate it.

To monitor a PL/SQL subprogram: 

1. On the Performance page, in the Average Active Sessions chart, click a block on the chart or its corresponding wait class in the legend.

   The Active Sessions Working page appears.

2. Under Detail for Selected 5 Minute Interval, select Top PL/SQL from the View list.

   The Top PL/SQL table appears.

   Figure 4-10 Monitoring Top PL/SQL

   
   Description of "Figure 4-10 Monitoring Top PL/SQL"
   
   

3. Click the PL/SQL Subprogram link of the most active subprogram.

   The PL/SQL Subprogram page appears.

   This page contains statistics for the selected subprogram.

   In Figure 4-10, the SYS.DBMS_AQ.LISTEN#2 subprogram is consuming 100% of database activity.

Monitoring Top Files

The Top Files table displays the average wait time for specific files during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

To monitor a file: 

1. On the Performance page, in the Average Active Sessions chart, click the User I/O block on the chart or its corresponding wait class in the legend.

   The Active Sessions Waiting: User I/O page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Files from the View list.

   The Top Files table appears.

   Figure 4-11 Monitoring Top Files

   
   Description of "Figure 4-11 Monitoring Top Files"
   
   

3. Click the Tablespace link of the file with the highest average wait time.

   The View Tablespace page appears.

   In the example shown in Figure 4-11, 75% of the wait times are associated with I/O to the files in the SYSTEM and SYSAUX tablespaces.

Monitoring Top Objects

The Top Objects table displays the top database objects waiting for the corresponding wait event during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

To monitor an object: 

1. On the Performance page, in the Average Active Sessions chart, click the User I/O block on the chart or its corresponding wait class in the legend.

   The Active Sessions Waiting: User I/O page appears.

2. Under Detail for Selected 5 Minute Interval, select Top Objects from the View list.

   The Top Objects table appears.

   Figure 4-12 Monitoring Top Objects

   
   Description of "Figure 4-12 Monitoring Top Objects"
   
   

3. Click the Object Name link of the object with the highest average wait time.

   The View page for the object appears.

   This example in Figure 4-12 shows that over 84% of the waits are for an object whose name is unavailable. Based on the information in Figure 4-4 and Figure 4-5, you can conclude that the performance problem is caused by the query and modification DML statements.

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. The Throughput charts on the Performance page display:

• Number of logons, transactions, physical reads, and redo size per second

• Number of physical reads and redo size per transaction

Figure 4-13 Monitoring Throughput

Description of "Figure 4-13 Monitoring Throughput"

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: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. In the instance activity chart, click Throughput.

   The Throughput charts are shown with Instance Throughput Rate set to the default value of Per Second. You can select Per Transaction to show the throughput rate per transaction.

   In the example in shown in Figure 4-13, the number of transactions and physical reads per second went up around 12:30 p.m. and remained up until around 5 p.m.

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-14 Monitoring I/O

Description of "Figure 4-14 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

• Monitoring I/O by Type

• Monitoring I/O by Consumer Group

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.

Description of the illustration perf_io_type.gif

When are 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: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. In the instance activity chart, click I/O.

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

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

   In this example, the number of small writes per second increased to more than 600. These writes correspond to the log writer I/O requests shown in Figure 4-14.

4. Click the largest block on the chart or its 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 the illustration perf_io_consum.gif

To monitor I/O requests by consumer group: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. In the instance activity chart, click I/O.

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

3. 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 charts 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-15 Monitoring Parallel Execution

Description of "Figure 4-15 Monitoring Parallel Execution"

To monitor parallel execution: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. In the instance activity chart, click Parallel Execution.

   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.

   In the example shown in Figure 4-15, query parallelization was active between 12:30 p.m. to 4 p.m.

Monitoring Services

The Services charts show services waiting for the corresponding wait event during the time period shown. 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 its service name. Only active services are shown.

Figure 4-16 Monitoring Services

Description of "Figure 4-16 Monitoring Services"

To monitor services: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. In the instance activity chart, click Services.

   The Services chart appears.

   In Figure 4-16, the SYS$USERS service has the greatest number of active sessions.

3. Click the largest block of color on the chart or its 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 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: 

1. From the Database Home page, under Host CPU, click the Load link.

   The Host page appears, showing the Performance subpage.

2. Select CPU Details from the View list.

   The CPU Details view appears.

   This view 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.

3. Verify the current CPU utilization using the CPU Utilization chart.

   The CPU Utilization chart shows CPU utilization over the last hour. The current value is displayed below the chart. During standard workload hours, the value should not exceed the critical threshold.

4. Click CPU Utilization.

   The CPU Utilization page appears.

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

   In the following example, the CPU utilization has suddenly spiked from approximately 6% to 99.86%, which is above the warning threshold of 80%.

   
   Description of the illustration cpu_util.gif
   
   

   If you notice an unexpected spike in this value that is sustained through normal workload hours, then the CPU performance problem should be investigated.

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

6. Click 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.

7. Verify the current CPU load using the CPU Load chart.

   The CPU Load chart shows the CPU load over the last hour. 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.

8. Click CPU Load.

   The Run Queue Length 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.

9. Return to the CPU Details view of the Host Performance subpage and review the Top 10 Processes table.

   If a process is consuming too much of the CPU utilization percentage, then this process should be investigated.

   In the following example, two database processes are consuming 87.6% of CPU utilization. Therefore, the database is the likely source of a potential CPU performance problem and should be investigated.

   
   Description of the illustration top_10_proc.gif
   
   

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

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: 

1. From the Database Home page, under Host CPU, click the Load link.

   The Host page appears, showing the Performance subpage.

2. Select Memory Details from the View list.

   The Memory Details view of the Performance subpage appears.

   This view 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.

   
   Description of the illustration mem_details.gif
   
   

3. 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 kinds of faults that may be causing systemwide delays.

4. Click Memory Scan Rate.

   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.

5. Verify the current memory utilization using the Memory Utilization chart.

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

6. 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 never exceeded 60%, so a warning was not generated.

   If you notice an unexpected spike in this value that is sustained through normal workload hours, then a memory performance problem might exist.

7. Verify current swap utilization using the Swap Utilization chart.

   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.

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

9. Return to the Memory Details view of the Host Performance subpage and 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.

10. If a memory performance problem is identified, 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.

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: 

1. From the Database Home page, under Host CPU, click the Load link.

   The Host page appears, showing the Performance subpage.

2. Select Disk Details from the View list.

   The Disk Details view appears.

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

   
   Description of the illustration disk_details.gif
   
   

3. Verify the current disk I/O utilization using the Disk I/O Utilization chart.

   The Disk I/O Utilization chart shows how many disk I/Os are being performed per second. The current value for total I/Os per second is displayed below the chart.

4. Click Total I/Os per Second.

   The Total Disk I/O 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.

5. Verify the current I/O service time using the Longest I/O Service Time chart.

   The Longest I/O Service Time 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.

6. Click Longest I/O Service Time.

   The Longest Service Time page appears.

   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.

7. On the Disk Details page, verify the disk devices in the Top Disk Devices table.

   If a particular disk is busy a high percentage of the time, then this disk should be investigated.

   In this example, the drives that host Oracle Database (xvda and xvda1) are only busy about 1.41 percent of the time, so no disk performance problem appears to exist.

   
   Description of the illustration top_disk_devices.gif
   
   

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