Friday, May 08, 2015

A Simple Introduction to ADDM and AWR

ADDM was introduced in Oracle 10 and stands for Automatic Database Diagnostic Monitor. You can use it to help solve performance problems. I was looking through some old copies of Oracle Magazine, found an article about it, written by Kimberly Floss in May/June 2004, and decided to give it a try. The article provided some SQL to produce an ADDM report so I ran it against an Oracle 12 database which nobody was using:

SQL> select dbms_advisor.get_task_report
  2  (task_name, 'TEXT', 'ALL')
  3  as addm_report
  4  from dba_advisor_tasks
  5  where task_id=
  6  (select max(t.task_id)
  7   from dba_advisor_tasks t,
  8     dba_advisor_log l
  9   where t.task_id = l.task_id
 10  and t.advisor_name = 'ADDM'
 11  and l.status = 'COMPLETED')
 12  /
 
ADDM_REPORT                                                                     
--------------------------------------------------------------------------------
          ADDM Report for Task 'ADDM:1254537384_1_154'                         
          --------------------------------------------                         
                                                                                
Analysis Period                                                                
---------------                                                                 
AWR snapshot range from 153 to 154.                                            
Time period starts at 02-APR-15 18.00.56                                       
Time period ends at 02-APR-15 19.00.11                                          
                                                                                
Analysis Target                                                                
---------------                                                                 
Database 'ORCL1' with DB ID 1254537384.                                        
Database version 12.1.0.1.0.                                                   
ADDM performed an analysis of instance orcl1, numbered 1 and hosted at         
WIN-HUVS4H99T56.                                                               
                                                                                
Activity During the Analysis Period                                            
-----------------------------------                                            
Total database time was 0 seconds.                                             
The average number of active sessions was 0.                                   
                                                                                
                                                                                
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
                                                                                
There are no findings to report.                                               
                                                                                
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
                                                                                
          Additional Information                                               
          ----------------------                                               
                                                                                
Miscellaneous Information                                                      
-------------------------                                                      
There was no significant database activity to run the ADDM.                    
                                                                                
The database's maintenance windows were active during 99% of the analysis      
period.                                                                        
                                                                                
                                                                                
 
SQL> 

This said that there was nothing to report, as you might expect. The following lines refer to an AWR snapshot:

AWR snapshot range from 153 to 154.
Time period starts at 02-APR-15 18.00.56 
Time period ends at 02-APR-15 19.00.11 

AWR stands for Automatic Workload Repository, which was also introduced in Oracle 10. A snapshot of this is taken every hour by default and ADDM uses these snapshots to produce its reports.
 
In the example above, the ADDM report had no recommendations but, in the normal course of events, you will be running ADDM on databases with problems. In this case, you might see output like this, which came from a Windows server trying to host 6 good-sized Oracle 11 databases with only 12 gigabytes of RAM:                                         
 
          Findings and Recommendations
          ----------------------------
 
Finding 1: Virtual Memory Paging
Impact is .22 active sessions, 100% of total activity.
------------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
 
   Recommendation 1: Host Configuration
   Estimated benefit is .22 active sessions, 100% of total activity.
   -----------------------------------------------------------------
   Action
      Host operating system was experiencing significant paging but no
      particular root cause could be detected. Investigate processes that do
      not belong to this instance running on the host that are consuming
      significant amount of virtual memory. Also consider adding more physical
      memory to the host. 

The next example came from a third-party application which was doing lots of COMMIT’s (sometimes up to 1000 each second): 

Finding 3: Commits and Rollbacks
Impact is .47 active sessions, 34.34% of total activity.
--------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is .47 active sessions, 34.34% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate application logic for possible reduction in the number of
      COMMIT operations by increasing the size of transactions.
   Rationale
      The application was performing 9966 transactions per minute with an
      average redo size of 1240 bytes per transaction.
 
   Recommendation 2: Host Configuration
   Estimated benefit is .47 active sessions, 34.34% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 3 K and the
      average time per write was 10 milliseconds.
   Rationale
      The total I/O throughput on redo log files was 217 K per second for
      reads and 446 K per second for writes.
   Rationale
      The redo log I/O throughput was divided as follows: 0% by RMAN and
      recovery, 67% by Log Writer, 0% by Archiver, 0% by Streams AQ and 32% by
      all other activity.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is .47 active sessions, 34.34% of total activity. 

You might take quite a while to work out problems like these but, by using ADDM, you can diagnose them quickly. I will look at further examples in future posts.

No comments: