IT Friends

ADDM overviewAutomatic Database Diagnostic Monitor (ADDM)

is a tool built into Oracle Database 10g that analyzes performance and diagnoses any problems that occur. The objective of ADDM is to make the best use of your resources by enabling a database to perform tasks in the shortest possible time.
ADDM runs automatically and analyzes the root causes of problems rather than simply dealing with their symptoms. It details how the whole database is affected by issues and offers solutions. It also outlines the benefits for the system of choosing a particular solution.
ADDM identifies areas in the database that are running well and are not contributing to performance difficulties. This means that you avoid making changes to processes that do not impact the performance of the database.
ADDM can recommend changes in a number of areas of your database:
hardware
database configuration
schemas
applications
other advisors
hardware
ADDM can suggest that you add CPUs to your network or reconfigure the I/O subsystems.
database configuration
ADDM can recommend that you change the basic initialization parameter settings of your database.
schemas
ADDM can advise you to change the schema by hash-partitioning a table or index, or by using Automatic Segment Space Management (ASSM).
applications
ADDM may recommend that you use the cache option for sequences or, alternatively, use bind variables.
other advisors
If ADDM cannot recommend changes, it can suggest that you run the SQL Tuning Advisor on high-load SQL or run the Segment Advisor on hot objects. If ADDM cannot suggest a solution, it can instead recommend other database specialists, such as the SQL Tuning Advisor and the Segment Advisor.
ADDM interacts with the following components to analyze database performance:
SGA
MMON
AWR
EM
SGA
The System Global Area (SGA) is shared memory created for each instance in a database. It contains data and control information. The database takes statistical information in the form of a snapshot from it automatically every 60 minutes.

The SGA is accessed by the MMON process.
MMON
The Manageability Monitor (MMON) process takes snapshots of the information stored in the SGA. A snapshot is a description of the database performance over a particular time period.

The MMON sends snapshots to the AWR and they can be viewed using EM.
AWR
Snapshots taken by the MMON are stored in an area of memory created by the instance called the Automatic Workload Repository (AWR). It is also used to store the results of the ADDM analysis.
EM
ADDM processes the statistical information in the snapshots and outputs the results of its analysis to Enterprise Manager (EM). You can view the findings and take action to resolve issues based on the recommendations made. ADDM is scheduled to run automatically every 60 minutes by the MMON process in every instance.
ADDM analyzes a database process by measuring the time it takes to complete an action – called the waiting time – and comparing it to previously entered threshold values. This analysis model is called the time statistics model.

If the threshold value is exceeded, ADDM examines the factors that are causing the delay. This is a top-down process, where ADDM examines the symptoms of the problem first and then drills down through the various contributing processes to isolate the root cause.
If a process is consuming less time than the threshold value, it is passed over and ADDM moves on to the next factor. In this way, you are aware of the areas that are running well and not impacting the database performance.
When a process exceeds its threshold, ADDM drills down and checks the next layer of factors, again eliminating those that are not exceeding their respective thresholds.
ADDM drills down from the symptom of the problem to its root cause.
Previous versions of the Oracle database used Statspack snapshots instead of ADDM for performance monitoring. However, ADDM snapshots are more granular and detailed.
Additionally, ADDM identifies the most serious problems first, enabling you to isolate and resolve them more quickly.
A major advantage of ADDM is that running it does not have a great impact on the performance of the database.
Question

How does ADDM identify problem areas in a database?

Options:

It details the root causes of each problem
It identifies areas that are running without problems
It prioritizes problems according to wait time
It uses Statspack snapshots

Answer
ADDM details the root causes of each problem. It also identifies areas that are running without problems and prioritizes problems according to the wait time.

Option 1 is correct. ADDM uses a top-down process to drill down to the root causes of a problem. This allows you to isolate and resolve the issues more quickly and efficiently. ADDM can recommend changes in hardware, database configuration, schemas, applications, and other advisors.

Option 2 is correct. As ADDM drills down, it differentiates between processes that are negatively impacting performance and those that are running efficiently.

Option 3 is correct. ADDM compares the wait time for each process to given threshold values. If the process exceeds these values, it is examined to identify the contributing processes at the next level.

