Tuesday, September 4, 2007

Using performance diagnostic tools in Oracle 10g

Using performance diagnostic tools in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to manage the AWR and use the ADDM to review database performance in Oracle Database 10 g.

1. Using the AWR

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning.

The AWR automatically captures the data from the System Global Area (SGA) in the form of snapshots every sixty minutes. The repository stores the information on disk for a default period of seven days.

You can modify the snapshot and retention intervals of the AWR.

By increasing the intervals, you can improve advisor recommendations, and therefore the scope of the statistical analysis.

In doing so, however, you should consider the cost of the space required to store the snapshots and the impact on performance of collecting the snapshot information.

The AWR has hundreds of tables that belong to the SYSMAN schema, and which are stored in the SYSAUX tablespace.

To work with the AWR, you use Enterprise Manager or the DBMS_WORKLOAD_REPOSITORY package. You cannot access the AWR directly with SQL.

Question

Which of these are features of the AWR?

Options:

  1. The AWR is accessed directly by SQL
  2. The AWR captures data from the SGA in snapshots every sixty minutes
  3. The AWR contains hundreds of tables that belong to the SYSMAN schema
  4. The AWR retains captured information on disk for seven days by default

Answer

The AWR captures data from the SGA in snapshots every sixty minutes, retains the information on disk for seven days, and contains hundreds of tables that belong to the SYSMAN schema.

Option 1 is incorrect. You cannot access the AWR directly with SQL. To work with the AWR, you use Enterprise Manager or the DBMS_WORKLOAD_REPOSITORY package

Option 2 is correct. The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning.

Option 3 is correct. The AWR stores the tables in the SYSAUX tablespace.

Option 4 is correct. You can modify the retention level if required.

You can set the AWR's snapshot collection level to one of the following:

  • BASIC
  • TYPICAL
  • ALL
BASIC
The BASIC level disables most ADDM functionality. It disables the collection of many of the important statistics required by other Oracle Database features, so it is not recommended.
TYPICAL
The TYPICAL level is the setting Oracle recommends. TYPICAL ensures the collection of all major statistics required for database self-management functionality, and provides best overall performance.
ALL
The ALL level adds extra SQL tuning data to the snapshots. ALL is very intensive in terms of performance and should only be enabled for short periods for specific reasons.

The settings for the AWR correspond to the STATISTICS_LEVEL initialization parameter.

STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }

When tuning a new application, you should consider setting the collection level to ALL.

The ALL setting enhances the recommendations of the SQL advisors by collecting SQL execution plans and timing statistics.

When tuning is complete, you should return the collection level to the TYPICAL setting.

Question

Which of these settings can you configure for the AWR?

Options:

  1. ALL
  2. BASIC
  3. TYPICAL
  4. EXTENDED

Answer

The AWR settings are ALL, BASIC, and TYPICAL.

Option 1 is correct. The ALL setting adds extra SQL tuning data to the snapshots

Option 2 is correct. The BASIC setting disables most ADDM functionality.

Option 3 is correct. TYPICAL is the setting Oracle recommends.

Option 4 is incorrect. The settings for the AWR correspond to the STATISTICS_LEVEL initialization parameter, for which EXTENDED is not a legitimate value. The syntax is

STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }

Suppose you want to change the AWR setting to collect SQL execution plans and timing statistics. This means configuring the collection level to ALL.

To begin, you open Enterprise Manager and navigate to the Administration tabbed page.

In the Administration tabbed page, you scroll to the Workload region. You now want to access the AWR settings.

You click the Automatic Workload Repository link.

In the Automatic Workload Repository page, you click Edit.

This brings you to the Edit Settings page. AWR settings include retention period, collection interval, and collection level.

Modifying any of these settings can impact the functionality of components that depend on the AWR, including the advisors.

You're now ready to change the collection level configuration.

You click TYPICAL.

In the Initialization Parameters page, the parameters are automatically filtered by STATISTICS_LEVEL to display the relevant parameter value.

In the Value text box, you delete the default value, TYPICAL.

And you type the new value, ALL. Then you click Apply.

You have successfully changed the AWR collection level setting to specify the inclusion of SQL execution plans and timing statistics.

Question

Suppose you want to change the current AWR setting using Enterprise Manager. To begin, you navigate to the Workload region of the Administration tabbed page.

This question requires you to configure the AWR to the setting Oracle recommends. Which option performs this task?

Options:

  1. Under the Workload heading, you click the Automatic Workload Repository link. In the Automatic Workload Repository page, you click Edit. In the Edit Settings page, you click ALL. In the Initialization Parameters page, you type TYPICAL in the Value text field, and click Apply.
  2. Under the Workload heading, you click the Automatic Workload Repository link. In the Automatic Workload Repository page, you click Edit. In the Edit Settings page, you click ALL. In the Initialization Parameters page, you type BASIC in the Value text field, and click Apply.

Answer

