AWR和ADDM联合使用

AWR报表（略），拿之前的AWR为例

运行ADDM

SQL> @/dat/DBSoftware/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql

Current Instance

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

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

  364029283 NACEC               1 NACEC

Instances in this Workload Repository schema

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

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 364029283         1 NACEC        NACEC        localhost.lo

                                                caldomain

Using  364029283 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

------------ ------------ --------- ------------------ -----

NACEC        NACEC              168 29 Mar 2014 09:05      1

                                169 29 Mar 2014 10:00      1

                                170 29 Mar 2014 11:00      1

                                171 29 Mar 2014 12:00      1

                                172 29 Mar 2014 13:00      1

                                173 29 Mar 2014 14:00      1

                                174 29 Mar 2014 15:00      1

                                175 30 Mar 2014 08:56      1

                                176 30 Mar 2014 10:00      1

                                177 30 Mar 2014 11:00      1

                                178 30 Mar 2014 12:00      1

                                179 30 Mar 2014 13:00      1

                                180 30 Mar 2014 14:00      1

                                181 30 Mar 2014 15:00      1

                                182 31 Mar 2014 09:17      1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 161

Begin Snapshot Id specified: 161

Enter value for end_snap: 162

End   Snapshot Id specified: 162

Specify the Report Name

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

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

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

Enter value for report_name: /home/oracle/addm.txt

Using the report name /home/oracle/addm.txt

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

          DETAILED ADDM REPORT FOR TASK 'TASK_164' WITH ID 164

          ----------------------------------------------------

              Analysis Period: 28-MAR-2014 from 09:00:10 to 10:00:45

         Database ID/Instance: 364029283/1

      Database/Instance Names: NACEC/NACEC

                    Host Name: localhost.localdomain

             Database Version: 10.2.0.4.0

               Snapshot Range: from 161 to 162

                Database Time: 2312 seconds

        Average Database Load: .6 active sessions

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

FINDING 1: 100% impact (2306 seconds)

-------------------------------------

SQL statements consuming significant database time were found.

   RECOMMENDATION 1: SQL Tuning, 100% benefit (2306 seconds)

      ACTION: Investigate the SQL statement with SQL_ID "025472hz6c4u4" for

         possible performance improvements.

         RELEVANT OBJECT: SQL statement with SQL_ID 025472hz6c4u4 and

         PLAN_HASH 4232819268

         update mytest000 set id=300 where name='f1111'

      RATIONALE: Waiting for event "enq: TX - row lock contention" in wait

         class "Application" accounted for 97% of the database time spent in

         processing the SQL statement with SQL_ID "025472hz6c4u4".

FINDING 2: 100% impact (2303 seconds)

-------------------------------------

SQL statements were found waiting for row lock waits.

   RECOMMENDATION 1: Application Analysis, 100% benefit (2303 seconds)

      ACTION: Significant row contention was detected in the TABLE

         "TEST.MYTEST000" with object id 55259. Trace the cause of row

         contention in the application logic using the given blocked SQL.

         RELEVANT OBJECT: database object with id 55259

      RATIONALE: The SQL statement with SQL_ID "025472hz6c4u4" was blocked on

         row locks.

         RELEVANT OBJECT: SQL statement with SQL_ID 025472hz6c4u4

         update mytest000 set id=300 where name='f1111'

   SYMPTOMS THAT LED TO THE FINDING:

      SYMPTOM: Wait class "Application" was consuming significant database

               time. (100% impact [2303 seconds])

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

          ADDITIONAL INFORMATION

          ----------------------

Wait class "Commit" was not consuming significant database time.

Wait class "Concurrency" was not consuming significant database time.

Wait class "Configuration" was not consuming significant database time.

CPU was not a bottleneck for the instance.

Wait class "Network" was not consuming significant database time.

Wait class "User I/O" was not consuming significant database time.

Session connect and disconnect calls were not consuming significant database

time.

Hard parsing of SQL statements was not consuming significant database time.

The database's maintenance windows were active during 100% of the analysis

period.

The analysis of I/O performance is based on the default assumption that the

average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you

run the report with the 'ALL' level of detail.

End of Report

Report written to /home/oracle/addm.txt

[oracle@localhost ~]$ tail -n 500 addm.txt

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2137556/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2137556/