Perform emergency monitoring and real-time ADDM

Overview

Both Real Time ADDM and Emergency Monitoring are accessed through the Performance menu of the database home page in OEM.

The general procedure is to use Emergency Monitoring first in order to confirm that there is a problem with the database instance and to get a general idea of what the problem is. For a more detailed analysis, use Real Time ADDM to specific advice on resource usage or hang analysis.

The database instance will invoke Real Time ADDM itself if certain thresholds are breached.

References

• Database 2 Day + Performance Tuning Guide
  • 8 Resolving Transient Performance Problems
    • Diagnosing Serious Performance Problems in Real Time
• Database PL/SQL Packages and Types Reference
  • 17 DBMS_ADDM
    • REAL_TIME_ADDM_REPORT Function

Procedure

Generate a Real Time ADDM Report Using SQL*Plus

I used the following SQL*Plus commands to generate a Real Time ADDM report using the REAL_TIME_ADDM_REPORT Function<. The report is attached as report.xml:

SET LONG 9999 SET PAGESIZE 100 SET LINESIZE 180 COLUMN report FORMAT a180 SELECT dbms_addm.real_time_addm_report() AS report FROM dual;

Simulate Performance Problem

I will simulate the performance problem by running the stress_test.sh on PADSTOW. (For further details, plese refer to "High CPU Usage with a CDB Resource Plan".)

Perform Emergency Monitoring

Access Emergency Monitoring Through OEM

Access Emergency Monitoring through the database home page under OEM as shown below:

Enter Credentials for Emergency Monitoring

Enter the required credentials on the following page:

View Emergency Monitoring

The following page appears:

Perform Real Time ADDM

Access Real Time ADDM Through OEM

Access Real Time ADDM through the database home page under OEM as shown below:

Enter Credentials for Real Time ADDM

Enter the required credentials on the following page:

Real Time ADDM Landing Page

The following page then appears:

Press the Start button to begin the analysis.

Real Time ADDM Analysis Completed

Once the Real Time ADDM analysis analysis has completed, the following screen appears:

Display Real Time ADDM Findings

Pressing the Findings button shows the following screen:

Highlighting a finding will give more details. These were not captured by me.

Display Hardware Resources

Pressing the Hardware Resources button shows the following screen:

Display Activity

Pressing the Activity button shows the following screen:

Display Hang Data

Pressing the Hang Data button shows the following screen:

Real Time ADDM Statistics

When the Statistics button is pressed, the following page appears:

This shows the database statistics. Statistics for instance and host were not captured by me.