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
No comments:
Post a Comment