PERFORMANCE AND TUNING

The Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor ( ADDM ) is an advisor which detects problem area’ s in the database and and which gives recommendations. ADDM uses the statistical data from the AWR - Automatic Workload Repository - stored in the sysaux tablespace. By default the statistical data is gathered / written to disk by snapshots every hour and kept in the sysaux tablespace for 7 days, assuming you haven’ t set the instance parameter statistics_level to basic. ( default = typical ). Please note you can also take AWR Reports which are an equivalent of the statspack Reports from previous releases. (AWR Reports gives even more detailed information )

We can however create a snapshot manually

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

As well we can change the retention period and the snapshot interval. In the example below we change the retention period to 5 days ( 5 x 24 x 60 ) and the snapshot interval to 30 minutes.

SQL> exec dbms_workload_repository.modify_snapshot_settings (retention => 7200,interval => 30);

PL/SQL procedure successfully completed.

Here is an example of how one can take a ADDM report.

SQL> @/app/oracle10g/10.2.0/rdbms/admin/addmrpt.sql;