About Oracle Advisors

The Oracle Advisory Framework

Oracle's Advisory Framework encompasses a set of performance tuning advisors that allow for an enhanced dimension of the cause of the problem, the problem involved, and the solution, with the most appropriate steps to be pursued at each stage. The framework is widely integrated with the Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR) reporting capabilities. I will carefully discuss at a later time how to approach the most typical scenarios in a production environment

Among the most important components of the advisory framework, a DBA can look at the following, namely:

1. Memory Advisor

Provides advice for SGA memory structures and PGA memory areas, very practical when using Automatic Memory Management. The SGA memory structures involved include the shared pool, the database buffer cache, the large pool, the Java pool, the streams pool.

2. SQL Tuning Advisor

This advisory component provides SQL Profiling, SQL Access Paths, statistics analysis, and structure analysis. This advisor can use the DBMS_SQLTUNE package to create, execute, and report a given task.

3. SQL Access Advisor

This advisor provides an analysis of the overall system performance through workload specifications, focusing on each segment structure, in particular indexes and materialized views in very critical scenarios. The specified workload could involve one of the following:

• A simple SQL statement
• A SQL statement tuning set
• Current contents of the SQL Cache
• Any hypothetical workload obtained through DDL of certain object sets.

4. Oracle Segment Advisor
   This advisory framework component allows to plan on the capacity of a specific segment as needed, and forecast its future growth.
   
   
5. Undo Advisor
   This Advisor is useful in the control of undo management and the necessary custom requirements to run specific processes.
   
   
6. MTTR Advisor
   This advisory component establishes the appropriate settings to establish the appropriate windows of time for an optimal mean time to recovery, to minimize downtime, in general.

These advisors can be used either through the Oracle Enterprise Manager Control interface or through the DBMS_ADVISOR package, as convenient in each scenario. Advisors have more value when they can also be utilized with the resource manager and specific plan directives that can effectively produce the desirable custom outcomes.