OCP 12C – Emergency Monitoring, Real-Time ADDM

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Emergency Monitoring

  • Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung.
  • Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening.
  • You can access real-time performance data from ASH and access the hang analysis data.
  • Unlike in 11g, you don’t have to explicitly activate the memory access mode, emergency monitoring connects directly to the SGA to retrieve the necessary data.

Real-Time ADDM

  • Real-Time ADDM unlike Emergency Monitoring is used to analyze the root cause of the hung.
  • Real-Time ADDM works like the normal ADDM but access only the last 10 minutes of ASH data to provide recommendations.
  • Because Real-Time ADDM only uses data from memory you can still access it when the database is hung.
  • You can use Real-Time ADDM triggers to proactively monitor the performance of an instance.
    • High load
    • I/O bound
    • CPU bound
    • Over-allocated memory
    • Interconnect bound
    • Session limit
    • Process limit
    • Hung session
    • Deadlock detected
  • Reports can be viewed in DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS.
  • To manually generate a report you can use the following procedure

  •  You can also generate an ADDM report using the EM Database Express by going to the performance hub et click on “Current ADDM findings“.

Create an Compare Period ADDM report

  • Before Oracle 12c there was the possibility to generate an AWR compare period report which was great to compare performance of a database between two periods. This method was also limited in finding the root cause of the performance degradation, this is why Oracle 12c introduced the Compare Period ADDM.
  • The Compare Period ADDM helps you to :
    • Analyse the potential causes of performance degradation between two periods
    • It gives a measure of the performance deviation.
    • Maps the performance diviation to the system changes.

SQL Commonality

  • When you compare two different periods, Oracle measure their commonality to determine their level of compatibility, to do that it uses factors like :
    • SQL Statements
    • Applications
    • Workload
  • Oracle considers that Compare Period ADDM findings are reliable if the commonality of the two periods is 80% or more.

Using DBMS_ADDM to compare periods

  • You can manually generate reports using the DBMS_ADDM package with the following functions :
    • DBMS_ADDM.COMPARE_INSTANCES : Produces a Compare Period ADDM report for an instance-level performance comparison.
    • DBMS_ADDM.COMPARE_DATABASES : Produces a Compare Period ADDM report for a database-wide performance comparison.
    • DBMS_ADDM.COMPARE_REPLAY_CAPTURE_REPORTS : Produces a Compare Period ADDM report comparing the performance of a capture to a replay.
    • DBMS_ADDM.COMPARE_REPLAY_REPLAY_REPORT : Produces a Compare Period ADDM report comparing the performance of a replay to another replay.

ASH Enhancements

  • The ASH data visualization has changed in Oracle 12c, In EM you can now access a new pages called “ASH Analytics“. It is now possible to Drilldown into logical dimensions. In addition you can now send these Active Reports to someone which can view them offline.


3 thoughts on “OCP 12C – Emergency Monitoring, Real-Time ADDM

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.