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

SQL> select dbms_addm.real_time_addm_report() from dual;

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