| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-02 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-02 |
|
|
View PDF |
This chapter describes how to use the automatic diagnostic feature of the Automatic Database Diagnostic Monitor (ADDM) to monitor database performance. ADDM automatically detects and reports on performance problems with the database. The results are displayed as ADDM findings on the Database Home page in Enterprise Manager. Reviewing the ADDM findings enables you to quickly identify the performance problems that require your attention. Each ADDM finding also provides a list of recommendations for reducing the impact of the performance problem. Reviewing ADDM findings and implementing the recommendations are tasks that you should perform daily as part of the regular database maintenance. Even when the database is operating at an optimal performance level, you should continue to use the ADDM to monitor database performance on an ongoing basis.
This chapter contains the following sections:
Reviewing the Automatic Database Diagnostics Monitor Analysis
Interpreting the Automatic Database Diagnostics Monitor Findings
The Automatic Database Diagnostic Monitor (ADDM) is a self-diagnostic engine built into Oracle Database. ADDM examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine possible performance problems in Oracle Database. ADDM then locates the root causes of the performance problems, provides recommendations for correcting them, and quantifies the expected benefits. ADDM also identifies areas of the database for informational purposes where no action is necessary.
An ADDM analysis is performed after each AWR snapshot (every hour by default), and the results are saved in the database, which can then be viewed using Oracle Enterprise Manager. Before using another performance tuning method presented in this guide, you should first review the results of the ADDM analysis.
The ADDM analysis is performed from the top down, first identifying symptoms and then refining the analysis to reach the root causes of performance problems. ADDM uses the DB time statistic to identify performance problems. DB time is the cumulative time spent by the database in processing user requests, including both wait time and CPU time of all user sessions that are not idle. The goal of tuning the performance of a database is to reduce the DB time of the system for a given workload. By reducing DB time, the database is able to support more user requests using the same resources. System resources that are using a significant portion of DB time are reported as problem areas by ADDM, and they are sorted in descending order by the amount of related DB time spent. For more information about the DB time statistic, see "Time Model Statistics".
In addition to diagnosing performance problems, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions from which you can choose. ADDM recommendations include:
Hardware changes
Adding CPUs or changing the I/O subsystem configuration
Database configuration
Changing initialization parameter settings
Schema changes
Hash partitioning a table or index, or using automatic segment-space management (ASSM)
Application changes
Using the cache option for sequences or using bind variables
Using other advisors
Running the SQL Tuning Advisor on high-load SQL statements or running the Segment Advisor on hot objects
ADDM benefits apply beyond production systems; even on development and test systems, ADDM can provide an early warning of potential performance problems.
It is important to realize that performance tuning is an iterative process, and fixing one problem can cause a bottleneck to shift to another part of the system. Even with the benefit of the ADDM analysis, it can take multiple tuning cycles to reach a desirable level of performance.
This section describes how to configure ADDM and contains the following topics:
ADDM is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable the automatic database diagnostic feature of ADDM. The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database features, including ADDM, and is not recommended.
See Also:
Oracle Database Reference for information about the STATISTICS_LEVEL initialization parameter
The ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of the DBIO_EXPECTED argument is the average time it takes to read a single database block, in microseconds. Oracle Database uses the default value of 10 milliseconds, which is an appropriate value for most hard drives. If your hardware is significantly different, consider using a different value.
To determine the correct setting for the DBIO_EXPECTED parameter:
Measure the average read time of a single database block for your hardware.
This measurement needs to be taken for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds.
Set the value one time for all subsequent ADDM executions.
For example, if the measured value if 8000 microseconds, run the following command as SYS user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
'ADDM', 'DBIO_EXPECTED', 8000);
By default, the Automatic Workload Repository (AWR) generates snapshots of performance data once every hour, and retains the statistics in the workload repository for 7 days. It is possible to change the default values for both the snapshot interval and the retention period. The data in the snapshot interval is then analyzed by ADDM. AWR compares the difference between snapshots to determine which SQL statements to capture, based on the effect on the system load. This reduces the number of SQL statements that need to be captured over time.
This section contains the following topics:
You can manually create snapshots, but this is usually not necessary because the AWR generates snapshots of the performance data once every hour by default. In some cases, however, it may be necessary to manually create snapshots to capture different durations of activity, such as when you want to compare performance data over a shorter period of time than the snapshot interval.
On the Database Performance page, under Additional Monitoring Links, click Snapshots.
The Snapshots page appears with a list of the most recent snapshots.
Click Create.
The Confirmation page appears.
Click Yes.
The Processing: Create Snapshot page is displayed while the snapshot is being taken.
Once the snapshot is taken, the Snapshots page reappears with a Confirmation message.
In this example, the ID of the snapshot that was created is 2284.
By default, the AWR generates snapshots of the performance data once every hour. Alternatively, you can modify the default values of both the interval between snapshots and their retention period.
To modify the snapshot settings:
On the Database Administration page, under Statistics Management, click Automatic Workload Repository.
The Automatic Workload Repository page appears.
Click Edit.
The Edit Settings page appears.
To change the retention period, in the Retention Period (Days) field, enter the number of days to retain the snapshots.
You can also choose to retain snapshots indefinitely by selecting Retain Forever. It is recommended that you increase the snapshot retention period whenever possible based on the available disk space. In this example, the retention period is changed to 30 days.
To change the interval between snapshots, select the desired interval from the Interval list.
You can also choose to disable snapshot collection by selecting Turn off Snapshot Collection. In this example, the snapshot collection interval is changed to 30 minutes.
To change the level of statistics that are captured in snapshots, click the Collection Level link.
The Initialization Parameter page appears. To change the statistics level, select the desired value in the Value list for the statistics_level parameter and click Save to File. In this example, the default value of Typical is used.
After the snapshot settings are modified, click OK to apply the changes.
The Automatic Workload Repository page appears and the new settings are displayed.
By default, ADDM runs every hour to analyze snapshots taken by the AWR during that period. If performance problems are found, the results of the analysis are displayed under Diagnostic Summary on the Database Home page, as shown in Figure 3-1.
The link next to ADDM Findings shows how many ADDM findings were found in the most recent ADDM analysis.
On the Database Home page, under Diagnostic Summary, click the link next to ADDM Findings.
The Automatic Database Diagnostic Monitor (ADDM) page appears. The results of the ADDM run are displayed, as shown in Figure 3-2.
Figure 3-2 Automatic Database Diagnostic Monitor (ADDM) Page
On the Automatic Database Diagnostic Monitor (ADDM) page, the Database Activity graph shows the database activity during the ADDM analysis period. Database activity types are defined in the legend based on its corresponding color in the graph. In the example shown in Figure 3-2, the largest block of activity appears in green and corresponds to CPU, as described in the legend. This suggests that the host CPU may be a performance bottleneck during the ADDM analysis period. To select a different analysis period, click the left arrow icon to move to the previous analysis period, or the right arrow icon to move to the next analysis period. You can also click the Zoom icons to shorten or lengthen the analysis period displayed on the graph.
The ADDM findings for the analysis period are listed under Performance Analysis and contains the following columns:
Impact (%)
Displays an estimate of the portion of database time that is used by the performance problem that was found.
Finding
Displays a summary of the ADDM finding. To view details about a finding, click the link in this column.
Recommendations
Displays the type of operation ADDM recommends to resolve the performance problem identified by the finding.
The Informational Findings section lists the areas that do not have a performance impact and are for informational purpose only.
The results of the ADDM finding can also be viewed in a report that can be saved for later access. To view the ADDM report, click View Report.
The ADDM analysis results are represented as a set of findings. Each ADDM finding belongs to one of three types:
Problem
Findings that describe the root cause of a database performance issue.
Symptom
Findings that contain information that often lead to one or more problem findings.
Information
Findings that are used to report areas of the system that do not have a performance impact.
Each problem finding is quantified with an estimate of the portion of DB time that resulted from the performance problem that was found.
When a specific problem has multiple causes, ADDM may report multiple findings. In this case, the impacts of these multiple findings can contain the same portion of DB time. Because the performance problems can overlap, summing all the impacts of the reported findings can yield a number higher than 100 percent of DB time. For example, if a system performs many read I/Os, ADDM may report a SQL statement responsible for 50 percent of DB time due to I/O activity as one finding, and an undersized buffer cache responsible for 75 percent of DB time as another finding.
A problem finding can be associated with a list of recommendations for reducing the impact of a performance problem. Each recommendation has a benefit that is an estimate of the portion of DB time that can be saved if the recommendation is implemented. When multiple recommendations are associated with an ADDM finding, the recommendations may contain alternatives for solving the same problem. In this case, the sum of the benefits may be higher than the impact of the finding. You do not need to apply all the recommendations to solve the same problem.
Recommendations are composed of actions and rationales. You need to apply all the actions of a recommendation to gain the estimated benefit of that recommendation. The rationales explain why the set of actions were recommended, and provide additional information for implementing the suggested recommendation. An ADDM action may present multiple solutions to you. If this is the case, choose the easiest solution to implement.
On the Automatic Database Diagnostic Monitor (ADDM) page shown in Figure 3-2, three ADDM finding are displayed in the Performance Analysis section, as shown in Figure 3-3.
To implement ADDM recommendations:
On the Automatic Database Diagnostic Monitor (ADDM) page, under Performance Analysis, click the ADDM finding that has the greatest impact.
In this example, there are two ADDM findings with 100% impact. The first ADDM finding dealing with host CPU will be first examined.
The Performance Finding Details page appears.
Under Recommendations, identify the recommendations and required actions for each recommendation.
In this example, two recommendations are displayed for this finding. The first recommendation contains two actions and is estimated to have a maximum benefit of up to 100% of DB time in the analysis period. The second recommendation contains one action and is estimated to have a maximum benefit of up to 93% of DB time in the analysis period.
Perform the required action of a chosen recommendation.
In this example, the most effective solution is to use Oracle Database Resource Manager to prioritize the workload from various consumer groups and add more CPUs to the host system. However, adding CPUs to the host system may be costly. Running the SQL Tuning Advisor on the high-load SQL statement that ADDM has identified is easier to implement and can still provide a significant improvement.
To run the SQL Tuning Advisor on the SQL statement, click Run Advisor Now. This will immediately run a SQL Tuning Advisor task on the SQL statement.
See Also:
Chapter 10, "Tuning SQL Statements" for information about tuning SQL statements
You can view the data contained in snapshots taken by the AWR using Oracle Enterprise Manager. Typically, it is not necessary to review snapshot data because they consist primarily of raw statistics. Instead, you should rely on ADDM, which analyzes these statistics automatically to identify performance problems. Snapshot statistics should be used primarily by advanced users, or by DBAs who are accustomed to using Statspack for performance analysis.
On the Database Administration page, under Statistics Management, click Automatic Workload Repository.
The Automatic Workload Repository page appears.
Under Manage Snapshots and Preserved Snapshot Sets, click the Snapshots link.
The Snapshots page appears.
To view the statistics gathered in a snapshot, click the ID link of the snapshot you want to view.
The Snapshot Details page appears with the statistics gathered from the previous snapshot (snapshot 2283) to the selected snapshot (snapshot 2284) displayed.
To view a Workload Repository report of the statistics, click the Report tab.
The Workload Repository report is displayed.
