Wednesday, February 26, 2014

AWR and ADDM Reports

AWR and ADDM - best explanation
The AUTOMATIC WORKLOAD repository is a source of information for several other Oracle 10g features including:-Automatic Database Diagnostic Monitor-SQL Tuning Advisor-Undo Advisor-Segment Advisor
ADDM analyze snapshots taken by AWR.
Automatic database diagnostic monitoring is enabled by default and is controlled by the CONTROL_MANAGEMENT_PACK_ACCESS and the STATISTICS_LEVEL initialization parameters.
The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter should be set to DIAGNOSTIC+TUNING
The STATISTICS_LEVEL initialization parameter should be set to the TYPICAL (default) or ALL to enable automatic database diagnostic monitoring.
Setting the DBIO_EXPECTED Parameter:::ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTEDThe value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds
Set the value one time for all subsequent ADDM executions::SQL>EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);
Managing AWR Snapshots::::By default, the Automatic Workload Repository (AWR) generates snapshots of performance data once every hour,and retains the statistics in the workload repository for 8 days.You can change the default values for both the snapshot interval and the retention period.The data in the snapshot interval is analyzed by ADDM. ADDM compares the difference between snapshots.
Modifying Snapshot Settings::::By default, AWR generates snapshots of performance data once every hour. Alternatively,you can modify the default values of both the interval between snapshots and their retention period.
EXAMPLE:--- This causes the repository to refresh every 15 minutes-- and retain all data for 2 weeks i.e 20160 min.sql>Exec dbms_workload_repository.modify_snapshot_settings(retention=>20160, interval=> 15);now,seesql> select DBID,SNAP_INTERVAL,RETENTION from dba_hist_wr_control;sql>@E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awwrpti.sql
ADDM report has following sections:
     Problem: Finding that describe the root cause of database performance issue.
     Symptom: Finding that contain information that often lead to one or more problem finding.
     Information: Findings that are used to report non-problem area of the system.
     Recommendations: Composed of action & rationales.
SQL> Execute DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED',8000);SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sqlsql>desc DBA_ADVISOR_FINDINGS:This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.
DBA_ADVISOR_RECOMMENDATIONS:This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

No comments: