10g performance guide 6th chapter

10g Performance Guide: 6th Chapter

Chapter 6: Automatic Performance Diagnostics

[Usual disclaimer: You should read the Oracle Performance and Tuning

Guide yourself at OTN -- these are just my notes of what I found

interesting or new while reading.]

Introduction to Database Diagnostic Monitoring

* Overall purpose is to aid DBAs in correctly diagnosing issues, as

opposed to just making changes based on initial symptoms. (I like

this approach. It fits well with Tom Kyte's mantra of testing and

understanding, rather than guessing.)

* ADDM analyzes data from AWR every time a snapshot's taken and

saves results to database. Provides hourly (by default) reports,

problem diagnoses, and root cause analyses.

Automatic Database Diagnostic Monitor (ADDM)

Goal is to reduce 'db time' -- a stat found in the V$SESS_TIME_MODEL

and V$SYS_TIME_MODEL views.

* Example problems ADDM considers: CPU bottlenecks, undersized

SGA/PGA, I/O capacity, RAC configuration, and more.

* Also documents non-problem areas like less-impactful wait events.

ADDM Results (Findings)

* Findings are categorized into three classes: problem, symptom, or

information.

* Findings are quantified by estimates of impact to 'db time'.

* When a problem has multiple causes, ADDM reports multiple

problem/symptom findings, all of which may share the same db time.

This means the impact of findings can add up to over 100% of the

time.

Setting it up

* It's enabled by default. Control it with STATISTICS_LEVEL

parameter.

* Note that DBIO_EXPECTED impacts analysis of I/O performance.

Running ADDM

* At command line: Use $ORACLE_HOME/rdbms/admin/addmrpt.sql. Give it

beginning and ending snapshot IDs, as well as output report name.

* Via APIs: We can use DBMS_ADVISOR package's APIs to create or

execute any advisor tasks (like an ADDM task).

Useful ADDM Views

* DBA_ADVISOR_TASKS: info about existing tasks.

* DBA_ADVISOR_LOG: status (including error messages and execution

times) for existing tasks.

* DBA_ADVISOR_RECOMMENDATIONS: results of completed diagnostic

tasks, with recommendations for problems identified. Note the

"rank" column which gives the magnatude of the problem for the

recommendation.

* DBA_ADVISOR_FINDINGS: all findings and symptoms founds, along with