ADDM Report for Task 'TASK_36563'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 36559 to 36560.
Time period starts at 26-JAN-17 12.00.40 PM
Time period ends at 26-JAN-17 01.00.51 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 30496 seconds.
The average number of active sessions was 8.45.
Summary of Findings
-------------------
Description Active Sessions Recommendation
s
Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Virtual Memory Paging 8.45 | 100 1
2 Top Segments by "User I/O" and "Cluster" 4.67 | 55.32 5
3 Top SQL Statements 3.85 | 45.6 5
4 Undersized instance memory 3.26 | 38.56 1
5 Commits and Rollbacks .48 | 5.74 2
6 PL/SQL Execution .2 | 2.31 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Virtual Memory Paging
Impact is 8.45 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.45 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.67 active sessions, 55.32% of total activity.
---------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is 2.22 active sessions, 26.32% of total activity.
--------------------------------------------------------------------
Action
Investigate application logic involving I/O on LOB
"MBLLIVE.SYS_LOB0001355245C00003$$" with object ID 1355246.
Related Object
Database object with ID 1355246.
Rationale
The I/O usage statistics for the object are: 0 full object scans,
1182052 physical reads, 0 physical writes and 0 direct reads.
Recommendation 2: Segment Tuning
Estimated benefit is 1.62 active sessions, 19.16% of total activity.
--------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE
"MBLLIVE.FBNK_EB_C004" with object ID 1355245.
Related Object
Database object with ID 1355245.
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 "25bqrc9pvapz9" 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,
1094704 physical reads, 1 physical writes and 0 direct reads.
Recommendation 3: Segment Tuning
Estimated benefit is .42 active sessions, 5.02% 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.
Recommendation 4: Segment Tuning
Estimated benefit is .21 active sessions, 2.51% of total activity.
------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE PARTITION
"MBLLIVE.FBNK_ACCOUNT.FBNK_ACCOUNT_P3" with object ID 1345836.
Related Object
Database object with ID 1345836.
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 "1zh2ms1aymubn" 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, 155859
physical reads, 0 physical writes and 0 direct reads.
Recommendation 5: Segment Tuning
Estimated benefit is .2 active sessions, 2.31% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE PARTITION
"MBLLIVE.FBNK_ACCOUNT.FBNK_ACCOUNT_P4" with object ID 1345837.
Related Object
Database object with ID 1345837.
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 "1zh2ms1aymubn" 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, 156154
physical reads, 0 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.66 active sessions, 67.01% of total activity.
Finding 3: Top SQL Statements
Impact is 3.85 active sessions, 45.6% 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 1.71 active sessions, 20.29% of total activity.
--------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"25bqrc9pvapz9".
Related Object
SQL statement with SQL_ID 25bqrc9pvapz9.
SELECT t.XMLRECORD.getClobVal() FROM FBNK_EB_C004 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 "25bqrc9pvapz9" was executed 2151304 times and
had an average elapsed time of 0.0027 seconds.
Rationale
I/O and Cluster wait for TABLE "MBLLIVE.FBNK_EB_C004" with object ID
1355245 consumed 95% of the database time spent on this SQL statement.
Recommendation 2: SQL Tuning
Estimated benefit is 1.07 active sessions, 12.65% 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 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: 90% for SQL
execution, 0% for parsing, 10% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "1zh2ms1aymubn" was executed 2866148 times and
had an average elapsed time of 0.0013 seconds.
Rationale
I/O and Cluster wait for TABLE PARTITION
"MBLLIVE.FBNK_ACCOUNT.FBNK_ACCOUNT_P3" with object ID 1345836 consumed
19% of the database time spent on this SQL statement.
Rationale
I/O and Cluster wait for TABLE PARTITION
"MBLLIVE.FBNK_ACCOUNT.FBNK_ACCOUNT_P4" with object ID 1345837 consumed
18% of the database time spent on this SQL statement.
Recommendation 3: SQL Tuning
Estimated benefit is .45 active sessions, 5.29% 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 799363 times and
had an average elapsed time of 0.0018 seconds.
Rationale
I/O and Cluster wait for TABLE "MBLLIVE.FBNK_AZ_ACCOUNT" with object ID
1353739 consumed 96% of the database time spent on this SQL statement.
Recommendation 4: SQL Tuning
Estimated benefit is .44 active sessions, 5.26% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"b68fqx3xanxc2".
Related Object
SQL statement with SQL_ID b68fqx3xanxc2.
SELECT t.RECID,t.XMLRECORD.getClobVal() FROM FBNK_ACCOUNT t WHERE
NVL(EXTRACTVALUE(t.XMLRECORD,:"SYS_B_0"),:"SYS_B_1")=:"SYS_B_2"
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: 85% for SQL
execution, 0% for parsing, 15% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "b68fqx3xanxc2" was executed 110 times and had
an average elapsed time of 4.9 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .18 active sessions, 2.11% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"b8y6vz1jybj1c".
Related Object
SQL statement with SQL_ID b8y6vz1jybj1c.
SELECT IE.COLUMN_EXPRESSION AS COL_NAME FROM USER_IND_EXPRESSIONS IE,
USER_INDEXES I WHERE I.INDEX_NAME = IE.INDEX_NAME AND I.ITYP_NAME IS
NULL AND I.TABLE_NAME = UPPER(:B1 )
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 "b8y6vz1jybj1c" was executed 993 times and had
an average elapsed time of 0.42 seconds.
Rationale
At least 3 distinct execution plans were utilized for this SQL statement
during the analysis period.
Finding 4: Undersized instance memory
Impact is 3.26 active sessions, 38.56% 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 3.26 active sessions, 38.56% 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 5.66 active sessions, 67.01% of total activity.
Finding 5: Commits and Rollbacks
Impact is .48 active sessions, 5.74% 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 .48 active sessions, 5.74% 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 13466 transactions per minute with an
average redo size of 7463 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is .48 active sessions, 5.74% 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 14 K and the
average time per write was 1 milliseconds.
Rationale
The total I/O throughput on redo log files was 1.6 M per second for
reads and 3.3 M per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 66% by Log Writer, 0% by Archiver, 0% by Streams AQ and 33% by
all other activity.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .48 active sessions, 5.74% of total activity.
Finding 6: PL/SQL Execution
Impact is .2 active sessions, 2.31% of total activity.
------------------------------------------------------
PL/SQL execution consumed significant database time.
Recommendation 1: SQL Tuning
Estimated benefit is .18 active sessions, 2.14% of total activity.
------------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.XMLTYPE.GETCLOBVAL#1" of type "TYPE"
and ID 5029. Refer to the PL/SQL documentation for addition information.
Rationale
653 seconds spent in executing PL/SQL "SYS.XMLTYPE.GETCLOBVAL#1" of type
"TYPE" and ID 5029.
Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, .17% of total activity.
-----------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.XMLTYPE.XMLTYPE#4" of type "TYPE" and
ID 5029. Refer to the PL/SQL documentation for addition information.
Rationale
51 seconds spent in executing PL/SQL "SYS.XMLTYPE.XMLTYPE#4" of type
"TYPE" and ID 5029.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" 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.
