ADDM Reports.

Different Type of Advisors are:-

SQL> SELECT * FROM DBA_ADVISOR_dEFINITIONS;

ADVISOR_ID ADVISOR_NAME PROPERTY
---------- ------------------------------ ----------
1 ADDM 1
2 SQL Access Advisor 15
3 Undo Advisor 1
4 SQL Tuning Advisor 7
5 Segment Advisor 3
6 SQL Workload Manager 0
7 Tune MView 31


ADDM ( Automatic Database Diagnostic Monitor)

The AWR has all the information on the activities and waits on the oracle 10g database. The ADDM is primary client of AWR information. ADDM can be considered as expert residing in oracle 10g.

The ADDM provides suggestions and recommendations for fixing the problems.

The ADDM is automatically invoked by the oracle 10g database and performs analysis to determine any issues in the database. The ADDM recommends the solution if it identifies issues.

The ADDM analysis is performed every time an AWR snapshot is taken. The MMON process triggers the ADDM analysis each time a snapshot is taken to do analysis of the period corresponding to last two snapshots. This approach proactively monitors the database and detects bottlenecks before they become significant problem . It is also possible to invoke ADDM manually along with the areas that have problem identified. ADDM also reports areas of the system that have no problem.s This allows you to quickly see that there is little to be gained by performing actions in hose areas.

The results of ADDM are shared in the AWR and are accessible through dictionary views and EM.

Analysis is performed from the top down, identifying symptoms first and then refining them to reach the root cause.

he goal of the analysis to to reduce a single throughput metric called DBtime. The Dbtime is the cumulative time spent by the database server in processing user requests, which includes wait time and CPU time. You can view this metric from the time model dictionary views. By reducing the dbtime, the database is able to support more user requests using the same resources, in other words the database can perform the same workload in less time.

Since the ADDM is integrated to the database server running the analysis has a minor impart on database at normally takes less than 3 seconds to complete the analysis. The addm analysis results are represented as findings, and each find belong to are of the three categories, problem (root cause), symptom, information.

DBA_ADVISOR_FINDINGS - To get the findings in the Database.
DBA_ADVISOR_RECOMMENDATIONS - To get the Recommendations on the findings.

Generating ADDM Reports:-

1) @?/rdbms/admin/addmrpt.sql
2) @?/rdbms/admin/addmrpti.sql (For particular instance).

Generating ADDM report through dbms_advisor Package:-

begin
dbms_advisor.create_task
(
ADVISOR_NAME => 'ADDM',
TASK_NAME => 'ADDM_REPORT',
TASK_DESC => 'GETTING ADDM ADVICE'
);
dbms_advisor.set_task_parameter
(
TASK_NAME => 'ADDM_REPORT',
PARAMETER => 'START_SNAPSHOT',
VALUE => 43
);
dbms_advisor.set_task_parameter
(
TASK_NAME => 'ADDM_REPORT',
PARAMETER => 'END_SNAPSHOT',
value => 44
);
dbms_advisor.execute_Task
(
TASK_NAME => 'ADDM_REPORT'
);
end;
/

SQL> set long 10000;
SQL> set pagesize 10000;
SQL> select dbms_advisor.get_Task_report('ADDM_REPORT') from dual;

DBMS_ADVISOR.GET_TASK_REPORT('ADDM_REPORT')
--------------------------------------------------------------------------------
DETAILED ADDM REPORT FOR TASK 'ADDM_REPORT' WITH ID 246
-------------------------------------------------------

Analysis Period: 01-DEC-2010 from 10:32:21 to 11:00:22
Database ID/Instance: 2728202513/1
Database/Instance Names: TOMIG/tomig
Host Name: rhel.manzoor.com
Database Version: 10.2.0.1.0
Snapshot Range: from 43 to 44
Database Time: 1 seconds
Average Database Load: 0 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

THERE WAS NOT ENOUGH DATABASE TIME FOR ADDM ANALYSIS.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADDITIONAL INFORMATION
----------------------

There was no significant database activity to run the ADDM.

The database's maintenance windows were active during 99% of the analysis
period.

The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.


Views:-

dba_advisor_Tasks;
dba_advisor_findings;
dba_advisor_recommendations;
dba_advisor_actions;
dba_advisor_log