49 Oracle Database Advisor overview

From Oracle 10g, Oracle has introduce many advisors to assist DBA maintenance work.

From the DBA_ADVISOR_DEFINITIONS table, we can get some of the advisors.

SELECT * FROM dba_advisor_definitions;

We can categorize the advisors as below:

SQL Tuning:

• SQL Tuning Advisor ( STA): DBMS_SQLTUNE
• SQL Access Advisor ( SAA)
• SQL Performance Analyzer ( SPA):  DBMS_SQLPA
• Tune MView : http://www.dba-oracle.com/tips_rittman_dbazine_mtune.htm

Space Advisor:

• Undo Advisor
• Segment Advisor
• Compression advisor

Memory Advisor:

• Shared Pool Advisor: V$SHARED_POOL_ADVICE
• Buffer Cache Advisor: V$DB_CACHE_ADVICE.
• PGA Advisor: V$PGA_TARGET_ADVICE
• Total SGA Size Advisor: When enable ASMM. V$SGA_TARGET_ADVICE
• Total Memory size Advisor: When enable the AMM.
V$MEMORY_TARGET_ADVICE

High Availability advisor

• Data Recovery Advisor
• Mean Time to recovery advisor : FAST_START_MTTR_TARGET
• Maximum Availability architecture advisor (MAA)

Related table

• DBA_ADVISOR_TASKS: Basic information for tasks
• DBA_ADVISOR_LOG:  Status for tasks.
• DBA_ADVISOR_FINDINGS:Findings identified by tasks
• DBA_ADVISOR_RECOMMENDATIONS:Recommendations for tasks

OEM Cloud Control

Advisor Central

Memory advisor