RSS Feed for This PostCurrent Article

Oracle AWR and ADDM for Performance Tuning

Oracle AWR (Advanced Workload Repository) and ADDM (Automated Database Diagnostic Monitor) which are available since Oracle 10g are useful features for performance tuning and troubleshooting.

For developer like me who uses Oracle but is not a Oracle dba both AWR and ADDM are useful when I need to troubleshoot for performance related issues related to my application. SQL Tuning Advisor is useful also after I identify the SQL statements which cause the bottleneck.

AWR Report
You can either use OEMDDC, which is the Enterprise Database Control to view the AWR snapshots, or can generate the AWR reports in text or HTML format.

By default for every database, Oracle automatically generates snapshots of the performance data once every hour and stores the statistics in the workload repository.

You can manually capture a snapshot if you want.

% sqlplus / as sysdba
SQL> exec dbms_workload_repository.create_snapshot;

You can run awrrpti.sql script to generate the report.

%sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

The report contains useful information like

  • Wait Events Statistics
  • SQL Statistics
  • Instance Activity Statistics
  • IO Stats
  • Buffer Pool Statistics
  • Advisory Statistics
  • Wait Statistics
  • Undo Statistics
  • Latch Statistics
  • Segment Statistics
  • Dictionary Cache Statistics
  • Library Cache Statistics
  • Memory Statistics
  • Streams Statistics
  • Resource Limit Statistics
  • init.ora Parameters

You can also create baseline snapshots and do your performance tuning based on the baseline.

ADDM analyses a number of different database-related problems

  • Memory-related issues such as shared pool latch contention, log buffer issues, or database buffer cache related problems
  • CPU bottlenecks
  • Disk I/O performance issues
  • Database configuration problems
  • Space-related issues, such as tablespaces running out of space
  • Application and SQL tuning issues such as excessive parsing and excessive locking

You can use Oracle Enterprise Manager (OEM) to view the ADDM findings.

Trackback URL

RSS Feed for This Post2 Comment(s)

  1. sbubzu | Mar 9, 2009 | Reply

    Are ADDM and AWR free or extra cost product ?

  2. Dave | Mar 9, 2009 | Reply

    They are extra cost.

Sorry, comments for this entry are closed at this time.