Under the Workload heading, you click the Automatic Workload Repository link. In the Automatic Workload Repository page, you click Edit. In the Edit Settings page, you click ALL. In the Initialization Parameters page, you type TYPICAL in the Value text field, and click Apply.

2. Using the ADDM

The Automatic Database Diagnostic Monitor (ADDM) analyzes the data in the snapshot interval.

Unlike other advisors, the ADDM runs automatically every time the AWR captures a snapshot.

It monitors the instance and detects bottlenecks, and then stores the results within the AWR.

The ADDM analyzes the period corresponding to the last two snapshots. For example, statistics captured include SQL statements that produce the highest load on the system.

The ADDM detects most bottlenecks before they trouble users, and recommends solutions for many detected problems.

The ADDM also quantifies the benefits of the recommendations.

Common problems that the ADDM detects include

  • bad Oracle Net connection management
  • CPU bottlenecks
  • I/O capacity
  • high checkpoint load and cause
  • high load SQL statements
  • high PL/SQL and Java time
  • lock contention
  • undersizing of Oracle memory structures

Question

Which of these are features of the ADDM?

Options:

  1. It analyzes the data in the snapshot interval
  2. It recommends solutions to detected problems
  3. It detects most bottlenecks before they trouble users
  4. It runs automatically on specified AWR snapshots only

Answer

The ADDM analyzes the data in the snapshot interval, recommends solutions to detected problems, and detects most bottlenecks before they trouble users.

Option 1 is correct. Common problems that the ADDM detects include CPU bottlenecks, I/O capacity, and poor Oracle Net connection management.

Option 2 is correct. The ADDM also quantifies the benefits of the recommendations.

Option 3 is correct. The ADDM is a proactive monitoring system.

Option 4 is incorrect. Unlike other advisors, the ADDM runs automatically every time the AWR captures a snapshot.

The ADDM stores the results of its analysis in the Automatic Workload Repository.

You can access the results through Enterprise Manager.

Suppose a bottleneck developed overnight in your Oracle database, and users are now experiencing slow response times.

You want to access the ADDM Findings page to view the analysis of the snapshots that the AWR took.

Beginning in the Enterprise Manager Home tabbed page, you want to navigate to the Related Links region of the Maintenance page and then access the page that enables you to view the ADDM results.

You select the Maintenance tab and then select Advisor Central.

This brings you to the Advisor Central page, which provides access to various experts.

You click ADDM to view the ADDM results.

In the Create ADDM Task page, you want to view the ADDM analysis for the time period between midnight and the current time.

To do this, you must create an ADDM task. First you need to choose the snapshot range by selecting the start and end time.

To set the start time, you ensure that the Period Start Time option is selected.

Then you click the snapshot icon that represents 12 PM. The Period Start time appears as 00:00:39.

You accept the default Period End Time, which is the time of the most recent snapshot - in this case, 11:00:03.

Then you click OK to create the task.

A message appears to say you have successfully created the snapshot task.

You scroll down to the table of findings generated by the task.

This lists the database problems that occurred during the time period you specified.

You now want to view more information on the findings.

You click the SQL statements consuming significant database time were found link.

On the ADDM Performance Finding Details page, you receive more detailed information on the problem and recommendations for solving the corresponding issue.

The ADDM recommendations table categorizes recommendations by SCHEMA, SQL Tuning, DB configuration, and others.

In this case, the ADDM recommendations relate to SQL tuning.

The Benefit column provides you with the maximum reduction in database elapse time if you implement the recommendation.

Question

Suppose your database is experiencing slow response times. You want to access detailed information on ADDM findings relating to SQL. To begin, you navigate to the Maintenance page.

This question requires you to create an ADDM task to analyze the AWR snapshot range from 12 p.m. to the default period end time, and view any finding that relates to SQL statements.

Options:

  1. In the Related Headings region of the Maintenance page, you select Advisor Central. In the Advisor Central page, you click ADDM. In the Create ADDM Task page, you select the Period End Time option. You click the 12 PM snapshot, and then click OK. Finally, you click the SQL statements consuming significant database time were found link.
  2. In the Related Links region of the Maintenance page, you select Advisor Central. In the Advisor Central page, you click ADDM. In the Create ADDM Task page, you click the 12 PM snapshot icon, and then click OK. Finally, you click the link that says SQL statements consuming significant database time were found.

Answer

In the Related Links region of the Maintenance page, you select Advisor Central. In the Advisor Central page, you click ADDM. In the Create ADDM Task page, you click the 12 PM snapshot icon, and then click OK. Finally, you click the link that says SQL statements consuming significant database time were found.

Summary

The Automatic Workload Repository (AWR) is a built-in repository of performance information. It automatically captures snapshots of database metrics every sixy minutes and retains them for a default of seven days.

The Automatic Database Diagnostic Monitor (ADDM) runs after each AWR snapshot, monitors the instance and detects bottlenecks, and then stores results within the AWR.

No comments: