ADDM Report for Task 'TASK_36561'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 36557 to 36558.
Time period starts at 26-JAN-17 10.00.26 AM
Time period ends at 26-JAN-17 11.00.30 AM
Analysis Target
---------------
Database 'MBLPROD' with DB ID 359300053.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance mblprod, numbered 1 and hosted at
MBLDRDB.
Activity During the Analysis Period
-----------------------------------
Total database time was 30945 seconds.
The average number of active sessions was 8.59.
Summary of Findings
-------------------
Description Active Sessions Recommendation
s
Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Virtual Memory Paging 8.59 | 100 1
2 Top Segments by "User I/O" and "Cluster" 3.94 | 45.84 3
3 Top SQL Statements 3.66 | 42.66 3
4 High Watermark Waits 2.76 | 32.15 2
5 Undersized instance memory .28 | 3.23 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging
Impact is 8.59 active sessions, 100% of total activity.
-------------------------------------------------------
Significant virtual memory paging was detected on the host operating system.
Recommendation 1: Host Configuration
Estimated benefit is 8.59 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.
Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is 3.94 active sessions, 45.84% of total activity.
---------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is 3.38 active sessions, 39.39% of total activity.
--------------------------------------------------------------------
Action
Investigate application logic involving I/O on LOB
"MBLLIVE.SYS_LOB0001353805C00003$$" with object ID 1353806.
Related Object
Database object with ID 1353806.
Rationale
The I/O usage statistics for the object are: 0 full object scans,
1566390 physical reads, 81850 physical writes and 0 direct reads.
Recommendation 2: Segment Tuning
Estimated benefit is .33 active sessions, 3.8% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic involving I/O on LOB
"MBLLIVE.SYS_LOB0001353739C00003$$" with object ID 1353740.
Related Object
Database object with ID 1353740.
Rationale
The I/O usage statistics for the object are: 0 full object scans, 257968
physical reads, 7378 physical writes and 0 direct reads.
Recommendation 3: Segment Tuning
Estimated benefit is .23 active sessions, 2.66% of total activity.
------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE
"MBLLIVE.FBNK_AZ_ACCOUNT" with object ID 1353739.
Related Object
Database object with ID 1353739.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the SELECT statement with
SQL_ID "2zb8b1vzm0qzp" is responsible for 81% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 0 full object scans, 418726
physical reads, 13621 physical writes and 0 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 4.89 active sessions, 57% of total activity.
Finding 3: Top SQL Statements
Impact is 3.66 active sessions, 42.66% 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 2.93 active sessions, 34.18% of total activity.
--------------------------------------------------------------------
Action
Investigate the UPSERT statement with SQL_ID "18f4sdvytdmy5" 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 18f4sdvytdmy5.
MERGE INTO FBNK_AZ_SCHEDULES USING DUAL ON (RECID = :RECID)
WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1,
1)
WHEN NOT MATCHED THEN INSERT VALUES(:RECID, XMLTYPE(:XMLRECORD, NULL,
1, 1))
Rationale
The SQL spent only 5% 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 "18f4sdvytdmy5" was executed 15229 times and
had an average elapsed time of 0.69 seconds.
Rationale
Waiting for event "enq: HW - contention" in wait class "Configuration"
accounted for 94% of the database time spent in processing the SQL
statement with SQL_ID "18f4sdvytdmy5".
Recommendation 2: SQL Tuning
Estimated benefit is .49 active sessions, 5.76% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"1zh2ms1aymubn".
Related Object
SQL statement with SQL_ID 1zh2ms1aymubn.
SELECT t.XMLRECORD.getClobVal() FROM FBNK_ACCOUNT t WHERE RECID
=:RECID
Rationale
The SQL spent 97% 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: 92% for SQL
execution, 0% for parsing, 8% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "1zh2ms1aymubn" was executed 1380555 times and
had an average elapsed time of 0.0012 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .22 active sessions, 2.57% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"2zb8b1vzm0qzp".
Related Object
SQL statement with SQL_ID 2zb8b1vzm0qzp.
SELECT t.XMLRECORD.getClobVal() FROM FBNK_AZ_ACCOUNT t WHERE RECID
=:RECID
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 "2zb8b1vzm0qzp" was executed 2397260 times and
had an average elapsed time of 0.00035 seconds.
Rationale
I/O and Cluster wait for TABLE "MBLLIVE.FBNK_AZ_ACCOUNT" with object ID
1353739 consumed 84% of the database time spent on this SQL statement.
Finding 4: High Watermark Waits
Impact is 2.76 active sessions, 32.15% of total activity.
---------------------------------------------------------
Contention on the high watermark (HW) enqueue was consuming significant
database time.
Recommendation 1: Schema Changes
Estimated benefit is .03 active sessions, .39% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the LOB "MBLLIVE.SYS_LOB0001353805C00003$$" with
object ID 1353806 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 1353806.
Recommendation 2: Schema Changes
Estimated benefit is .02 active sessions, .23% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the INDEX "MBLLIVE.SYS_IL0001353805C00003$$" with
object ID 1353807 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 1353807.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Configuration" was consuming significant database time.
Impact is 2.76 active sessions, 32.16% of total activity.
Finding 5: Undersized instance memory
Impact is .28 active sessions, 3.23% of total activity.
-------------------------------------------------------
The Oracle instance memory (SGA and PGA) was inadequately sized, causing
additional I/O and CPU usage.
The value of parameter "memory_target" was "14336 M" during the analysis
period.
Recommendation 1: Database Configuration
Estimated benefit is .28 active sessions, 3.23% of total activity.
------------------------------------------------------------------
Action
Increase memory allocated to the instance by setting the parameter
"memory_target" to 16128 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 4.89 active sessions, 57% 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.
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.
