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:
- Logon to EM.
- Click the Advisor Central Link.
- Select ADDM link.
- From here you can start/stop snapshots; create ADDM tasks and display results.
- You can view ADDM analysis from the Performance Analysis section of the Home page.
ADDMRPT.SQL
- This script is located under ORACLE_HOME/rdbms/admin directory and can be executed at any time.
- 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>
- 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:
- 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:
- Enter a Ending snapshot id:
Enter value for end_snap: 390
End Snapshot Id specified: 390
- 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:
- 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
- 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>
- 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;
/
- 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;
/
- Execute task
Begin
— Execute the task.
DBMS_ADVISOR.execute_task(task_name => ‘389_390_AWR_SNAPSHOT’);
End;
/
- 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;
- This completes generation for ADDM reports.
Larry Catt
OCP