|
Automatic
Database Diagnostic Monitor in the Oracle 10g database
|
More Resources by
Google: |
|
|
|
|
|
‘Live as if
you were to die tomorrow. Learn as if you were to live forever.’
Gandhi
|
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 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’)
/
|