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 was supposed to be attached as report.xml, but was lost during web-site conversion:

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, please refer to "High CPU Usage with a CDB Resource Plan".)