IT Innovation

Using Database Advisors

Get advice on key management challenges and improve performance in Oracle Database 11g.

By Sushma Jagannath

July/August 2008


Advisors are powerful tools that provide specific advice on how to address key database management challenges, covering a wide range of areas, including space, performance, and undo management. Advisors are built around two infrastructure components:

Automatic workload repository (AWR). This repository provides services for collecting, maintaining, and utilizing statistics for problem detection and self-tuning purposes. The statistical information is stored in the AWR in the form of snapshots.

Automatic database diagnostic monitor (ADDM). This monitor performs analysis, detects bottlenecks, and recommends solutions. Recommendations can include the type of advisor that needs to be used to resolve the problem.

This column focuses on some of the database advisors that are invoked by ADDM to help you improve database performance. It presents sample questions of the type you may encounter when taking the Oracle Database 11g Administration Workshop I exam, which enables you to earn the Oracle Certified Associate level of certification.

SQL Tuning Advisor

The SQL tuning advisor analyzes problems with individual SQL statements, such as a poorly performing optimizer plan or the mistaken use of certain SQL structures, and makes recommendations for improving their performance. You can run the SQL tuning advisor against resource-intensive SQL statements, a set of SQL statements over a period of time, or from a SQL workload. Typically, you run this advisor in response to an ADDM performance finding that recommends its use.

Oracle Database 11g introduces the automatic SQL tuning advisor, which can be configured to automatically run during system maintenance windows as a maintenance task. During each automatic run, the advisor selects high-load SQL queries in the system and generates recommendations on how to tune them.

John starts to create a new table based on data in the customer table. The following criteria must be applied on the data:

  • All columns of the customer table must be available in the new table.
  • The new table must have data for only those customers whose average order is US$1 million or more per quarter, who have not made payments for the last two orders, and whose payment period has exceeded the credit period.

John notices that the table-creation process is taking very long to complete. The DBA has enabled the automatic SQL tuning advisor with automatic implementation, but when he runs the SQL tuning advisor, he notes that this SQL statement was poorly formed and not automatically tuned. Why did the server not automatically tune this statement?

A. The automatic SQL tuning advisor ignores CREATE TABLE AS SELECT statements.
B. The automatic SQL tuning advisor ignores CREATE TABLE statements.
C. The automatic SQL tuning advisor tunes only SQL queries.
D. The automatic SQL tuning advisor does not tune DML statements.

The correct answer is A. Even though the automatic SQL tuning advisor is enabled, it does not resolve every SQL performance issue. It does not automatically resolve issues with the following types of SQL statements: CREATE TABLE AS SELECT and INSERT SELECT, ad hoc or rarely repeated SQL, parallel queries, and recursive SQL.

You have received complaints about the degradation of SQL query performance and have identified the most-resource-intensive SQL queries. What is your next step to get recommendations about restructuring the SQL statements to improve query performance?

A. Run the segment advisor
B. Run the SQL tuning advisor on the most-resource-intensive SQL statements
C. Run the AWR report
D. Run ADDM on the most-resource-intensive SQL statements

The correct answer is B. After you have identified the SQL statements that are the most resource intensive, you use the SQL tuning advisor to get recommendations on how to tune them. Answer A is incorrect because the segment advisor reports on the growth trend of segments and provides recommendations on whether a segment needs to be shrunk. Answer C is incorrect because AWR is a repository that stores performance-related information in the form of snapshots. Answer D is incorrect because ADDM uses these statistics to perform analysis and detect bottlenecks and then recommends solutions.

SQL Access Advisor

The SQL access advisor provides recommendations for improving the performance of a workload. In addition to analyzing indexes and materialized views as in Oracle Database 10g, the SQL access advisor in Oracle Database 11g analyzes tables and queries and provides recommendations on optimizing storage structures.

The SQL access advisor tunes a schema to a particular workload. Typically, when you use the SQL access advisor for performance tuning, you perform the following steps: create a task, define the workload, generate recommendations, and implement recommendations.

You can use the SQL access advisor to receive recommendations on which of the following:

A. Schema modifications
B. Tuning resource-intensive SQL statements
C. Improving the execution plan of SQL statements
D. SQL workload

The correct answers are A and D. The SQL access advisor analyzes an entire workload and recommends changes to indexes, materialized views, and tables to improve performance. Answers B and C are incorrect because the SQL tuning advisor makes recommendations on tuning resource-intensive SQL statements and improving the execution plan of SQL statements.

Memory Advisor

The memory advisor is a collection of several advisory functions that help determine the best settings for the total memory used by the database instance. They provide graphical analyses of total memory target settings (as shown in Figure 1), SGA and PGA target settings, or SGA component size settings. You use these analyses to tune database performance and for what-if planning. Several memory advisors are available for memory tuning (note that the availability of these advisors depends on whether the automatic memory management [AMM] and the automatic shared memory management [ASMM] features are enabled or disabled): The SGA advisor provides information about percentage improvement in DB (database) time for various sizes of SGA, the shared pool advisor provides information about the estimated parse time in the shared pool for different pool sizes, the buffer cache advisor provides information about physical reads and time for the cache size, and the PGA advisor provides information about cache hit percentage against PGA target memory size.

You have enabled AMM and ASMM features in your database, and you use Oracle Enterprise Manager to manage your database. Which memory advisors—memory size advisor, shared pool advisor, buffer cache advisor, or Java pool advisor—will you be able to use?

A. Only memory size advisor
B. Only shared pool advisor
C. All four of the memory advisors
D. Shared pool advisor, buffer cache advisor, and Java pool advisor

The correct answer is A. When AMM and ASMM are enabled, the system adapts to workload changes by automatically sizing SGA and PGA components. Because you will not receive advice on these individual components of SGA, the corresponding advisors will be disabled.

Undo Advisor

The undo advisor helps you determine the size of the undo tablespace. You can compute the minimum size of the undo tablespace, based on either the statistics gathered over a designated time period or an undo retention period. Using the runtime statistics collected in the AWR, you can use the undo advisor to extrapolate how future requirements might affect the size of the undo tablespace. You then use the Undo Management page in Oracle Enterprise Manager to make the changes recommended by the undo advisor.

You are a DBA of an online transaction processing (OLTP) system that supports thousands of users and millions of transactions every day. As part of the periodic tuning activity, you plan to use the undo advisor to ensure that the size of the undo tablespace meets the requirements of the longest-running transaction of the instance. What information will the advisor use to determine the size of the undo tablespace?

A. The analysis time period
B. The undo retention period
C. The undo generation rate
D. The number of undo tablespaces in the database

figure 1
Figure 1: Total memory target settings

The correct answers are A, B, and C. The undo advisor uses the analysis time period, the undo retention period, and the rate of undo generation to recommend the minimum size of the undo tablespace that can meet the requirements of the longest-running transaction. Answer D is incorrect because only one undo tablespace is active at any particular time, so it does not matter how many undo tablespaces a database has.


This column has focused on some advisors that help you manage and tune your database:

  • SQL tuning advisor provides recommendations on actions such as rewriting the statement, changing the instance configuration, and adding indexes.
  • SQL access advisor takes a SQL workload as an input and recommends which indexes, materialized views, and logs to create, drop, or retain for faster performance.
  • Memory advisor provides graphical analyses of total memory target settings, SGA and PGA target settings, or SGA component size settings.
  • Undo advisor determines the undo tablespace size that is required to support a given retention period.
Next Steps

 READ more about database advisors


Photography by Syda Productions, Shutterstock