Advisors on Oracle 10g

[adrianomp]

Actually I'm reviewing some material for 1z0-040 exam. I faced some doubts about advisors, hope you can enlighten me.

I'm trying to figure out the API/CLIs to access all the advisors:

SQL> select advisor_name from dba_advisor_definitions;

ADVISOR_NAME
------------------------------
ADDM
SQL Access Advisor
Undo Advisor
SQL Tuning Advisor
Segment Advisor
SQL Workload Manager
Tune MView

Access them through EM Database Control is not a problem at all. From what I understood:

1. ADDM advisor can be accessed creating a task with DBMS_ADVISOR package, setting ADVISOR_NAME as "ADDM"
2. Undo Advisor? Is there a point on it, since one can simply put RETENTION GUARANTEE? The only way I figured out was to check DBA_ALERT_HISTORY for any issues with the undo tablespace
3. SQL Tuning Advisor can be accessed creating a task with DBMS_SQLTUNE. Not necessary to inform the advisor name
4. SQL Workload Manager Advisor is accessed through DBMS_ADVISOR package, %SQLWKLD% procedures.
5. Segment Advisor means just using DBMS_SPACE to check for shrink-candidates or checking DBA_ALERT_HISTORY for tablespace thresholds on free space? Didn't find any references to that advisor, seems that Oracle and AWR can deal with these issues alone.
6. Tune MView Advisor according to "Oracle 10g New Features" by Robert Freeman is pointed to be done with DBMS_SQLTUNE.TUNE_MVIEW. However, the section name is "SQL Access Advisor", which I thought it was referring to suggestions about creating indexes and so on (which is included in DBMS_SQLTUNE). A little misleading.

I'm still trying to understand the mess about all these advisors spread through DBMS_SQLTUNE, DBMS_ADVISOR or no-DBMS. Can someone clarify it, since the sources I checked seem to diverge on some points? Just to know, they are:

Oracle 10g New Features - Robert Freeman - Oracle Press
http://www.oracle-base.com/articles/10g/Articles10g.php
http://www.oracle.com/technology/pub...dba/index.html
http://www.iselfschooling.com/iss4ora10g.htm
http://www.oraclecoach.com/oracle10g.htm
Oracle Documentation and Metalink as further reference

[adrianomp]

Ok, got it after further reading on Tim Hall's notes. Thank you anyway.