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