|
‘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
Introduction
The automatic Database
Diagnostic Monitor (ADDM) maintains a self-diagnostic to a database.
It will either perform a treatment or refer it to specialists such as
the SQL tuning advisor.
Gathering Statistics
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 two last
snapshots. It is possible also to invoke an ADDM analysis manually.
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.
Disable ADDM
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.
Default Setting
To check your default
setting, execute the following SQL statement.
SQL> SELECT
parameter_value, is_default
FROM
dba_advisor_def_parameters
WHERE advisor_name = ‘ADDM’
/
Most recent ADDM using SQL
To 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
The MMAN background process
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.
Disabling or enabling MMAN
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.
The actual size of the
auto-tuned components
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 is 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’
/
Changing 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.
Automatic Checkpoint Tuning
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-
Gathering statistics and creating snapshots
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, 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
load
To 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’)
/
|
“If a man is called
to be a streetsweeper, he should sweep streets even as
Michelangelo painted, or Beethoven composed music, or
Shakespeare wrote poetry. He should sweep streets so well that
all the host of heaven and earth will pause to say, here lived a
great streetsweeper who did his job well.” Martin Luther King
Jr. |
Questions:
Questions on
Automatic
Database Diagnostic Monitor in the Oracle 10g database
Q: What does the Automatic
Database Diagnostic Monitor?
Q: How does ADDM work?
Q: Where can I access the
latest ADDM run?
Q: How can I turn the ADDM
process off?
Q: How can you check your
ADDM default setting?
Q: How can I retrieve ADDM
Reports using SQL?
Q: What is the Automatic
Shared Memory Management (MMAN)?
Q: How do you enable or
disable Automatic Shared Memory Management?
Q: How do you determine the
actual size of the auto-tuned components in the SGA?
Q: How do you change the
SGA_TARGET value?
Q: What is Automatic
Checkpoint Tuning?
|