Using advisors in Oracle 10g

Learning objective

After completing this topic, you should be able to identify the functions of various tuning and diagnostic advisors and use the segment advisor to detect storage problems in a given scenario.

1. Tuning and diagnostic advisors

Oracle Database 10g provides several tuning and diagnostic advisors.

• ADDM
• SQL Tuning Advisor
  
• SQL Access Advisor
• Memory Advisor
• MTTR Advisor
• Segment Advisor
• Undo Management Advisor

ADDM

The Automatic Database Diagnostic Monitor (ADDM) is a server-based expert that reviews database performance every 60 minutes. ADDM detects possible system bottlenecks early and recommends fixes before the user notices a degradation in system performance.

SQL Tuning Advisor

The SQL Tuning Advisor recommends ways of improving the performance of individual SQL statements. You cannot invoke the SQL Tuning Advisor directly. You call it using monitoring links such as Top SQL or Top Sessions. This helps optimize high-impact SQL statements.

SQL Access Advisor

The SQL Access Advisor analyzes all SQL statements issued within a set period and recommends adding additional indexes or materialized views to improve performance.

Memory Advisor

The Memory Advisor is a collection of several advisory functions that determine the best settings for the shared pool, database buffer cache, and Program Global Area (PGA). This advisor also provides a central point of control for the large pool and the Java pool.

MTTR Advisor

The Mean-Time-To-Recover (MTTR) Advisor enables you to specify the recovery time for a database after an instance crash.

Segment Advisor

This Segment Advisor searches for tables and indexes that are consuming space inefficiently, and recommends ways of distributing the free space. It can generate scripts to reduce space consumption in some cases.

Undo Management Advisor

The Undo Management Advisor enables you to determine what undo tablespace size is required to support a given retention period.

Question

Match the tuning and diagnostic advisors to their functions.

Options:

0. ADDM
0. MTTR
0. SQL Access Advisor
0. SQL Tuning Advisor

Targets:

0. Specifies the recovery time for a database
0. Reviews database performance every 60 minutes
0. Recommends ways of improving the performance of individual SQL statements.
0. Analyzes all SQL statements issued within a set period

Answer

The ADDM is a server-based expert that reviews database performance every 60 minutes. The MTTR advisor enables you to specify the recovery time for a database. The SQL Access Advisor analyzes all SQL statements issued within a set period. The SQL Tuning Advisor recommends ways of improving the performance of individual SQL statements.

The Automatic Database Diagnostic Monitor (ADDM) detects possible system bottlenecks early and recommends fixes before the user notices a degradation in system performance.

The Mean-Time-To-Recover (MTTR) Advisor determines the optimal value for the maximum time needed to recover the database.

The SQL Access Advisor recommends adding additional indexes or materialized views to improve performance.

SQL Tuning Advisor recommendations might include rewriting the statement, changing the instance configuration, or adding indexes.

2. SQL tuning and access advisors

The purpose of SQL analysis is to identify high-impact SQL statements and recommend improvements to performance.

You can improve the performance of SQL statements by

• adding or changing indexing
• providing staged aggregate data as materialized views
• rewriting the SQL to be more efficient
• correcting setup flaws such as improper initialization parameters

It is time-consuming to analyze and tune SQL statements manually. Instead, you can use the SQL tuning and access features available in Enterprise Manager.

Using the advisors, it's easier for you to find high-impact SQL statements and recognize common errors in SQL statement construction.

The advisors also suggest places where you should add indexes or materialized views, and calculate the cost of adding those objects.

Question

How do the SQL tuning and access advisors help improve SQL statement performance?

Options:

0. They automatically rewrite inefficient SQL statements before execution
   
0. They enable you to find high-impact SQL statements
0. They enable you to identify common errors in SQL statements
0. They suggest places where you should add indexes or materialized views

Answer

The SQL tuning and access advisors help improve SQL statement performance by enabling you to find high-impact SQL statements and identify common errors in SQL statements. They also suggest places where you should add indexes or materialized views.

Option 1 is incorrect. The advisors provide recommendations on how the SQL statements can be modified, but do not rewrite the statements.

Option 2 is correct. It's less time-consuming to use the advisor to find high-impact SQL statements than to do so manually.

