iSelfSchooling - Automatic Database Diagnostic Monitor in the Oracle 10g database

The automatic Database Diagnostic Monitor (ADDM) maintains a self-diagnostic to a database. Either it will perform a treatment or refer it to specialists such as the SQL tuning advisor.

How does it?

The Oracle database automatically gathers statistics from the SGA every 60 minutes and stores them in the Automatic Workload Repository (AWR) in the form of snapshots. These snapshots are similar to STATSPACK snapshots. The MMON process, it is a process that schedules the ADDM to run automatically to detect problems proactively for every last two snapshots. It is possible also to invoke an ADDM analysis manually.

Where can I access the latest ADDM run?

Go to the Database Control home page, on the Diagnostic Summary section you will see the number of ADDM finding from the previous automatic run. Click on the Performance Findings link. The Automatic Database Diagnostic Monitor (ADDM) page will be display with the details of the latest ADDM run.

How can I turn it off?

By default the ADDM process is enabled since the STATISTICS_LEVEL initialization parameter is TYPICAL. By setting this parameter to BASIC will stop to run automatically.

How to check my default setting?

Execute the following SQL statement.

SQL> SELECT parameter_value, is_default

FROM dba_advisor_def_parameters

WHERE advisor_name = ‘ADDM’

How can I retrieve ADDM Reports using SQL?

Type the following SQL statement to display the most recent ADDM report using a SQL command.

SQL> SELECT dbms_advisor.GET_TASK_REPORT(task_name)

FROM dba_advisor_tasks

WHERE task_id = (SELECT max(t.task_id)

FROM dba_advisor_tasks t, dba_advisor_log l

WHERE t.task_id = l.task_id AND t.advisor_name = ‘ADDM’

AND l.status = ‘COMPLETED’

SQL> @$ORACLE_HOME/rdbms/addmrpt

What is the Automatic Shared Memory Management (MMAN)?

It maintains the management of the most important shared memory structures. For example, if your system runs OLTP during the day and large parallel batch jobs at night, you may not need to decrease buffer cache and increase large pool in order to satisfy the needs of your nightly jobs. The MMAN background process should do that.

How to enable or disable Automatic Shared Memory Management?

Go to your Database Control page. Click on the Administration tab, select Memory Parameters under the Instance heading, and click the SGA tab. Now, you are able to enable or disable. When you enable it you can enter the total SGA size or the SGA_TARGET value. If you set SGA_TARGET to 0, Automatic Shared Memory Management will be disabled.

How to determine the actual size of the auto-tuned components in the SGA?

When the SGA_TARGET value is set to no-zero, you can determine the actual size of the auto-tuned components in the SGA by the following SQL statement.

SQL> SELECT component, current_size/1024/1024

FROM v$sga_dynamic_components

Notice that if the SGA_TARGET value is no-zero and no value for an auto-tuned SGA parameter, then the values of the auto-tuned SGA parameters in the v$parameter view are 0. You will see the values if you assigned a value for any of the auto-tuned parameters.

SQL> SELECT name, value, isdefault

FROM v$parameter

WHERE name LIKE ‘%size’

How to change the SGA_TARGET value?

You can change it by using the ALTER SYSTEM command dynamically. The value can be increased up to the value of SGA_MAX_SIZE.

NOTICE: You should still manually gather statistics to collect system statistics and fixed objects.

What is Automatic Checkpoint Tuning?

It will make the best effort to write out dirty buffers without adverse impact on the database automatically. To enable it you should set the FAST_START_MTTR_TARGET value to a nonzero value and all the checkpoint parameters will be ignored.

Hands-On #1

Create a tablespace with a size of 50Meg, using manually segment space management and locally extent managed.

SQL> CREATE TABLESPACE "TEST4ADDM"

DATAFILE '/u02/oradata/ora10g/test4addm.dbf' SIZE 50M

LOGGING

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT MANUAL;

Alter the iself user to use the above tablespace as its default tablespace and grant the DBA role to it.

SQL> ALTER USER iself

DEFAULT TABLESPACE test4addm

SQL> GRANT dba TO iself

Connect as iself in SQL*Plus and create a table, gather statistics, and create a snapshot.

SQL> CREATE TABLE test (c1 number, c2 VARCHAR2(2000));

SQL> BEGIN

dbms_stats.gather_table_stats (

ownername=>’ISELF’, tablename=>’TEST’,

estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

END;

SQL> BEGIN

dbms_workload_repository.create_snapshot();

END;

Type the following SQL statement to display the most recent ADDM report using a SQL command.

SQL> SELECT dbms_advisor.GET_TASK_REPORT(task_name)

FROM dba_advisor_tasks

WHERE task_id = (SELECT max(t.task_id)

FROM dba_advisor_tasks t, dba_advisor_log l

WHERE t.task_id = l.task_id AND t.advisor_name = ‘ADDM’

AND l.status = ‘COMPLETED’

Write a SQL script to create add records into the table.

SQL> DECLARE

v_c1 CHAR(2000);

BEGIN

FOR this IN 1..15000 LOOP

v_c1 := ‘this is just a test ’ || this;

INSERT INTO test VALUES (this, v_c1);

COMMIT;

END LOOP;

END:

Check the problems.

SQL> SELECT dbms_advisor.GET_TASK_REPORT(task_name)

FROM dba_advisor_tasks

WHERE task_id = (SELECT max(t.task_id)

FROM dba_advisor_tasks t, dba_advisor_log l

WHERE t.task_id = l.task_id AND t.advisor_name = ‘ADDM’

AND l.status = ‘COMPLETED’

Now, if you drop the tablespace and recreate it with AUTO option instead of MANUAL and then repeat the process, you should not find any problem this time.

SQL> DROP TABLESPACE test4addm

INCLUDING CONTENTS AND DATAFILES

SQL> CREATE TABLESPACE "TEST4ADDM"

DATAFILE '/u02/oradata/ora10g/test4addm.dbf' SIZE 50M

LOGGING

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

To clean up your database:

SQL> DROP TABLESPACE test4addm

INCLUDING CONTENTS AND DATAFILES;

SQL> REVOKE DBA FROM iself;

Hands-On #2

Determine the effects of the database load on the memory buffers do the following:

SQL> SHOW PARAMETER sga_

SQL> COL component FORMAT a30

SQL> SELECT component, current_size, min_size, granule_size

FROM v$sga_dynamic_components

WHERE component in (‘shared pool’, ‘large pool’, ‘java pool’,

‘DEFAULT buffer cache’)

--OR--

SQL> COL name FORMAT a30

SQL> COL value FORMAT a30

SQL> SELECT name, value, isdefault

FROM v$parameter

WHERE name in (‘shared_pool_size’, ‘large_pool_size’, ‘java_pool_size’,

‘db_cache_size’)