Tales From A Lazy Fat DBA

ADDM (Automatic Database Diagnostic Monitor) can be describe as the database’s doctor. It allows an Oracle database to diagnose itself and determine how potential problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.
It Explains the problem and recommendations to fix the flaw in Database.
Example:
FINDING 1: 59% impact (944 seconds)
———————————–
The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)
      ACTION: Increase SGA target size by increasing the value of parameter
         “sga_target” by 28 M.

   SYMPTOMS THAT LED TO THE FINDING:
      Wait class “User I/O” was consuming significant database time. (83%
      impact [1336 seconds])

[oracle@localhost admin]$ ls -ltr *add*
-rw-r—– 1 oracle oinstall 13103 Oct 22  2002 owaddemo.sql
-rw-r—– 1 oracle oinstall  3168 Oct 15  2003 addmrpt.sql
-rw-r—– 1 oracle oinstall  4748 Jan  5  2005 addmrpti.sql
-rw-r—– 1 oracle oinstall  1249 Jun 28  2005 prvthadd.plb
-rw-r—– 1 oracle oinstall 55186 Jun 28  2005 prvtbadd.plb
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Aug 24 01:49:54 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> @addmrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
 1301453781 ORCL                1 orcl
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————————
* 1301453781        1 ORCL         orcl         localhost.localdomain
                                               

Using 1301453781 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               226 22 Aug 2012 03:42      1
                                           236 23 Aug 2012 05:30      1
                                           237 23 Aug 2012 06:30      1
                                          238 23 Aug 2012 07:30      1

                                          239 24 Aug 2012 01:45      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Begin Snapshot Id specified:

Enter value for end_snap:

Alternate Ways to Generate Report:

===============================

DBA_ADVISOR_FINDINGS – To get the findings in the Database.
DBA_ADVISOR_RECOMMENDATIONS – To get the Recommendations on the findings.

SQL> SELECT * FROM DBA_ADVISOR_dEFINITIONS;

ADVISOR_ID ADVISOR_NAME                     PROPERTY
———- —————————— ———-
         1 ADDM                                    1
         2 SQL Access Advisor                     15
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                      7
         5 Segment Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31

7 rows selected.