Surveying Diagnostics

Tools in Oracle Database 10g store diagnostic information and provide answers.

Oracle Database 10g introduced Automatic Database Diagnostic Monitor (ADDM), which enables the database to self-analyze its performance. The database can identify potential bottlenecks and fix them automatically or recommend a solution to an administrator. To make solutions or recommendations, ADDM uses the statistical data saved in Automatic Workload Repository (AWR). Oracle Database 10g also provides several advisors that guide DBAs in managing and tuning key components of the database.

This column focuses on how AWR, ADDM, and the advisors work to make performance tuning easy. It presents sample questions of the type that you may encounter when taking the Oracle Database 10g Administration Workshop I exam. The Oracle Database 10g Administration Workshop I exam enables you to earn the Oracle Certified Associate (OCA) level of certification. Note that the sample question format has been adjusted for presentation in this column.

Automatic Workload Repository

AWR is a built-in repository in Oracle Database 10g. At regular intervals, the Oracle database takes a snapshot of all its vital statistics as well as workload information and stores the snapshots in AWR. You can display the gathered data in both reports and views. By default, the AWR snapshots are captured once every hour and are retained in the database for seven days.

You need to change the collection interval and retention period of AWR snapshots for your database. Which two options could you use to change these settings?

A. Oracle Enterprise Manager 10g
B. Procedures in the DBMS_WORKLOAD_REPOSITORY package
C. An ALTER DATABASE SQL command
D. Procedures in the DBMS_ADVISOR package

The correct answers are A and B. To access AWR through Oracle Enterprise Manager 10g, select the Automatic Workload Repository link under Workload on the Administration page. Click on the Edit button on the Automatic Workload Repository page to open the Edit Settings page. Use this page to modify AWR snapshot settings. Alternatively, you can use the MODIFY_SNAPSHOT_SETTINGS procedure in the DBMS_WORKLOAD_REPOSITORY package to modify AWR snapshot settings. Answers C and D are incorrect because Oracle Database 10g does not support direct SQL access to repository settings, and you cannot use the procedures in the DBMS_ADVISOR package to modify AWR settings.

Automatic Database Diagnostic Monitor

ADDM is a self-diagnostic engine that analyzes AWR data on a regular basis. It then identifies the root causes of any performance issues and provides recommendations for resolving them. You can view the results of the ADDM analysis by using Oracle Enterprise Manager 10g, as shown in Figure 1. Alternatively, you can also run ADDM manually by using the addmrpt.sql script and DBMS_ADVISOR package application- programming interfaces (APIs).

An ADDM analysis is performed automatically each time ______. (Choose all that apply.)

A. A datafile is added to any of the tablespaces
B. A new tablespace is added to the database
C. A new user is added to the database
D. An AWR snapshot is taken

The correct answer is D. An ADDM analysis is automatically performed every time an AWR snapshot is taken, and the analysis results are saved in the database. Answers A, B, and C are incorrect because an ADDM analysis is not performed when a datafile, a tablespace, or a user is added to the database.

Which two statements are correct regarding ADDM?

A. ADDM presents information for only those areas in a database that have performance issues.
B. ADDM does not target the tuning of individual user response times.
C. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable automatic database diagnostic monitoring.
D. The results of each ADDM analysis are stored in the SYSTEM tablespace.

The correct answers are B and C. ADDM does not target the tuning of individual response times. You can use user-tracing techniques to tune for individual response times. ADDM is enabled by default and controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable automatic database diagnostic monitoring. Setting STATISTICS_LEVEL to BASIC disables automatic database diagnostic monitoring. Answer A is incorrect because ADDM also documents areas of the database that have no performance issues. (This information allows you to quickly identify the areas in which there is little to be gained by performing actions and, therefore, saves you time and effort.) Answer D is incorrect because results of each ADDM analysis are stored in AWR, and AWR is stored in the SYSAUX tablespace.

Users complain that database performance was slow between 9 p.m. and 11 p.m. the previous night. AWR snapshots are being taken every hour, and the snapshot retention period is set to seven days. You plan to use the ADDM findings to identify the cause of performance issues and take suitable action to avoid such issues in the future.

What would you do?

A. Use the latest ADDM report.
B. Check the alert log entries for the ADDM recommendations.
C. Create and run a custom ADDM task.
D. Modify the AWR settings so that the snapshots are taken every two hours.

The correct answer is C. You can create a custom ADDM task to invoke an ADDM analysis on the AWR snapshots that are taken during the specific time. For example, you can choose the snapshot that was taken before or at 9 p.m. and the snapshot that was taken after or at 11 p.m. the previous night. The simplest way to run an ADDM analysis over a specific time period is with Oracle Enterprise Manager 10g, as shown in Figure 2. Answer A is incorrect because the latest ADDM report would provide findings for the last hour. It would not be a clear indication of the performance issues during the previous night. Answer B is incorrect because alert log entries do not provide ADDM recommendations. Answer D is incorrect because modifying the AWR snapshot interval setting applies to future snapshots only and will not help in identifying the cause of performance issues that occurred the previous night.

Advisor Framework

Oracle Database 10g includes a number of advisors for different subsystems in the database to automatically determine how the operation of the corresponding subcomponents could be further optimized. Typically, advisors are run either directly or in response to an ADDM performance finding. The advisor output provides recommendations that help you in tuning subsystems of the database, such as memory and undo.

Your ADDM analysis report (in Figure 1) shows the following finding:

SQL statements consuming significant database time were found.

ADDM recommends that you run SQL Tuning Advisor. SQL Tuning Advisor recommendations would help you in _____.

A. Shrinking appropriate segments
B. Setting the undo retention suitably
C. Setting the buffer cache size appropriately
D. Improving the execution plan of the SQL statements

The correct answer is D. The SQL Tuning Advisor provides recommendations that improve the execution plan of SQL statements. The SQL Tuning Advisor takes one or more SQL statements as input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of recommendations, along with a rationale for each recommendation and its expected benefit.

Conclusion

In Oracle Database 10g, the statistical data needed for the accurate diagnosis of a problem is saved in AWR. ADDM is a diagnostic tool that automatically and proactively analyzes the data in AWR to determine whether there are major issues with the database system. ADDM also provides recommendations to resolve performance issues. ADDM recommendations may suggest that you run an advisor to optimize performance, and you can use recommendations from the advisors to resolve performance issues and/or improve performance.

REFERENCES

Surveying Diagnostics By Aradhana Puri