Performance Tuning Advisors

Performance Tuning Advisors

Automatic Database Diagnostic Monitor (ADDM)

Using the AWR infrastructure, ADDM is able to holistically analyze the system, identify the major problem in the system (not the symptoms),  and recommend corrective action (which some times may require use of a separate Oracle10g advisor). To focus the attention on the actual problems, ADDM also shows the areas of the system that have no problems.

ADDM is invoked automatically every time that a new AWR snapshot is generated (by default every 60 minutes) and the results of the analysis are stored in the advisory framework tagged with the snapshot id.

Manually - $ORACLE_HOME/rdbms/admin/addmrpt.sql


SQL Tuning Advisor :

For the SQL's in issue from ADDM report, we can run SQL Advisor

SQL Access Advisor : We have this advisor as well.

Note: AWR report is more informative than Statspack

Adv of AWR over STATSPACK :

The repository is created and maintained automatically
The report can be generated in text or html mode
The report displays additional information regarding wait classes, metrics, OS stats, etc.

To generate an AWR Report :

$ORACLE_HOME/rdbms/admin/awrrpt.sql

or

EXECUTE dbms_workload_repository.create_snapshot();

Note :

Pre req for all the above

STATISTICS_LEVEL is set to TYPICAL (recommended) or ALL ( Consumes lot of CPU ).

Ref : ID 276103.1

UR's Key Run ;-)