Option 4 is incorrect. ADDM snapshots are more detailed and granular than Statspack snapshots, making it a more effective tool for diagnosing and solving problems.
2. Viewing ADDM findingsSuppose you want to view the results of the last automatic ADDM run. You can do this using EM or by using SQL code.
In EM, the Diagnostic Summary section is where the number of ADDM findings from the last run are displayed.
You click the Performance Findings link.
The ADDM page opens and the results are shown.
The database activity graph indicates database activity increases rapidly, beginning at approximately 2:00 am. The color also indicates that the increase in activity was caused by a Wait issue.
The highlighted checkbox listed beneath the graph indicates the time in the graph that corresponds to the findings currently displayed on the page.
Further down the ADDM page, the Database Time shows the amount of non-idle time spent by database sessions during the period of the ADDM analysis.
The time at which the analysis started is displayed, as is the duration of the analysis and the average number of sessions that were active over the period. The task owner, SYS, in this case, is also shown.
The Performance Analysis section lists a table of findings, each one with a set of recommendations, and an impact value, which represents the amount of time consumed by the issue compared to the Database Time. You can access detailed information about each finding by clicking it.
Suppose you want to view a report of the analysis in text form.
You click View Report.
A text file opens with details about the ADDM findings and recommendations.
You click Save to File to save the report.
When you have saved the report, you can return to the Performance Analysis section of the ADDM page. You want information about the first issue listed.
You select the first finding in the list.
The Performance Finding Details page offers recommendations for solving the issue that you selected.
The recommendations are placed in different categories, such as SQL Tuning and Host Configuration.
You click Show to display more information about any of the recommendations.
The Benefit column shows you the maximum percentage improvement in performance that you can expect as a result of taking this action.
Question
You want to view the last ADDM analysis of the database performance and display the recommendations for solving the second most significant performance issue involved.

Which option allows you to navigate to the ADDM page and display the recommendations for the second most significant issue?

Options:

Click the Performance Findings link and select the recommendations for the second issue in the list
Click the Performance Findings link and then click View Report
Click the Performance tab

Answer
You click the Performance Findings link and select the recommendations for the second issue in the list.

You can use SQL code to retrieve an ADDM report. This involves using the GET_TASK_REPORT procedure from the DBMS_ADVISOR package.
Question
You can view the findings of an ADDM analysis using the DBMS_ADVISOR package.

Enter the name of the procedure in this package that enables you to retrieve ADDM findings.

SELECT dbms_advisor.MISSING CODE(task_name)

Answer
The procedure in the DBMS_ADVISOR package that allows you to retrieve ADDM findings is GET_TASK_REPORT.
You can use the addmrpt.sql script to run ADDM from the rdbms/admin directory.
You can run ADDM on any two snapshots that are available, provided that they have been taken by the same instance.
This script can provide the ID of the database and list the snapshot identifiers for the last three days, allowing you to determine which snapshots you want to analyze.
3. Creating an ADDM taskAn Oracle 10g database runs ADDM tasks automatically on every snapshot stored in the AWR.
You can create an ADDM task to analyze the database performance over a particular period of time by specifying snapshots taken at the beginning and end of that period.
Suppose you want to create a new ADDM task to examine the database performance between 3:00 am and 10:00 am, as this is a period already identified as having a significant increase in database activity.

To do this, you need to access Advisor Central, which is listed under the Related Links section of the EM Home page.
You click Advisor Central.
The Advisor Central page offers a selection of different database advisors and the option to create ADDM tasks, which is what you want to do in this case.
You click ADDM.
In the Create ADDM Task page, you can specify the start and end times for your performance analysis. The Period Start Time radio button is selected by default. Now you must set your start time.
You select the snapshot from 3:00 am.
The time of your chosen snapshot – 3:00 am – is entered as the start time. Now you want to specify a snapshot for the end of the analysis period.
You select the Period End Time radio button, select the snapshot from 10:00 am, and click OK.
The ADDM page opens and displays information on the performance of your database over the time period you specified. It also gives you a recommendation for resolving the issues.
Note
You can also access the results of your manually created ADDM task from the Advisor Central page of EM.

Question
Suppose you receive a complaint from a database user complaining that their database was performing very slowly between 2:00 am and 4:00 am on July 1, 2004. You decide to create an ADDM task to analyze performance for that time period.

Which steps do you take to carry out an analysis of the database performance for the period in question?

Options:

Select 2:00 am as the period start time and 4:00 am as the period end time and then click OK
Select 4:00 am as the period start time and 2:00 am as the period end time and then click OK
Click Performance Findings and select the relevant finding from the list

Answer
You select 2:00 am as the period start time and 4:00 am as the period end time and then click OK.

4. Adjusting ADDM attributesADDM analysis is enabled by default in an Oracle 10g database. You can verify this by checking the STATISTICS_LEVEL initialization parameter.
This parameter should be set to TYPICAL or ALL. Setting it to BASIC will deactivate the automatic ADDM analysis.
STATISTICS_LEVEL = TYPICALSTATISTICS_LEVEL = ALL

