Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR
——————————————————————————————
The Automatic Database Diagnostic Monitor (hereafter called ADDM) is an integral part of the Oracle RDBMS capable of gathering performance statistics and advising on changes to solve any exitsing performance issues measured.
For this it uses the Automatic Workload Repository ( hereafter called AWR), a repository defined in the database to store database wide usage statistics
at fixed size intervals (60 minutes).
To make use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been implemented. This PL/SQL interface may be called thourgh the supplied $ORACLE_HOME/rdbms/admin/addmrpt.sql script, called directly, or used in combination with the Oracle Enterprise Manager application. Besides this PL/SQL package a number of views (with names starting with the DBA_ADVISOR_ prefix) allow retrieval of the results of any actions performed with the
DBMS_ADVISOR API. The preferred way of accessing ADDM is through the Enterprise Manager interface, as it shows a complete performance overview including recommendations on how to solve bottlenecks on a single screen. When accessing ADDM manually, you should consider using the ADDMRPT.SQL script provided with your Oracle release, as it hides the complexities involved in accessing the DBMS_ADVISOR package.
To use ADDM for advising on how to tune the instance and SQL, you need to make sure that the AWR has been populated with at least 2 sets of performance data. When the STATISTICS_LEVEL is set to TYPICAL or ALL the database will automatically schedule the AWR to be populated at 60 minute intervals.
When you wish to create performance snapshots outside of the fixed intervals, then you can use the DBMS_WORKLOAD_REPOSITORY package for this, like in:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(‘TYPICAL’);
END;
/
The snapshots need be created before and after the action you wish to examine.
E.g. when examining a bad performing query, you need to have performance data snapshots from the timestamps before the query was started and after the query finished.
Example:
——–
You can use ADDM through the PL/SQL API and query the various advisory views in SQL*Plus to examine how to solve performance issues.
The example is based on the SCOTT account executing the various tasks. To allow SCOTT to both generate AWR snapshots and sumbit ADDM recommendation jobs, he needs to be granted proper access:
CONNECT / AS SYSDBA
GRANT ADVISOR TO scott;
GRANT SELECT_CATALOG_ROLE TO scott;
GRANT EXECUTE ON dbms_workload_repository TO scott;
Furthermore, the buffer cache size (DB_CACHE_SIZE) has been reduced to 24M.
The example presented makes use of a table called BIGEMP, residing in the SCOTT schema. The table (containing about 14 million rows) has been created with:
CONNECT scott/tiger
CREATE TABLE bigemp AS SELECT * FROM emp;
ALTER TABLE bigemp MODIFY (empno NUMBER);
DECLARE
n NUMBER;
BEGIN
FOR n IN 1..18 LOOP
INSERT INTO bigemp SELECT * FROM bigemp;
END LOOP;
COMMIT;
END;
/
UPDATE bigemp SET empno = ROWNUM;
COMMIT;
The next step is to generate a performance data snapshot:
EXECUTE dbms_workload_repository.create_snapshot(‘TYPICAL’);
Execute a query on the BIGEMP table to generate some load:
SELECT * FROM bigemp WHERE deptno = 10;
After this, generate a second performance snapshot:
EXECUTE dbms_workload_repository.create_snapshot(‘TYPICAL’);
The easiest way to get the ADDM report is by executing:
@?/rdbms/admin/addmrpt
Running this script will show which snapshots have been generated, asks for the snapshot IDs to be used for generating the report, and will generate the report containing the ADDM findings.
When you do not want to use the script, you need to submit and execute the ADDM task manually.
First, query DBA_HIST_SNAPSHOT to see which snapshots have been created. These snapshots will be used by ADDM to generate recommendations:
SELECT * FROM dba_hist_snapshot ORDER BY snap_id;
SNAP_ID DBID INSTANCE_NUMBER
———- ———- —————
STARTUP_TIME
———————————————————————–
BEGIN_INTERVAL_TIME
———————————————————————–
END_INTERVAL_TIME
———————————————————————–
FLUSH_ELAPSED
———————————————————————–
SNAP_LEVEL ERROR_COUNT
———- ———–
1 494687018 1
17-NOV-03 09.39.17.000 AM
17-NOV-03 09.39.17.000 AM
17-NOV-03 09.50.21.389 AM
+00000 00:00:06.6
1 0
2 494687018 1
17-NOV-03 09.39.17.000 AM
17-NOV-03 09.50.21.389 AM
17-NOV-03 10.29.35.704 AM
+00000 00:00:02.3
1 0
3 494687018 1
17-NOV-03 09.39.17.000 AM
17-NOV-03 10.29.35.704 AM
17-NOV-03 10.35.46.878 AM
+00000 00:00:02.1
1 0
———————————————————————–
Mark the 2 snapshot IDs (such as the lowest and highest ones) for use in generating recommendations.
Next, you need to submit and execute the ADDM task manually, using a script similar to:
DECLARE
task_name VARCHAR2(30) := ‘SCOTT_ADDM’;
task_desc VARCHAR2(30) := ‘ADDM Feature Test’;
task_id NUMBER;
BEGIN
/*(1)*/ dbms_advisor.create_task(‘ADDM’, task_id, task_name, task_desc, null);
/*(2)*/ dbms_advisor.set_task_parameter(‘SCOTT_ADDM’, ‘START_SNAPSHOT’, 1);
dbms_advisor.set_task_parameter(‘SCOTT_ADDM’, ‘END_SNAPSHOT’, 3);
dbms_advisor.set_task_parameter(‘SCOTT_ADDM’, ‘INSTANCE’, 1);
dbms_advisor.set_task_parameter(‘SCOTT_ADDM’, ‘DB_ID’, 494687018);
/*(3)*/ dbms_advisor.execute_task(‘SCOTT_ADDM’);
END;
/
Here is the explanation of the steps you need to take to successfully execute an ADDM job:
1) The first step is to create the task. For this, you need to specify the name under which the task will be known in the ADDM task system. Along with the name you can provide a more readable description on what the job should do. The task type must be ‘ADDM’ in order to have it executed in the ADDM environment.
2) After having defined the ADDM task, you must define the boundaries within which the task needs to be executed. For this you need to set the starting and ending snapshot IDs, instance ID (especially necessary when running in a RAC environment), and database ID for the newly created job.
3) Finally, the task must be executed. When querying DBA_ADVISOR_TASKS you see the just created job:
SELECT * FROM dba_advisor_tasks;
OWNER TASK_ID TASK_NAME
—————————— ———- ——————————
DESCRIPTION
————————————————————————
ADVISOR_NAME CREATED LAST_MODI PARENT_TASK_ID
—————————— ——— ——— ————–
PARENT_REC_ID READ_
————- —–
SCOTT 5 SCOTT_ADDM
ADDM Feature Test
ADDM 17-NOV-03 17-NOV-03 0
0 FALSE
When the job has successfully completed, examine the recommendations made by ADDM by calling the DBMS_ADVISOR.GET_TASK_REPORT() routine, like in:
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT a80
SELECT dbms_advisor.get_task_report(‘SCOTT_ADDM’, ‘TEXT’, ‘TYPICAL’) FROM sys.dual;
The recommendations supplied should be sufficient to investigate the performance issue, as in:
DETAILED ADDM REPORT FOR TASK ‘SCOTT_ADDM’ WITH ID 5
—————————————————-
Analysis Period: 17-NOV-2003 from 09:50:21 to 10:35:47
Database ID/Instance: 494687018/1
Snapshot Range: from 1 to 3
Database Time: 4215 seconds
Average Database Load: 1.5 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 65% impact (2734 seconds)
————————————
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to the “Tuning PL/SQL Applications” chapter of Oracle’s “PL/SQL User’s Guide and Reference”
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
FINDING 2: 35% impact (1456 seconds)
————————————
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM
FINDING 3: 20% impact (836 seconds)
———————————–
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 20% benefit (836 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem. Oracle’s recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance.
RECOMMENDATION 2: Host Configuration, 14% benefit (584 seconds)
ACTION: The performance of file
D:ORACLEORADATAV1010UNDOTBS01.DBF was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks.
RELEVANT OBJECT: database file “D:ORACLEORADATAV1010UNDOTBS01.DBF”
SYMPTOMS THAT LED TO THE FINDING:
Wait class “User I/O” was consuming significant database time.
(34% impact [1450 seconds])
FINDING 4: 11% impact (447 seconds)
———————————–
Undo I/O was a significant portion (33%) of the total database I/O.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than expected. (20% impact [836 seconds])
Wait class “User I/O” was consuming significant database time.
(34% impact [1450 seconds])
FINDING 5: 9.9% impact (416 seconds)
————————————
Buffer cache writes due to small log files were consuming significant database time.
RECOMMENDATION 1: DB Configuration, 9.9% benefit (416 seconds)
ACTION: Increase the size of the log files to 796 M to hold at least 20 minutes of redo information.
SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than expected. (20% impact [836 seconds])
Wait class “User I/O” was consuming significant database time.
(34% impact [1450 seconds])
FINDING 6: 9.2% impact (387 seconds)
————————————
Individual database segments responsible for significant user I/O wait were found.
RECOMMENDATION 1: Segment Tuning, 7.2% benefit (304 seconds)
ACTION: Run “Segment Advisor” on database object “SCOTT.BIGEMP” with object id 49634.
RELEVANT OBJECT: database object with id 49634
ACTION: Investigate application logic involving I/O on database object “SCOTT.BIGEMP” with object id 49634.
RELEVANT OBJECT: database object with id 49634
RECOMMENDATION 2: Segment Tuning, 2% benefit (83 seconds)
ACTION: Run “Segment Advisor” on database object
“SYSMAN.MGMT_METRICS_RAW_PK” with object id 47084.
RELEVANT OBJECT: database object with id 47084
ACTION: Investigate application logic involving I/O on database
object “SYSMAN.MGMT_METRICS_RAW_PK” with object id 47084.
RELEVANT OBJECT: database object with id 47084
SYMPTOMS THAT LED TO THE FINDING:
Wait class “User I/O” was consuming significant database time.
(34% impact [1450 seconds])
FINDING 7: 8.7% impact (365 seconds)
————————————
Individual SQL statements responsible for significant physical I/O were found.
RECOMMENDATION 1: SQL Tuning, 8.7% benefit (365 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM
RECOMMENDATION 2: SQL Tuning, 0% benefit (0 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to the “Tuning PL/SQL Applications” chapter of Oracle’s “PL/SQL User’s Guide and Reference”
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than
expected. (20% impact [836 seconds])
Wait class “User I/O” was consuming significant database time.
(34% impact [1450 seconds])
FINDING 8: 8.3% impact (348 seconds)
————————————
Wait class “Configuration” was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Waits for free buffers were not consuming significant database time.
Waits for archiver processes were not consuming significant database time.
Log file switch operations were not consuming significant database time while waiting for checkpoint completion.
Log buffer space waits were not consuming significant database time.
High watermark (HW) enqueue waits were not consuming significant database time.
Space Transaction (ST) enqueue waits were not consuming significant database time.
ITL enqueue waits were not consuming significant database time.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
———————-
An explanation of the terminology used in this report is available when
you run the report with the ‘ALL’ level of detail.
The analysis of I/O performance is based on the default assumption that
the average read time for one database block is 5000 micro-seconds.
Wait class “Administrative” was not consuming significant database time.
Wait class “Application” was not consuming significant database time.
Wait class “Cluster” was not consuming significant database time.
Wait class “Commit” was not consuming significant database time.
Wait class “Concurrency” was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class “Network” was not consuming significant database time.
Wait class “Scheduler” was not consuming significant database time.
Wait class “Other” was not consuming significant database time.
============================= END OF ADDM REPORT ======================
ADDM points out which events cause the performance problems to occur and suggests directions to follow to fix these bottlenecks. The ADDM recommendations show amongst others that the query on BIGEMP needs to be examined; in this case it suggests to run the Segment Advisor to check whether the data segment is fragmented or not; it also advices to check the application logic involved in accessing the BIGEMP table. Furthermore, it shows the system suffers from I/O problems (which is in this example caused by not using SAME and placing all database files on a single disk partition).
The findings are sorted descending by impact: the issues causing the greatest performance problems are listed at the top of the report. Solving these issues will result in the greatest performance benefits. Also, in the last section of the report ADDM indicates the areas that are not representing a problem for the performance of the instance.
In this example the database is rather idle. As such the Enterprise Manager notification job (which runs frequently) is listed at the top. You need not worry about this job at all. Please notice that the output of the last query may differ depending on what took place on your database at the time the ADDM recommendations were generated.