Option 3 is correct. It's easier to use the advisor to identify common errors in SQL statements than to do it yourself.

Option 4 is correct. The advisors also help by calculating the cost of adding those objects.

3. Memory advisors

If you specify a size for the System Global Area (SGA) that is either too small or too large, you will severely degrade performance.


The memory advisors make it easy to find SGA components and set them to the most efficient sizes.

Each memory advisor provides you with a graph that contrasts the benefit of increased memory with the amount of memory required to achieve that benefit.

You should try to size the different cache areas close to the "knee" of the curve. This is the point at which performance gains either level out or provide minimal performance improvement for the cost of the memory allocation.

When configuring memory, you must balance the needs of the SGA and the PGA with the total memory requirements of the system.

For example, if you increase the amount of memory allocated to the instance, you risk the server using disk to simulate real memory, also known as swapping.

Question

How do the memory advisors help determine the most efficient settings for SGA components?

Options:

0. They enable you to balance SGA and PGA needs with total memory requirements
   
0. They enable you to set SGA components to the most efficient size
0. They provide you with a graph to help you decide how much memory to use
0. They automatically adjust memory size parameter values when performance is hampered

Answer

The memory advisors enable you to balance SGA and PGA needs with total memory requirements. By providing you with a graph to help you decide how much memory to use, they help you to set SGA components to the most efficient size.

Option 1 is correct. Otherwise if you increase the amount of memory allocated to the instance, you risk the server using disk to simulate real memory, also known as swapping.

Option 2 is correct. You can also specify how much memory is allocated when the database starts up.

Option 3 is correct. The graph contrasts the benefit of increased memory with the amount of memory required to achieve that benefit.

Option 4 is incorrect. The advisors model the benefit of increased memory contrasted with the amount of memory required to achieve that benefit. But you are still required to decide what size to set for the memory parameters.

4. The segment advisor

The segment advisor detects and corrects storage inefficiencies.

This diagnostic advisor looks for tables or indexes with excess free space, and suggests ways to return the unneeded space to the database.

The segment advisor is useful, because tables often have more space than they need.

When you delete a row from a table, Oracle allocates the free space to the table.

Oracle also returns free space to a table when you update data within a row to a value that consumes less space than the original.

The segment advisor has two modes.

• Complete Analysis of All Segments (Comprehensive)
• Analysis Based on Available Statistics (Limited)

Complete Analysis of All Segments (Comprehensive)

In comprehensive mode, the segment advisor scans the tables and indexes to provide the best recommendations.

You can also narrow the analysis scope to individual tables and indexes, or expand it to cover an entire tablespace.

Analysis Based on Available Statistics (Limited)

In limited mode, the segment advisor makes suggestions using the existing optimizer statistics.

Question

Which of these are characteristics of the segment advisor?

Options:

0. It provides the best recommendations in limited mode.
   
0. It scans tables and indexes in comprehensive mode.
0. It suggests ways to return free space to the database.

Answer

The segment advisor suggests ways to return free table space to the database. In comprehensive mode, it scans tables and indexes for free space.

Option 1 is incorrect. The segment advisor provides the best recommendations in comprehensive mode. In limited mode, it makes suggestions using the existing optimizer statistics.

Option 2 is correct. You can narrow the analysis scope to individual tables and indexes, or expand it to cover an entire tablespace.

Option 3 is correct. You use the segment advisor to detect and correct storage inefficiencies.

5. Detecting and correcting storage inefficiencies

Suppose you want to use the segment advisor to detect and correct a storage inefficiency in the EMPLOYEES table of the Human Resources (HR) schema.

In Enterprise Manager, you navigate to the Related Links section.

Here you want to open the page that activates the diagnostic advisors.

You click Advisor Central.

In the Advisor Central page, you want to choose the advisor that detects and corrects storage inefficiencies.

You click Segment Advisor.

In this section of the Segment Advisor page, you want to narrow the segment advisor's focus to individual segment objects.

You also want to set the advisor mode to limited, which bases the advisor's analysis on existing optimizer statistics.

You select the Schema Object option and then you select the Analysis Based on Available Statistics (Limited) option.

Having specified the type of analysis you want, you click Continue.

