Oracle has built-in reports that can give vital information
on the performance of your database.
Today I will show you ADDM (Automatic Database Diagnostic Manager). ADDM analyzes data from the Automatic
Workload Repository and reports it’s findings.
Oracle provides a script that will generate an ADDM
report. The script is named ‘addmrpt.sql’ and can be found in */product/12.2.0.1/*/rdbms/admin. Locate
the script on your machine and then execute that script from within SQL*Plus.
Once executed the script will display all snapIDs that are available for analyzing and their
associated timestamp. You need to
specify the snapID for the associated start time and
end time that you would like to observe.
You will also be prompted to specify a name for the output file,
however, you can just accept the default and hit enter.
The generated report will highlight areas of potential
concern along with recommendations for each item called out in the report. Below is an example of generating an ADDM
report from within SQL*Plus. If you
take a close look you will see Oracle has called out 4 different findings. The top finding is SQL Statements. The ADDM report goes on to provide
recommendations for specific SQL_IDs.
The ADDM may not always pinpoint to an exact issue, but it
can often point you to the area you need to focus on. As always, you can learn more by reviewing Oracle
documentation on this topic.
Happy reporting!
SQL> @/opt/oracle/base/product/12.2.0.1/demo/rdbms/admin/addmrpt.sql
Listing the last 3 days of Completed Snapshots
Instance DB Name
Snap Id
Snap Started
Snap Level
------------ ------------
---------- ------------------ ----------
DEMO DEMODB 71722 18
Oct 2018 00:01 1
71723 18
Oct 2018 00:30 1
71724 18
Oct 2018 01:00 1
71725 18
Oct 2018 01:30 1
.
.
71859 20
Oct 2018 20:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 71834
Begin Snapshot Id specified: 71834
Enter value for end_snap: 71859
End Snapshot Id
specified: 71859
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is
addmrpt_1_71834_71859.txt. To use this
name,
press <return> to continue, otherwise enter an
alternative.
Enter value for report_name:
Using the report name addmrpt_1_71834_71859.txt
Running the ADDM analysis on the specified pair of snapshots
...
Generating the ADDM report for this analysis ...
ADDM Report
for Task 'TASK_216884'
----------------------------------
Analysis Period
---------------
AWR snapshot range from 71834 to 71859.
Time period starts at 20-OCT-18 08.00.25 AM
Time period ends at 20-OCT-18 08.30.07 PM
Analysis Target
---------------
Database 'DEMODB' with DB ID 110721271.
Database version 12.2.0.1.0.
ADDM performed an analysis of instance DEMO, numbered 1 and
hosted at
demo.demo.com.
Activity During the Analysis Period
-----------------------------------
Total database time was 28953 seconds.
The average number of active sessions was .64.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------------------ ------------------- ---------------
1 Top SQL
Statements .61 | 94.17 3
2 "User
I/O" wait Class .1 | 15.38 0
3 Undersized SGA .04 | 6.48 1
4 PGA_AGGREGATE_LIMIT
Throttling .03 | 3.89 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and
Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .61 active sessions, 94.17% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were
found. These
statements offer a good opportunity for performance
improvement.
Recommendation 1:
SQL Tuning
Estimated benefit
is .56 active sessions, 86.41% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning
Advisor on the SELECT statement with SQL_ID
"9w52tbp4nqgtt".
Related Object
SQL statement
with SQL_ID 9w52tbp4nqgtt.
<Example only, SQL Text Removed>
Rationale
The SQL spent
100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database time
for this SQL was divided as follows: 100% for SQL
execution, 0%
for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement
with SQL_ID "9w52tbp4nqgtt" was executed 58158 times and
had an average
elapsed time of 0.31 seconds.
Rationale
Top level calls
to execute the PL/SQL statement with SQL_ID
"6mcpb06rctk0x" are responsible for 100% of the database time
spent on
the SELECT
statement with SQL_ID "9w52tbp4nqgtt".
Related Object
SQL statement
with SQL_ID 6mcpb06rctk0x.
<Example
only, SQL Text Removed>
Recommendation 2:
SQL Tuning
Estimated benefit
is .03 active sessions, 5.12% of total activity.
------------------------------------------------------------------
Action
Investigate the
INSERT statement with SQL_ID "5hwv4fyrrak98" for
possible
performance improvements. You can supplement the information
given here with
an ASH report for this SQL_ID.
Related Object
SQL statement
with SQL_ID 5hwv4fyrrak98.
<Example
only, SQL Text Removed>
Rationale
The SQL spent
only 28% of its database time on CPU, I/O and Cluster
waits.
Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance
data for the SQL to find potential improvements.
Rationale
Database time
for this SQL was divided as follows: 100% for SQL
execution, 0%
for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement
with SQL_ID "5hwv4fyrrak98" was executed 77 times and had
an average
elapsed time of 13 seconds.
Rationale
Waiting for
event "acknowledge over PGA limit" in wait class
"Scheduler"
accounted for
71% of the database time spent in processing the SQL
statement with
SQL_ID "5hwv4fyrrak98".
Rationale
Top level calls
to execute the PL/SQL statement with SQL_ID
"b6usrg82hwsa3" are responsible for 100% of the database time
spent on
the INSERT
statement with SQL_ID "5hwv4fyrrak98".
Related Object
SQL statement
with SQL_ID b6usrg82hwsa3.
<Example
only, SQL Text Removed>
Recommendation 3:
SQL Tuning
Estimated benefit
is .02 active sessions, 2.63% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning
Advisor on the SELECT statement with SQL_ID
"34cd4y8mbqvsk".
Related Object
SQL statement
with SQL_ID 34cd4y8mbqvsk.
<Example only, SQL Text
Removed>
Action
Use bigger fetch
arrays while fetching results from the SELECT statement
with SQL_ID
"34cd4y8mbqvsk".
Related Object
SQL statement
with SQL_ID 34cd4y8mbqvsk.
<Example only, SQL Text Removed
Rationale
The SQL spent
100% of its database time on CPU, I/O and Cluster waits.
This part of
database time may be improved by the SQL Tuning Advisor.
Rationale
Database time
for this SQL was divided as follows: 100% for SQL
execution, 0%
for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement
with SQL_ID "34cd4y8mbqvsk" was executed 32322 times and
had an average
elapsed time of 0.016 seconds.
Rationale
At least 4
distinct execution plans were utilized for this SQL statement
during the
analysis period.
Rationale
Top level calls
to execute the PL/SQL statement with SQL_ID
"6mcpb06rctk0x" are responsible for 100% of the database time
spent on
the SELECT
statement with SQL_ID "34cd4y8mbqvsk".
Related Object
SQL statement
with SQL_ID 6mcpb06rctk0x.
<Example
only, SQL Text Removed>
Finding 2: "User I/O" wait Class
Impact is .1 active sessions, 15.38% of total activity.
-------------------------------------------------------
Wait class "User I/O" was consuming significant
database time.
Waits for I/O to temporary tablespaces were not consuming
significant database
time.
The throughput of the I/O subsystem was not significantly
lower than expected.
No recommendations
are available.
Finding 3: Undersized SGA
Impact is .04 active sessions, 6.48% of total activity.
-------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or
hard parses.
The value of parameter "sga_target"
was "4096 M" during the analysis period.
Recommendation 1:
Database Configuration
Estimated benefit
is .01 active sessions, 2.01% of total activity.
------------------------------------------------------------------
Action
Increase the
size of the SGA by setting the parameter "sga_target"
to
4608 M.
Symptoms That Led
to the Finding:
---------------------------------
Wait class
"User I/O" was consuming significant database time.
Impact is .1
active sessions, 15.38% of total activity.
Finding 4: PGA_AGGREGATE_LIMIT Throttling
Impact is .03 active sessions, 3.89% of total activity.
-------------------------------------------------------
Database sessions were consuming significant PGA memory in
some database
instances (or PDBs).
Recommendation 1:
Database Configuration
Estimated benefit
is .03 active sessions, 3.89% of total activity.
------------------------------------------------------------------
Action
Sessions were
consuming significant PGA memory. Consider increasing the
PGA limit for
these sessions by increasing the value of the parameter
PGA_AGGREGATE_LIMIT.
Symptoms That Led
to the Finding:
---------------------------------
Wait class
"Scheduler" was consuming significant database time.
Impact is .03
active sessions, 3.89% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming
significant database time.
Wait class "Commit" was not consuming significant
database time.
Wait class "Concurrency" was not consuming
significant database time.
Wait class "Configuration" was not consuming
significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant
database time.
Session connect and disconnect calls were not consuming
significant database
time.
Hard parsing of SQL statements was not consuming significant
database time.
The database's maintenance windows were active during 100%
of the analysis
period.
End of Report
Report written to addmrpt_1_71834_71859.txt