ADDM Report for Task 'TASK_36564'

---------------------------------

Analysis Period
---------------
AWR snapshot range from 36560 to 36561.
Time period starts at 26-JAN-17 01.00.51 PM
Time period ends at 26-JAN-17 02.00.58 PM

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 32150 seconds.
The average number of active sessions was 8.91.

Summary of Findings
-------------------
Description Active Sessions Recommendation
s
Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Virtual Memory Paging 8.91 | 100 1
2 Top Segments by "User I/O" and "Cluster" 4.29 | 48.07 2
3 Top SQL Statements 2.65 | 29.69 5
4 High Watermark Waits 1.03 | 11.53 4
5 Commits and Rollbacks .29 | 3.25 2
6 Unusual "Network" Wait Event .2 | 2.21 3

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Findings and Recommendations

----------------------------

Finding 1: Virtual Memory Paging

Impact is 8.92 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.91 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 4.29 active sessions, 48.07% 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.85 active sessions, 43.2% 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,
1493931 physical reads, 1 physical writes and 0 direct reads.

Recommendation 2: Segment Tuning

Estimated benefit is .43 active sessions, 4.87% 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 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 0 full object scans, 540698
physical reads, 1324 physical writes and 0 direct reads.

Symptoms That Led to the Finding:

---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 5.28 active sessions, 59.2% of total activity.

Finding 3: Top SQL Statements

Impact is 2.65 active sessions, 29.69% 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 .71 active sessions, 7.92% of total activity.
------------------------------------------------------------------
Action
Investigate the UPSERT statement with SQL_ID "bfha7fmd9d5s6" 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 bfha7fmd9d5s6.
MERGE INTO FBNK_PD_BALANCES 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 4% 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 "bfha7fmd9d5s6" was executed 25314 times and
had an average elapsed time of 0.086 seconds.
Rationale
Waiting for event "enq: HW - contention" in wait class "Configuration"
accounted for 95% of the database time spent in processing the SQL
statement with SQL_ID "bfha7fmd9d5s6".

Recommendation 2: SQL Tuning

Estimated benefit is .63 active sessions, 7.1% 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 98% 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: 84% for SQL
execution, 0% for parsing, 16% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "1zh2ms1aymubn" was executed 2754562 times and
had an average elapsed time of 0.0009 seconds.

Recommendation 3: SQL Tuning

Estimated benefit is .56 active sessions, 6.33% of total activity.
------------------------------------------------------------------
Action
Investigate the UPSERT statement with SQL_ID "a6v0fafywx7ty" 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 a6v0fafywx7ty.
MERGE INTO F_JOB_LIST_9 USING DUAL ON (RECID = :RECID)
WHEN MATCHED THEN UPDATE SET XMLRECORD=:XMLRECORD
WHEN NOT MATCHED THEN INSERT VALUES(:RECID, :XMLRECORD)
Rationale
The SQL spent only 4% 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 "a6v0fafywx7ty" was executed 206059 times and
had an average elapsed time of 0.0086 seconds.
Rationale
Waiting for event "enq: HW - contention" in wait class "Configuration"
accounted for 95% of the database time spent in processing the SQL
statement with SQL_ID "a6v0fafywx7ty".

Recommendation 4: SQL Tuning

Estimated benefit is .5 active sessions, 5.6% 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 3203323 times and
had an average elapsed time of 0.00052 seconds.
Rationale
I/O and Cluster wait for TABLE "MBLLIVE.FBNK_AZ_ACCOUNT" with object ID
1353739 consumed 88% of the database time spent on this SQL statement.

Recommendation 5: SQL Tuning

Estimated benefit is .23 active sessions, 2.61% of total activity.
------------------------------------------------------------------
Action
Investigate the SELECT statement with SQL_ID "92315tc01mfrh" 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 92315tc01mfrh.
SELECT RECID, t.XMLRECORD.GetClobVal() FROM F_EB_JOURNAL_SUMMARY t
Rationale
The SQL spent only 9% 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 "92315tc01mfrh" was executed 250 times and had
an average elapsed time of 2.8 seconds.
Rationale
Waiting for event "SQL*Net more data to client" in wait class "Network"
accounted for 90% of the database time spent in processing the SQL
statement with SQL_ID "92315tc01mfrh".
Finding 4: High Watermark Waits
Impact is 1.03 active sessions, 11.53% 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, .35% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the INDEX "MBLLIVE.LOBI_F_JOB_LIST_9" with object
ID 2272686 in a manner that will evenly distribute concurrent DML across
multiple partitions.
Related Object
Database object with ID 2272686.

Recommendation 2: Schema Changes

Estimated benefit is .03 active sessions, .32% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the TABLE "MBLLIVE.F_JOB_LIST_9" with object ID
2272684 in a manner that will evenly distribute concurrent DML across
multiple partitions.
Related Object
Database object with ID 2272684.

Recommendation 3: Schema Changes

Estimated benefit is .02 active sessions, .22% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the LOB "MBLLIVE.LOB_F_JOB_LIST_9" with object ID
2272685 in a manner that will evenly distribute concurrent DML across
multiple partitions.
Related Object
Database object with ID 2272685.

Recommendation 4: Schema Changes

Estimated benefit is .01 active sessions, .16% of total activity.
-----------------------------------------------------------------
Action
Consider partitioning the INDEX "MBLLIVE.SYS_IL0001357612C00003$$" with
object ID 1357614 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 1357614.

Symptoms That Led to the Finding:

---------------------------------
Wait class "Configuration" was consuming significant database time.
Impact is 1.03 active sessions, 11.54% of total activity.

Finding 5: Commits and Rollbacks

Impact is .29 active sessions, 3.25% 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 .29 active sessions, 3.25% 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 7247 transactions per minute with an
average redo size of 7477 bytes per transaction.

Recommendation 2: Host Configuration

Estimated benefit is .29 active sessions, 3.25% 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 10 K and the
average time per write was 0 milliseconds.
Rationale
The total I/O throughput on redo log files was 972 K per second for
reads and 1.8 M per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 65% by Log Writer, 0% by Archiver, 0% by Streams AQ and 34% by
all other activity.

Symptoms That Led to the Finding:

---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .29 active sessions, 3.25% of total activity.

Finding 6: Unusual "Network" Wait Event

Impact is .2 active sessions, 2.21% of total activity.
------------------------------------------------------
Wait event "SQL*Net more data to client" in wait class "Network" was consuming
significant database time.

Recommendation 1: Application Analysis

Estimated benefit is .2 active sessions, 2.21% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net more data to client" waits.
Refer to Oracle's "Database Reference" for the description of this wait
event.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant time on the "SQL*Net more data to client" wait event. For
example, the SELECT statement with SQL_ID "92315tc01mfrh" is responsible
for 100% of these waits.

Recommendation 2: Application Analysis

Estimated benefit is .2 active sessions, 2.21% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net more data to client" waits in
Module "jsh@MBLDRAPP (TNS V1-V3)".

Recommendation 3: Application Analysis

 Estimated benefit is .2 active sessions, 2.21% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net more data to client" waits in
Service "mblprod".

Symptoms That Led to the Finding:

---------------------------------
Wait class "Network" was consuming significant database time.
Impact is .22 active sessions, 2.46% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.