ADDM was introduced in Oracle 10 and stands for Automatic Database Diagnostic Monitor. You can use it to help solve performance problems. I was looking through some old copies of Oracle Magazine,
found an article about it, written by Kimberly Floss in May/June 2004,
and decided to give it a try. The article provided some SQL to produce
an ADDM report so I ran it against an Oracle 12 database which nobody
was using:
SQL> select dbms_advisor.get_task_report
2 (task_name, 'TEXT', 'ALL')
3 as addm_report
4 from dba_advisor_tasks
5 where task_id=
6 (select max(t.task_id)
7 from dba_advisor_tasks t,
8 dba_advisor_log l
9 where t.task_id = l.task_id
10 and t.advisor_name = 'ADDM'
11 and l.status = 'COMPLETED')
12 /
ADDM_REPORT
--------------------------------------------------------------------------------
ADDM Report for Task 'ADDM:1254537384_1_154'
--------------------------------------------
Analysis Period
---------------
AWR snapshot range from 153 to 154.
Time period starts at 02-APR-15 18.00.56
Time period ends at 02-APR-15 19.00.11
Analysis Target
---------------
Database 'ORCL1' with DB ID 1254537384.
Database version 12.1.0.1.0.
ADDM performed an analysis of instance orcl1, numbered 1 and hosted at
WIN-HUVS4H99T56.
Activity During the Analysis Period
-----------------------------------
Total database time was 0 seconds.
The average number of active sessions was 0.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 99% of the analysis
period.
SQL>
This said that there was nothing to report, as you might expect. The following lines refer to an AWR snapshot:
AWR snapshot range from 153 to 154.
Time period starts at 02-APR-15 18.00.56
Time period ends at 02-APR-15 19.00.11
AWR stands for Automatic Workload Repository, which was also introduced in Oracle 10. A snapshot of this is taken every hour by default and ADDM uses these snapshots to produce its reports.
Time period ends at 02-APR-15 19.00.11
AWR stands for Automatic Workload Repository, which was also introduced in Oracle 10. A snapshot of this is taken every hour by default and ADDM uses these snapshots to produce its reports.
In
the example above, the ADDM report had no recommendations but, in the
normal course of events, you will be running ADDM on databases with
problems. In this case, you might see output like this, which came from a
Windows server trying to host 6 good-sized Oracle 11 databases with
only 12 gigabytes of RAM:
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging
Impact is .22 active sessions, 100% of total activity.
------------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
Recommendation 1: Host Configuration
Estimated benefit is .22 active sessions, 100% of total activity.
-----------------------------------------------------------------
Action
Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that do
not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more physical
memory to the host.
The next example came from a third-party application which was doing lots of COMMIT’s (sometimes up to 1000 each second):
Finding 3: Commits and Rollbacks
The next example came from a third-party application which was doing lots of COMMIT’s (sometimes up to 1000 each second):
Finding 3: Commits and Rollbacks
Impact is .47 active sessions, 34.34% of total activity.
--------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .47 active sessions, 34.34% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 9966 transactions per minute with an
average redo size of 1240 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is .47 active sessions, 34.34% of total activity.
-------------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 3 K and the
average time per write was 10 milliseconds.
Rationale
The total I/O throughput on redo log files was 217 K per second for
reads and 446 K per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 67% by Log Writer, 0% by Archiver, 0% by Streams AQ and 32% by
all other activity.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .47 active sessions, 34.34% of total activity.
You might take quite a while to work out problems like these but, by using ADDM, you can diagnose them quickly. I will look at further examples in future posts.
You might take quite a while to work out problems like these but, by using ADDM, you can diagnose them quickly. I will look at further examples in future posts.
No comments:
Post a Comment