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