ADDM can be set to measure the performance of an I/O device, such as a hard disk, in your database. This is done by comparing the time the device currently takes to read a database block against the expected time set for the I/O subsystem.

The expected time is defined using the DBIO_EXPECTED parameter, the value of which is set in microseconds.
Note
A time of 10 milliseconds (ms) – or 10,000 microseconds – is regarded as a good average for modern hard disks. The value of this setting varies according to your installed hardware.
As ADDM analysis of I/O performance depends on the value of DBIO_EXPECTED. You should change this value for non-standard hardware.
To do this, you must
measure the average processing time
set the DBIO_EXPECTED parameter to this value
When determining average processing time, you need to include the seek time – the time taken for a hard disk drive head to move from one track to another – in your calculation.
You need to enter the result of your measurement, in microseconds, as the expected value for the device by specifying it as the DBIO_EXPECTED parameter.
Suppose that, having calculated average processing time, you want to set the DBIO_EXPECTED parameter to 10,000 microseconds for ADDM analysis.
exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(-
‘ADDM’, ‘DBIO_EXPECTED’, ‘expected I/O performance in microseconds);
You do this using the SET_DEFAULT_TASK_PARAMETER procedure of the DBMS_ADVISOR package, passing, in this order, ‘ADDM’, ‘DBIO_EXPECTED’, and ‘10000′ as your parameters.
exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(-’ADDM’, ‘DBIO_EXPECTED’, 10000);
Question
Suppose you have measured a speed of 20,000 microseconds for an I/O device.

Fill the correct terms into the following code to set the DBIO_EXPECTED parameter to 20,000.

exec DBMS_ADVISOR SET_DEFAULT_TASK_PARAMETER(MISSING CODE);

Answer
You type ‘ADDM’,'DBIO_EXPECTED’, 20000 as the parameters.
You can check the value assigned to the DBIO_EXPECTED parameter by entering this SELECT statement.
SELECT parameter_value, is_defaultFROM dba_advisor_def_parametersWHERE advisor_name = ‘ADDM’ AND parameter_name =’DBIO_EXPECTED’;
Question

Which values do you enter for SET_DEFAULT_TASK_PARAMETER in order to set the expected speed of an I/O device to 5 milliseconds for an ADDM analysis?

Options:

ADDM
DBIO_EXPECTED
5
5000
STATISTICS_LEVEL

Answer
You enter the values ADDM, DBIO_EXPECTED, and 5000 to set the expected speed of an I/O device to 5000 microseconds for an ADDM analysis.

Option 1 is correct. There are a number of advisors available, including the SQL Tuning Advisor and the Segment Advisor. You need to specify that you want to carry out an ADDM analysis.

Option 2 is correct. The DBIO_EXPECTED parameter is assigned the average time required to read a single database block. This parameter will then be compared to the current performance.

Option 3 is incorrect. You set the speed of an I/O device in microseconds and not in milliseconds.

Option 4 is correct. You enter a value of 5 milliseconds, or 5000 microseconds, at the end of the statement in order to declare it as the DBIO_EXPECTED parameter.

Option 5 is incorrect. You set the STATISTICS_LEVEL initialization parameter to TYPICAL or ALL to enable an automatic ADDM analysis of the database performance.
SummaryAutomatic Database Diagnostic Monitor (ADDM) is used to analyze performance issues that arise on Oracle 10g databases. It has a number of advantages over previously used analysis tools. It drills down to the root causes of a problem, rather than simply alerting you to the symptoms. It also identifies areas of database operation that are performing well and do not require attention. It makes recommendations for solving issues and outlines the benefits of taking each action.

You can view the results of an ADDM process using EM or by entering SQL. EM displays information about the analysis on the ADDM page. It indicates whether any performance issues arose during that period and gives information about the nature of those issues. It also displays a list of factors contributing to performance problems and details their relative impact on the system.

ADDM analysis runs automatically whenever a database instance is created. You can create your own custom ADDM tasks to analyze your database’s performance during specific periods of interest. To run ADDM, you navigate to the Create ADDM Task page and specify the snapshots that correspond to the time period that you are interested in. Then you navigate to the ADDM page where the results are displayed in the Performance Analysis section.

If you want ADDM to run automatically, you need to ensure that the STATISTICS_LEVEL parameter is set to TYPICAL, which is the default value, or ALL. You can upgrade the ADDM analysis of an I/O device by measuring its current speed and then assigning this value to the DBIO_EXPECTED parameter. The performance of the device is then compared to this expected speed.
Table of Contents | Top of page |

| Learning objective |

| 1. ADDM overview |

| 2. Viewing ADDM findings |

| 3. Creating an ADDM task |

| 4. Adjusting ADDM attributes |