Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,422
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

ADDM (Automatic Database Diagnostic Monitor)

Posted by FatDBA on August 24, 2012

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.

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: