About Advisors

Advisors are powerful tools for database management. They provide specific advice on how to address key database management challenges, covering a wide range of areas including space, performance, and undo management. In general, advisors produce more comprehensive recommendations than alerts. This is because alert generation is intended to be low cost and have minimal impact on performance, whereas advisors consume more resources and perform more detailed analysis. This, along with the what-if capability of some advisors, provides vital information for tuning that cannot be procured from any other source. You run advisors from Oracle Enterprise Manager Database Control (Database Control). Some are also run automatically during maintenance windows (time periods).

Advisors are provided to help you improve database performance. These advisors include Automatic Database Diagnostic Monitor (ADDM), SQL advisors, and memory advisors. For example, the Shared Pool Advisor, graphically displays the impact on performance of changing the size of this component of the System Global Area (SGA).

You can run a performance advisor when faced with the following situations:

You want to resolve a problem in a specific area, for example, to determine why a given SQL statement is consuming 50 percent of CPU time and what to do to reduce its resource consumption.You can use the SQL Tuning Advisor.
During application development, you want to tune a new schema and its associated SQL workload for optimal performance. You can use the SQL Access Advisor.
You are planning to add memory to your system. You can use the Memory Advisor to determine the database performance impact of increasing your SGA or PGA (Program Global Area).

You can run all advisors from the Advisor Central home page, accessible through a link on the Database Home page. You can also invoke many of the advisors from the Performance page, through recommendations from ADDM, or from alerts.

Table: Performance Advisors describes the performance advisors. Other advisors are listed in Table: Other Advisors.

Performance Advisors

Advisor	Description
Automatic Database Diagnostic Monitor (ADDM)	ADDM makes it possible for Oracle Database to diagnose its own performance and determine how any identified problems can be resolved.See "Performance Self-Diagnostics: Automatic Database Diagnostic Monitor" and "Diagnosing Performance Problems Using ADDM".
SQL Advisors SQL Tuning Advisor SQL Access Advisor	The SQL Tuning Advisor analyzes one or more SQL statements and makes recommendations for improving performance. This advisor is run automatically during the maintenance periods, but can also be run manually. See "About the Automatic SQL Tuning Advisor" and "Running the SQL Tuning Advisor". The SQL Access Advisor tunes a schema to a given SQL workload. For example, the SQL Access Advisor can provide recommendations for creating indexes, materialized views, or partitioned tables for a given workload. See "Running the SQL Access Advisor".
Memory Advisors Memory Advisor SGA Advisor Shared Pool Advisor Buffer Cache Advisor PGA Advisor	The Memory Advisors provide graphical analyses of total memory target settings, SGA and PGA target settings, or SGA component size settings. You use these analyses to tune database performance and for what-if planning. Depending on the current memory management mode, different memory advisors are available. If Automatic Memory Management is enabled, then only the Memory Advisor is available. This advisor provides advice for the total memory target for the instance. If Automatic Shared Memory Management is enabled, then the SGA Advisor and PGA Advisor are available. If Manual Shared Memory Management is enabled, then the Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor are available. See "Optimizing Memory Usage with the Memory Advisors". See "Managing Memory" for more information about memory management modes.

Advisor

Description

Automatic Database Diagnostic Monitor (ADDM)

ADDM makes it possible for Oracle Database to diagnose its own performance and determine how any identified problems can be resolved.See "Performance Self-Diagnostics: Automatic Database Diagnostic Monitor" and "Diagnosing Performance Problems Using ADDM".

SQL Advisors

SQL Tuning Advisor
SQL Access Advisor

The SQL Tuning Advisor analyzes one or more SQL statements and makes recommendations for improving performance. This advisor is run automatically during the maintenance periods, but can also be run manually. See "About the Automatic SQL Tuning Advisor" and "Running the SQL Tuning Advisor".

The SQL Access Advisor tunes a schema to a given SQL workload. For example, the SQL Access Advisor can provide recommendations for creating indexes, materialized views, or partitioned tables for a given workload. See "Running the SQL Access Advisor".

Memory Advisors

Memory Advisor
SGA Advisor
Shared Pool Advisor
Buffer Cache Advisor
PGA Advisor

The Memory Advisors provide graphical analyses of total memory target settings, SGA and PGA target settings, or SGA component size settings. You use these analyses to tune database performance and for what-if planning. Depending on the current memory management mode, different memory advisors are available.

If Automatic Memory Management is enabled, then only the Memory Advisor is available. This advisor provides advice for the total memory target for the instance.
If Automatic Shared Memory Management is enabled, then the SGA Advisor and PGA Advisor are available.
If Manual Shared Memory Management is enabled, then the Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor are available.

See "Optimizing Memory Usage with the Memory Advisors". See "Managing Memory" for more information about memory management modes.

Other Advisors

Advisor	Description
Segm ent Advisor	The Segment Advisor provides advice on whether a segment is a good candidate for a shrink operation based on the level of space fragmentation within that segment. The advisor also reports on the historical growth trend of segments. You can use this information for capacity planning and for arriving at an informed decision about which segments to shrink. See "Reclaiming Unused Space".
Undo Adviso r	The Undo Advisor assists in correctly sizing the undo tablespace. The Undo Advisor can also be used to set the low threshold value of the undo retention period for any Oracle Flashback requirements. See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".

About Advisors

Related Topics