This brings you to the Segment Advisor: Schema Objects page, where you need to select a schema object for the segment advisor to analyze.

The first step is to click Add.

In the Schema Objects: Add page, you want to look for the schema object in the HR schema.

You type HR in the Schema search field, and then click Search.

The Results section lists the tables and indexes of the HR schema.

You select the HR.EMPLOYEES table for analysis, and click OK.

You return to the Segment Advisor: Schema Objects page, which confirms your choice of HR.EMPLOYEES as the schema object that the segment advisor will analyze.

You click Next to move to the next step of the process.

The Segment Advisor: Options page is the second step. Here you can set the time limit for analysis.

You accept the default option - Unlimited - by clicking Next.

The Segment Advisor: Schedule page is the third step. Here you can give the task a name and description, and also schedule a time for the analysis to take place.

You want to enter a more descriptive name than the one automatically suggested by Oracle. In the Task Information section, you enter SHRINK_OBJECT_GROWTH in the Task Name field.

You don't want to run the job in multiple windows. So, in the Schedule region, you select Standard from the Schedule Type drop-down list. You click Next to continue.

The Segment Advisor: Review page is the fourth and final step.

Here you run the Segment Advisor by clicking Submit.

A message appears confirming that you have created the segment advisor task.

You scroll to the Results section to see if the SHRINK_OBJECT_GROWTH task is completed.

If not, you return to the top of the page and click Refresh until it does.

You now want to see the results for the task you created.

You click SHRINK_OBJECT_GROWTH.

The Segment Advisor Task: SHRINK_OBJECT_GROWTH page contains the segment advisors's recommendation on shrinking segments to reclaim space.

To shrink the segments and release unused space back to the database, you first click Schedule Implementation.

In the Schedule Implementation page, you accept the default setting to run the job immediately by clicking Submit.

In the Scheduler Jobs page, a message confirms that the task has been completed.

To view the status of your task, you click the Run History tab.

In the Run History tabbed page, the status of your task, SQLSCRIPT_3598250, is SUCCEEDED.

This means you have deleted the unused space from the segment.

Segment Advisor recommendations usually require that you change table properties to enable row movement.

You can do this by using Enterprise Manager.

To begin, you navigate to the Schema region of the Administration tabbed page, and click Tables.

In the Tables page, you run a search for the HR schema, and select the EMPLOYEES table from the results.

You now want to modify the table.

You click Edit.

This brings you to the Edit Table: HR.EMPLOYEES page.

Here you click the Options tab.

You want to enable row movement in the EMPLOYEES table.

You select Yes from the Enable Row Movement drop-down list.

You click Apply to apply the change to the table.

You have now modified the table properties to enable row movement.

A message appears, confirming the change to the HR.EMPLOYEES table.

Question

You want to use the segment advisor to detect storage inefficiencies in a table. In Enterprise Manager, you navigate to the Related Links region of the Administration tabbed page.

This question requires you to access the Segment Advisor, then narrow the segment advisor's focus to individual segment objects, and set the advisor mode to limited. Finally, you must navigate to the page where you select the schema object. Which option enables you to do this task?

Options:

0. In the Related Links region, you select Advisor Central. In the Advisor Central page, you click Segment Advisor. In the Segment Advisor page, you select the Analysis Based on Available Statistics (Limited) option. You then click Continue.
0. In the Related Links region, you select Advisor Central. In the Advisor Central page, you click Segment Advisor. In the Segment Advisor page, you select the Schema Objects option and then select the Analysis Based on Available Statistics (Limited) option. You then click Continue.

Answer

In the Related Links region, you select Advisor Central. In the Advisor Central page, you click Segment Advisor. In the Segment Advisor page, you select the Schema Object option and then select the Analysis Based on Available Statistics (Limited) option. You then click Continue.

Summary

Oracle Database 10 g contains several tuning and diagnostic advisors that you can access using Enterprise Manager.

The SQL tuning and access advisors enable you to identify high-impact SQL statements and recommend improvements to performance.

The memory advisors make it easy to find SGA components and optimize their sizes.

The segment advisor detects and corrects storage inefficiencies, and returns space that is no longer required to the rest of the database.

Segment advisor recommendations usually require that you change table properties to enable row movement.