Advisors are powerful tools for database management. They provide specific advice on how to address key 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. Some advisors are run automatically.
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 SGA Advisor graphically displays the impact on performance of changing the size 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.
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 also invoke some of the advisors from the Performance Hub page, or through recommendations from ADDM.
Table 10-2 describes the performance advisors.
Table 10-2 Performance Advisors
| Advisor | Description |
|---|---|
|
Automatic Database Diagnostics 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 Tuning 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." For more information about the maintenance windows (time periods) for your database, see Oracle Database Reference. |
|
Memory Advisors
|
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.
See "Optimizing Memory Usage with the Memory Advisors" for more information about memory advisors, and see "Managing Memory" for more information about memory management modes. |
|
Undo Advisor |
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." |