Advisors are a collection of services provided by Oracle that help in the maximum utilization of available resources. They are the server side components that provide feedback on the resource utilization and performance of a database. Within a database, the advisors work on particular subsystems to analyze, diagnose, and give recommendations on the performance of the database. Advisors provide a consistent, uniform interface for enabling all the advisors in a database to interact with each other. Some of the Advisors are as follows:
The Automated Database Diagnostic Monitor The Automated Database Diagnostic Monitor (ADDM) constitutes the most important of all the advisors. ADDM can be called the core of the advisors. ADDM proactively analyzes the system, identifies performance issues, and detects the bottlenecks within the database. It therefore, identifies the issues and their potential causes and suggests the solutions or recommendations for solving the issue. It proactively automates the tuning of the Oracle 10g instance. The ADDM collects a lot of statistics frequently, analyzes the statistics, finds out the cause, produces report on the findings, and writes Structured Query Language (SQL) to resolve the issue.
Segment Advisor: The Segment Advisor defragments the space within the segments, notifies of fragmented space, and reclaims the wasted space using the online shrink operation. In a shrink operation space can be recovered from specific objects based on the recommendation of the Segment Advisor. In the earlier versions, repairing fragmentation meant dropping and recreating the objects. The option of high water mark can also be reset or left as it is. High water mark is a feature in Oracle that is used to identify the highest amount of space used by a particular segment. The Segment Advisor also reports on the growth trends of segments to enable better capacity planning and resource monitoring.
The Undo Advisor The Undo Advisor assists in determinig the appropriate Undo tablespace. It also helps in determining the optimal UNDO_RETENTION settings. This reduces the ORA-01555 (Snapshot too old) errors. The Undo Advisor also helps in setting the low threshold value of the Undo retention period for using it with the flashback query feature. The flashback query feature means that the users can query data at a particular time by mentioning the time or the system change number (SCN). This enables the users to correct the changes without the intervention of DBAs. Therefore, it assists in identifying the Undo tablespace related issues and suggests corrective measures.
The SQL Tuning Advisor The work of SQL Access Advisor is to reduce the workload and recommend proper access structures to improve the performance of the workload. In fact, the tuning of database has always been a challenge to database administration. However much tuned a database might be it, a poorly written SQL statement can have a negative effect on the functioning of the database if the query optimizer selects a poor execution path. But with the coming of Automatic SQL tuning, there has been marked improvement.
The SQL Access Advisor To reduce the amount of input/output (I/O), CPU, and wait time for a given SQL statement the SQL Access Advisor provides support to find potential schema modifications. The Advisor can do the following actions:
- It determines whether the addition of a materialized view would help SQL statements.
- It checks materialized views.
- It checks indexes.
- It assesses whether the addition of an index would benefit SQL statements.
The Memory Advisor: The Memory Advisor performs the overall supervision of the instance. It helps in tuning the size of the memory structures of the database. The Memory Advisor can be used to gather more information including the System Global Area (SGA) and other memory structures. The Memory Advisor can also be accessed from the Memory Advisor main window. The sizes of all the four components in the SGA, i.e., the shared pool, buffer cache, large pool, and java pool are present in the main window in both tabular as well as graphical form. The Advice button in the main window can be clicked to tell the Memory Advisor to formulate the tuning recommendations for a particular memory structure. There is a second tab for the Program Global Area (PGA). Clicking the tab shows the result of the advisor's analysis of the PGA.
Note: The Memory Advisor and its associated advisors are available only when the Automatic Shared Memory Management (ASMM) is not used.
The Mean Time to Recover Advisor: The main purpose of Mean Time to Recover (MTTR) is to do instance recovery in case of instance failure so that database can be brought online quickly. An instance failure can occur due to the following reasons:
- If the ABORT option has been used to shut down the instance.
- An instance failure can occur if the host server crashes.
- An instance failure can occur when a crucial background process fails.
In such cases the instances recovery automatically occurs on first startup since the instance failure. Oracle uses the online redo logs and undo segments to roll back any changes that had not been committed during the time of instance failure. The MTTR Advisor monitors the database during its normal functioning. This MTTR makes recommendations about instance parameters and database.
The Undo Advisor: The Undo Advisor monitors and proactively responds to the issues occurring in Undo segments. The before image of data is stored in the Database Buffer Cache in the SGA, copies of which are also stored in undo segment. This is stored in the Undo tablespace. There are the following three purposes of storing data in an undo segment:
- If the user issues a rollback command, then the Undo Advisor provides original and unchanged state of the data.
- In case of an instance failure, Undo Advisor is used during instance recovery by undoing the uncommitted transactions that were in progress at the time of instance failure.
The Undo segments can prove to be one of the most difficult aspects of database administration. Even when there seem to be no undo segment issues then also the combinations of transactions can cause an undo segment error. This may lead to segment-tuning issues.
The goals of undo segments tuning are as follows:
- To ensure that the database users always get a read-consistent view that they need to complete their transactions.
- To ensure that the undo segments do not result in unrequired I/O.
Note: In previous releases of Oracle the undo segments were known as rollback segments since they were used to rollback a transaction.
Invoking Advisors: The Advisors can be used in various conditions. The Advisors can be revoked by the following four methods:
- Through Oracle Enterprise Manager (EM) Database Control. It is a graphical user interface (GUI).
- Through the Manageability Monitor (MMON) process. The advisors are executed in limited capacity when they are invoked through the MMON process. The MMON triggers an advisor when it detects the need of advisors or in response to its findings when monitoring the system.
- By a Server Alert recommendation.
- By using the DBMS_ADVISOR package.
