Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Directory wise folder wise space usage Linux/Unix
  • create a folder in multiple places Linux/Unix
  • xargs use Linux/Unix
  • Renaming Global Name GLOBAL_NAME Oracle
  • Transfer SQL Profiles from One database to other database. Oracle
  • block_ident.sql Oracle
  • How to sort list of files on basis of their sizes. Linux/Unix
  • How to analyze statspack or AWR report. Oracle
  • find_longsql.sql Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • Distributed Transaction Troubleshooting. Oracle
  • exp syntax in oracle 10g Oracle
  • More info about /proc folder and its relation with processes. Linux/Unix
  • Which environment is used by currently running process ( Very good) Linux/Unix
  • First Entry in RAC Oracle

Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR

Posted on 06-Sep-2005 By Admin No Comments on Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR

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.

Oracle, SQL scripts

Post navigation

Previous Post: Shuffle an array
Next Post: How to Modify the statistics collection by MMON for AWR repository

Related Posts

  • DBMS_UTILITY PACKAGE Oracle
  • Jai Shree Ram Oracle
  • Another export with Query Oracle
  • Good notes for shared pool Oracle
  • How to find the real execution plan and binds used in that explain plan in Oracle 10g?? Oracle
  • sql_doing_fts.sql Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Read CSV file using PySpark Python/PySpark
  • Running some SQL on multiple databases connecting using monitoring userid and password Linux/Unix
  • Oracle Statspack survival Guide Oracle
  • plan10g.sql good Oracle
  • Distributed Transaction Troubleshooting. Oracle
  • scripts to take listener.log backup Linux/Unix
  • Important Solaris Commands Linux/Unix
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme