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.

ASH ANALYTICS

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.