|
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�
/
or
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 oracle user to use the above tablespace as
its default tablespace and grant the DBA role to it.
SQL> ALTER USER oracle
DEFAULT TABLESPACE test4addm
/
SQL> GRANT dba TO oracle
/
Connect as oracle 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=>�ORACLE�,
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 oracle;
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�)
/ |
