Emergency Monitoring feature of EM Cloud allows for DBA to connect to non-responding database by way of proprietary resources for diagnosis of problem.  The EM agent connects directly to SGA, bypassing the SQL retrieval layer.   The page displays ASH data and Hang Analysis table, displaying blocking sessions and allows for killing this blocks.  Emergency Monitoring page is accessible from Performance Menu of EM Cloud.  EM Express has no diagnostic connection.

 

Real-Time ADDM – introduced in EM Cloud 12c, much of functionality is available in EM Express.  ADDM is to help with analysis of problem which cause unresponsive behavior without restarting DB.  ADDM checks for problems based on set of predefined criteria, then suggests solutions.

Two Ways ADDM connects:

1. Normal Connection – normal JDBC connection, used for extensive performance analysis.
2. Diagnostic Connection – latchless connection for hung situations, where JDBC connection is not available.

 

Differences between REAL-Time ADDM and ADDM

1. Real-Time ADDM performs similar analysis to ADDM by does not use AWR snapshots, rather it uses ASH data from SGA.
2. Real-Time ADDM using in-memory real time performance data.
3. Real-Time ADDM analysis is automatic when problem is detected.
4. RT ADDM scans every 3 seconds using MMON process.
5. RT ADDM does not use lock or latch.
6. Things that would trigger RT ADDM analysis:
   1. High load – active session > 3x number of CPU
   2. I/O bound – I/O of active sessions concentrated on a single block read.
   3. CPU bound – Active sessions > 10% of all sessions and CPU > 50% utilization.
   4. Over-allocated memory – memory allocated > 95% of physical memory.
   5. Interconnect bound – Single block continually used.
   6. Session limit – near 100%
   7. Process limit – near 100%
   8. Hung session – hung session > 10% of all sessions.
   9. Deadlock detected – Any deadlock.
7. MMON slave process will store ADDM reports created by AWR, which is accessible by DBA_HIST_REPORTS view.
8. RT ADDM controls level analysis resource usage by:
   1. Duration between reports – RT ADDM will not produce new report if it has been less than 5 min since last report.
   2. Oracle RAC control – Only one RAC instance can create ADDM report at any given time.
   3. Repeated Triggers – A unique issue can only trigger an analysis if the impact is 100% or greater previous impact within 45 min.
   4. Newly identified issues – If new issue is identified, an analysis will be performed regardless of active session load.

 

 

Larry Catt

OCP

In Oracle 12c, one needs to generate ADDM reports for comparison of Normal database operations and Periods performance is thought to be sub-par.   This article covers the generation of ADDM for normal operation.   There are three methods to generate ADDM reports:  EM Cloud control, ADDMRPT.SQL script, and DBMS_ADVISOR package.

 

EM Cloud Control:

1. Logon to EM.
2. Click the Advisor Central Link.
3. Select ADDM link.
4. From here you can start/stop snapshots; create ADDM tasks and display results.
5. You can view ADDM analysis from the Performance Analysis section of the Home page.

 

ADDMRPT.SQL

1. This script is located under ORACLE_HOME/rdbms/admin directory and can be executed at any time.
2. Logon to SQLPLUS with sysdba privileges.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 29 09:04:21 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

 

3. Execute the script and select snapshots to generate reports.

 

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

 

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

 1457738340 ORCL                1 orcl

 

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

———— ——– ———— ———— ————

* 1457738340        1 ORCL         orcl         linux2.local

                                                domain

Using 1457738340 for database Id

Using          1 for instance number

 

 

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

 

Listing the last 3 days of Completed Snapshots

 

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

———— ———— ——— —————— —–

orcl         ORCL               388 27 Jun 2016 00:00      1

                                389 27 Jun 2016 01:00      1

                                390 27 Jun 2016 02:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap:

 

 

4. Enter a starting snapshot id:

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 389

Begin Snapshot Id specified: 389

 

Enter value for end_snap:

 

 

5. Enter a Ending snapshot id:

 

Enter value for end_snap: 390

End   Snapshot Id specified: 390

 

 

6. Enter a report name or use default with enter.

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_389_390.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

7. The report will be generated and placed in current directory.

 

End of Report

Report written to addmrpt_1_389_390.txt

SQL>

 

 

DBMS_ADVISOR package

 

1. Logon to SQLPLUS with sysdba privileges.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 29 09:04:21 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

 

2. Create a ADDM task.

 

Begin

  DBMS_ADVISOR.create_task (

    advisor_name      => ‘ADDM’,

    task_name         => ‘389_390_AWR_SNAPSHOT’,

    task_desc         => ‘Advisor for snapshots 389 to 390.’);

End;

/

 

3. Set start and end parameters for task.

 

Begin

  — Set the start and end snapshots.

  DBMS_ADVISOR.set_task_parameter (

    task_name => ‘389_390_AWR_SNAPSHOT’,

    parameter => ‘START_SNAPSHOT’,

    value     => 389);

 

  DBMS_ADVISOR.set_task_parameter (

    task_name => ‘389_390_AWR_SNAPSHOT’,

    parameter => ‘END_SNAPSHOT’,

    value     => 390);

End;

/

 

4. Execute task

 

Begin

  — Execute the task.

  DBMS_ADVISOR.execute_task(task_name => ‘389_390_AWR_SNAPSHOT’);

End;

/

 

5. View report.

 

— Display the report.

SET LONG 1000000 LONGCHUNKSIZE 1000000

SET LINESIZE 1000 PAGESIZE 0

SET TRIM ON TRIMSPOOL ON

SET ECHO OFF FEEDBACK OFF

 

SELECT DBMS_ADVISOR.get_task_report(‘389_390_AWR_SNAPSHOT’) AS report

FROM   dual;

 

6. This completes generation for ADDM reports.

 

 

 

 

 

Larry Catt

OCP