Real Time ADDM (Automatic Database Diagnostic Monitoring) in Oracle 12c

Automatic Database Diagnostic Monitoring (ADDM) is one of the ways to use if you are facing performance problems with your database to gather information. Moreover, in previous releases the ADDM is generated based on 2 AWR (Automatic Workload Repository) Report snapshots which is taken on hourly basis and retained for 8 days by default. In oracle 12c, a new enhanced feature is introduced which is Real Time-ADDM. The real time ADDM is based on information fetched from memory triggered by manageability monitor process (MMON) especially when the database is in “hanging” state.

Database statistics gathering requires that STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Also, check CONTROL_MANAGEMENT_PACK_ACCESS parameter.

ADDM DB Parameters

Let us assume a scenario that your database is in “hanging” state:

sqlplus ‘/ as sysdba’  (if you can’t access then use: sqlplus -prelim “/as sysdba”)
SQL>@$ORACLE_HOME/rdbms/admin/rtaddmrpt.sql

SQL>exit

Now you have successfully generated a Real-Time ADDM, check it and see what is causing the problem (the file will be in HTML format).

Another important report you can generated is “Performance Hub Active Report” which is a comprehensive HTML report that will show different tab information such as:

Activity

Workload

ADDM findings

Resources (CPU and Memory)

I/O

This can be generated using the following query:

SQL>@$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

perf hub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s