ADDM Report for Task 'TASK_12779' --------------------------------- Analysis Period --------------- AWR snapshot range from 4675 to 4676. Time period starts at 26-MAY-23 04.00.22 PM Time period ends at 26-MAY-23 05.00.32 PM Analysis Target --------------- Database 'YYDSDB' with DB ID 295871789. Database version 19.0.0.0.0. ADDM performed an analysis of instance yydsdb1, numbered 1 and hosted at yyds01. ADDM detected that the database type is MULTITENANT DB. Activity During the Analysis Period ----------------------------------- Total database time was 38644 seconds. The average number of active sessions was 10.7. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity ----------------------------- ------------------- --------------- 1 Top SQL Statements 8.63 | 80.64 5 2 Undersized PGA 6.49 | 60.66 0 3 Unusual "User I/O" Wait Event .51 | 4.72 4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is 8.63 active sessions, 80.64% 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 6.67 active sessions, 62.27% of total activity. -------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "47h5f27tkdadh". Related Object SQL statement with SQL_ID 47h5f27tkdadh. /*NDTM*/ WITH SQLAREA_PLAN_HASH AS( SELECT DISTINCT INST_ID,SQL_ID,PLAN_HASH_VALUE FROM (SELECT INST_ID,SQL_ID,PLAN_HASH_VALUE, row_number() over (partition by inst_ID order by ELAPSED_TIME desc nulls last) rn1, row_number() over (partition by inst_ID order by ROUND((ELAPSED_TIME / 1000) / DECODE(EXECUTIONS, 0, 1, EXECUTIONS), 0) desc nulls last) rn2 FROM GV$SQLAREA_PLAN_HASH WHERE LAST_ACTIVE_TIME >= sysdate - 6/(24*60) AND PARSING_SCHEMA_NAME NOT IN ('SYSMAN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS' ,'APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','XDB','O RDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MGMT_VIEW','SYS', 'SYSTEM','OUTLN','DIP','ORACLE_OCM','XS$NULL','SQLTXPLAIN')) WHERE RN1 <=60 OR RN2 <=60 ) SELECT VSPH.INST_ID, VSPH.SQL_ID, SUBSTR(VSPH.SQL_TEXT, 0, 50) AS SQL_TEXT_BRIEF, VSPH.SQL_FULLTEXT, VSPH.SHARABLE_MEM, VSPH.PERSISTENT_MEM, VSPH.RUNTIME_MEM, VSPH.SORTS, VSPH.VERSION_COUNT, VSPH.LOADED_VERSIONS, VSPH.OPEN_VERSIONS, VSPH.USERS_OPENING, VSPH.FETCHES, VSPH.EXECUTIONS, VSPH.PX_SERVERS_EXECUTIONS, VSPH.END_OF_FETCH_COUNT, VSPH.USERS_EXECUTING, VSPH.LOADS, VSPH.FIRST_LOAD_TIME, VSPH.INVALIDATIONS, VSPH.PARSE_CALLS, VSPH.DISK_READS, VSPH.DIRECT_WRITES, VSPH.BUFFER_GETS, VSPH.APPLICATION_WAIT_TIME / 1000 AS APPLICATION_WAIT_TIME, VSPH.CONCURRENCY_WAIT_TIME / 1000 AS CONCURRENCY_WAIT_TIME, VSPH.CLUSTER_WAIT_TIME / 1000 AS CLUSTER_WAIT_TIME, VSPH.USER_IO_WAIT_TIME / 1000 AS USER_IO_WAIT_TIME, VSPH.PLSQL_EXEC_TIME / 1000 AS PLSQL_EXEC_TIME, VSPH.JAVA_EXEC_TIME / 1000 AS JAVA_EXEC_TIME, VSPH.ROWS_PROCESSED, VSPH.COMMAND_TYPE, VSPH.OPTIMIZER_MODE, VSPH.OPTIMIZER_COST, VSPH.OPTIMIZER_ENV_HASH_VALUE, VSPH.PARSING_USER_ID, VSPH.PARSING_SCHEMA_ID, VSPH.PARSING_SCHEMA_NAME, VSPH.KEPT_VERSIONS, VSPH.HASH_VALUE, VSPH.PLAN_HASH_VALUE, VSPH.MODULE, VSPH.ACTION, VSPH.SERIALIZABLE_ABORTS, VSPH.OUTLINE_CATEGORY, VSPH.CPU_TIME / 1000 AS CPU_TIME, VSPH.ELAPSED_TIME / 1000 AS ELAPSED_TIME, VSPH.OUTLINE_SID, VSPH.REMOTE, VSPH.OBJECT_STATUS, VSPH.LITERAL_HASH_VALUE, VSPH.LAST_LOAD_TIME, VSPH.SQL_PROFILE, VSPH.PROGRAM_ID, VSPH.PROGRAM_LINE# AS PROGRAM_LINE, VSPH.LAST_ACTIVE_TIME, VSPH.TYPECHECK_MEM, VSPH.IO_CELL_OFFLOAD_ELIGIBLE_BYTES, VSPH.IO_INTERCONNECT_BYTES, VSPH.PHYSICAL_READ_REQUESTS, VSPH.PHYSICAL_READ_BYTES, VSPH.PHYSICAL_WRITE_REQUESTS, VSPH.PHYSICAL_WRITE_BYTES, VSPH.OPTIMIZED_PHY_READ_REQUESTS, VSPH.IO_CELL_UNCOMPRESSED_BYTES, VSPH.IO_CELL_OFFLOAD_RETURNED_BYTES FROM GV$SQLAREA_PLAN_HASH VSPH,SQLAREA_PLAN_HASH SPH WHERE VSPH.INST_ID=SPH.INST_ID AND VSPH.SQL_ID=SPH.SQL_ID AND VSPH.PLAN_HASH_VALUE=SPH.PLAN_HASH_VALUE Rationale The SQL statement executed in the root container. 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 "47h5f27tkdadh" was executed 9 times and had an average elapsed time of 2550 seconds. Rationale At least one execution of the statement ran in parallel. Recommendation 2: SQL Tuning Estimated benefit is .99 active sessions, 9.25% of total activity. ------------------------------------------------------------------ Action Investigate the SELECT statement with SQL_ID "9kzgjkryyctrm" 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 9kzgjkryyctrm. /*NDTM*/ SELECT D.INST_ID, D.NAME NAME, F.PHYRDS, F.PHYBLKRD, F.PHYWRTS, F.PHYBLKWRT, F.READTIM * 10 as READTIM, F.WRITETIM * 10 as WRITETIM FROM GV$FILESTAT F, GV$DATAFILE D WHERE F.FILE# = D.FILE# AND F.INST_ID = D.INST_ID ORDER BY F.PHYRDS DESC, F.PHYWRTS DESC Rationale The SQL statement executed in the root container. Rationale The SQL spent only 17% 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 "9kzgjkryyctrm" was executed 88 times and had an average elapsed time of 38 seconds. Rationale At least one execution of the statement ran in parallel. Rationale At least 2 distinct execution plans were utilized for this SQL statement during the analysis period. Rationale Waiting for event "control file sequential read" in wait class "System I/O" accounted for 82% of the database time spent in processing the SQL statement with SQL_ID "9kzgjkryyctrm". Recommendation 3: SQL Tuning Estimated benefit is .4 active sessions, 3.78% of total activity. ----------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "47h5f27tkdadh". Related Object SQL statement with SQL_ID 47h5f27tkdadh. /*NDTM*/ WITH SQLAREA_PLAN_HASH AS( SELECT DISTINCT INST_ID,SQL_ID,PLAN_HASH_VALUE FROM (SELECT INST_ID,SQL_ID,PLAN_HASH_VALUE, row_number() over (partition by inst_ID order by ELAPSED_TIME desc nulls last) rn1, row_number() over (partition by inst_ID order by ROUND((ELAPSED_TIME / 1000) / DECODE(EXECUTIONS, 0, 1, EXECUTIONS), 0) desc nulls last) rn2 FROM GV$SQLAREA_PLAN_HASH WHERE LAST_ACTIVE_TIME >= sysdate - 6/(24*60) AND PARSING_SCHEMA_NAME NOT IN ('SYSMAN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS' ,'APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','XDB','O RDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MGMT_VIEW','SYS', 'SYSTEM','OUTLN','DIP','ORACLE_OCM','XS$NULL','SQLTXPLAIN')) WHERE RN1 <=60 OR RN2 <=60 ) SELECT VSPH.INST_ID, VSPH.SQL_ID, SUBSTR(VSPH.SQL_TEXT, 0, 50) AS SQL_TEXT_BRIEF, VSPH.SQL_FULLTEXT, VSPH.SHARABLE_MEM, VSPH.PERSISTENT_MEM, VSPH.RUNTIME_MEM, VSPH.SORTS, VSPH.VERSION_COUNT, VSPH.LOADED_VERSIONS, VSPH.OPEN_VERSIONS, VSPH.USERS_OPENING, VSPH.FETCHES, VSPH.EXECUTIONS, VSPH.PX_SERVERS_EXECUTIONS, VSPH.END_OF_FETCH_COUNT, VSPH.USERS_EXECUTING, VSPH.LOADS, VSPH.FIRST_LOAD_TIME, VSPH.INVALIDATIONS, VSPH.PARSE_CALLS, VSPH.DISK_READS, VSPH.DIRECT_WRITES, VSPH.BUFFER_GETS, VSPH.APPLICATION_WAIT_TIME / 1000 AS APPLICATION_WAIT_TIME, VSPH.CONCURRENCY_WAIT_TIME / 1000 AS CONCURRENCY_WAIT_TIME, VSPH.CLUSTER_WAIT_TIME / 1000 AS CLUSTER_WAIT_TIME, VSPH.USER_IO_WAIT_TIME / 1000 AS USER_IO_WAIT_TIME, VSPH.PLSQL_EXEC_TIME / 1000 AS PLSQL_EXEC_TIME, VSPH.JAVA_EXEC_TIME / 1000 AS JAVA_EXEC_TIME, VSPH.ROWS_PROCESSED, VSPH.COMMAND_TYPE, VSPH.OPTIMIZER_MODE, VSPH.OPTIMIZER_COST, VSPH.OPTIMIZER_ENV_HASH_VALUE, VSPH.PARSING_USER_ID, VSPH.PARSING_SCHEMA_ID, VSPH.PARSING_SCHEMA_NAME, VSPH.KEPT_VERSIONS, VSPH.HASH_VALUE, VSPH.PLAN_HASH_VALUE, VSPH.MODULE, VSPH.ACTION, VSPH.SERIALIZABLE_ABORTS, VSPH.OUTLINE_CATEGORY, VSPH.CPU_TIME / 1000 AS CPU_TIME, VSPH.ELAPSED_TIME / 1000 AS ELAPSED_TIME, VSPH.OUTLINE_SID, VSPH.REMOTE, VSPH.OBJECT_STATUS, VSPH.LITERAL_HASH_VALUE, VSPH.LAST_LOAD_TIME, VSPH.SQL_PROFILE, VSPH.PROGRAM_ID, VSPH.PROGRAM_LINE# AS PROGRAM_LINE, VSPH.LAST_ACTIVE_TIME, VSPH.TYPECHECK_MEM, VSPH.IO_CELL_OFFLOAD_ELIGIBLE_BYTES, VSPH.IO_INTERCONNECT_BYTES, VSPH.PHYSICAL_READ_REQUESTS, VSPH.PHYSICAL_READ_BYTES, VSPH.PHYSICAL_WRITE_REQUESTS, VSPH.PHYSICAL_WRITE_BYTES, VSPH.OPTIMIZED_PHY_READ_REQUESTS, VSPH.IO_CELL_UNCOMPRESSED_BYTES, VSPH.IO_CELL_OFFLOAD_RETURNED_BYTES FROM GV$SQLAREA_PLAN_HASH VSPH,SQLAREA_PLAN_HASH SPH WHERE VSPH.INST_ID=SPH.INST_ID AND VSPH.SQL_ID=SPH.SQL_ID AND VSPH.PLAN_HASH_VALUE=SPH.PLAN_HASH_VALUE Rationale The SQL statement executed in container YYDSPDB with database ID 1730301391. 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 "47h5f27tkdadh" was executed 3 times and had an average elapsed time of 400 seconds. Rationale At least one execution of the statement ran in parallel. Recommendation 4: SQL Tuning Estimated benefit is .34 active sessions, 3.14% of total activity. ------------------------------------------------------------------ Action Investigate the SELECT statement with SQL_ID "196mqnmxgxpv1" 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 196mqnmxgxpv1. select sysStat.branchnodesplits , sysStat.consistentreadchanges , sysStat.consistentreadgets , sysStat.crblks , sysStat.crundorecs , sysStat.dbblkchanges , sysStat.dbblkgets , sysStat.dbwrcheckpoints , sysStat.sortsdisk , sysStat.enqdeadlocks , sysStat.enqreqs , sysStat.enqtimeouts , sysStat.enqwaits , sysStat.indxscansfull , sysStat.indxscansfullrowid , sysStat.indxscansfulldirectread , sysStat.executions , sysStat.totalPhysicalReads , sysStat.totalPhysicalWrites , sysStat.phyreadtotalioreqs , sysStat.phywritetotalioreqs , sysStat.leafnodesplits , sysStat.logreads , sysStat.logons , sysStat.tabscanslong , sysStat.bytestoclientviasqlnet , sysStat.bytesfromclientviasqlnet , sysStat.bytestodblinkviasqlnet , sysStat.bytesfromdblinkviasqlnet , sysStat.bytestoclientviasqlnetvec , sysStat.bytesfromclientviasqlnetvec , sysStat.bytestodblinkviasqlnetvec , sysStat.bytesfromdblinkviasqlnetvec , sysStat.opncurs , sysStat.failedparses , sysStat.physreadslob , sysStat.physwriteslob , sysStat.physreadsdir , sysStat.physwritesdir , sysStat.physicalreads , sysStat.physicalwrites , sysStat.redosize , sysStat.redowrites , sysStat.sortsmemory , sysStat.sortsrows , sysStat.parses , sysStat.hardparses , sysStat.indxscanstotal , sysStat.usercalls , sysStat.recurscalls , sysStat.commits , sysStat.rollbacks , sysStat.userrollbackundorec , sysStat.bgcheckpoints , sysTM.dbCpu , sysTM.dbTime , sysTM.active_session , iostat.smallsyncreadreqs , iostat.smallsyncreadlatency from ( select sum(decode(name, 'branch node splits', value, 0)) branchnodesplits , sum(decode(name, 'consistent changes', value, 0)) consistentreadchanges , sum(decode(name, 'consistent gets', value, 0)) consistentreadgets , sum(decode(name, 'CR blocks created', value, 0)) crblks , sum(decode(name, 'data blocks consistent reads - undo records applied', value, 0)) crundorecs , sum(decode(name, 'db block changes', value, 0)) dbblkchanges , sum(decode(name, 'db block gets', value, 0)) dbblkgets , sum(decode(name, 'DBWR checkpoints', value, 0)) dbwrcheckpoints , sum(decode(name, 'sorts (disk)', value, 0)) sortsdisk , sum(decode(name, 'enqueue deadlocks', value, 0)) enqdeadlocks , sum(decode(name, 'enqueue requests', value, 0)) enqreqs , sum(decode(name, 'enqueue timeouts', value, 0)) enqtimeouts , sum(decode(name, 'enqueue waits', value, 0)) enqwaits , sum(decode(name, 'index fast full scans (full)', value, 0)) indxscansfull , sum(decode(name, 'index fast full scans (rowid ranges)', value, 0)) indxscansfullrowid , sum(decode(name, 'index fast full scans (direct read)', value, 0)) indxscansfulldirectread , sum(decode(name, 'execute count', value, 0)) executions , sum(decode(name, 'physical read total bytes', value, 0)) totalPhysicalReads , sum(decode(name, 'physical write total bytes', value, 0)) totalPhysicalWrites , sum(decode(name, 'physical read total IO requests', value, 0)) phyreadtotalioreqs , sum(decode(name, 'physical write total IO requests', value, 0)) phywritetotalioreqs , sum(decode(name, 'leaf node splits', value, 0)) leafnodesplits , sum(decode(name, 'session logical reads', value, 0)) logreads , sum(decode(name, 'logons cumulative', value, 0)) logons , sum(decode(name, 'table scans (long tables)', value, 0)) tabscanslong , sum(decode(name, 'bytes sent via SQL*Net to client', value, 0)) bytestoclientviasqlnet , sum(decode(name, 'bytes received via SQL*Net from client', value, 0)) bytesfromclientviasqlnet , sum(decode(name, 'bytes sent via SQL*Net to dblink', value, 0)) bytestodblinkviasqlnet , sum(decode(name, 'bytes received via SQL*Net from dblink', value, 0)) bytesfromdblinkviasqlnet , sum(decode(name, 'bytes via SQL*Net vector to client', value, 0)) bytestoclientviasqlnetvec , sum(decode(name, 'bytes via SQL*Net vector from client',value, 0)) bytesfromclientviasqlnetvec , sum(decode(name, 'bytes via SQL*Net vector to dblink',value, 0)) bytestodblinkviasqlnetvec , sum(decode(name, 'bytes via SQL*Net vector from dblink',value, 0)) bytesfromdblinkviasqlnetvec , sum(decode(name, 'opened cursors cumulative', value, 0)) opncurs , sum(decode(name, 'parse count (failures)', value, 0)) failedparses , sum(decode(name, 'physical reads direct (lob)', value, 0)) physreadslob , sum(decode(name, 'physical writes direct (lob)', value, 0)) physwriteslob , sum(decode(name, 'physical reads direct', value, 0)) physreadsdir , sum(decode(name, 'physical writes direct', value, 0)) physwritesdir , sum(decode(name, 'physical reads', value, 0)) physicalreads , sum(decode(name, 'physical writes', value, 0)) physicalwrites , sum(decode(name, 'redo size', value, 0)) redosize , sum(decode(name, 'redo writes', value, 0)) redowrites , sum(decode(name, 'sorts (memory)', value, 0)) sortsmemory , sum(decode(name, 'sorts (rows)', value, 0)) sortsrows , sum(decode(name, 'parse count (total)', value, 0)) parses , sum(decode(name, 'parse count (hard)', value, 0)) hardparses , sum(decode(name, 'index scans kdiixs1', value, 0)) indxscanstotal , sum(decode(name, 'user calls', value, 0)) usercalls , sum(decode(name, 'recursive calls', value, 0)) recurscalls , sum(decode(name, 'user commits', value, 0)) commits , sum(decode(name, 'user rollbacks', value, 0)) rollbacks , sum(decode(name, 'rollback changes - undo records applied', value, 0)) userrollbackundorec , sum(decode(name, 'background checkpoints completed', value, 0)) bgcheckpoints from v$sysstat ) sysStat , (select sum(decode(stat_name, 'DB CPU', value/10000, 0)) dbCpu , sum(decode(stat_name, 'DB time', value/10000, 0)) dbTime , sum(decode(stat_name, 'DB time', value/1000000, 0)) active_session from v$sys_time_model ) sysTM , ( select sum(small_sync_read_reqs) smallsyncreadreqs , sum (small_sync_read_latency) smallsyncreadlatency from v$IOSTAT_FILE ) iostat Rationale The SQL statement executed in the root container. Rationale The SQL spent only 25% 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 "196mqnmxgxpv1" was executed 4 times and had an average elapsed time of 375 seconds. Rationale Waiting for event "control file sequential read" in wait class "System I/O" accounted for 74% of the database time spent in processing the SQL statement with SQL_ID "196mqnmxgxpv1". Recommendation 5: SQL Tuning Estimated benefit is .24 active sessions, 2.2% of total activity. ----------------------------------------------------------------- Action Investigate the SELECT statement with SQL_ID "8sas62tckw5d7" 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 8sas62tckw5d7. /*NDTM*/ SELECT T1.INST_ID, RDTIME * 10 as RDTIME, PHYRDS, WRTSTIME * 10 as WRTSTIME, PHYWRTS, USED_PGA, ALLOC_PGA, ROUND(USED_PGA / ALLOC_PGA * 100, 2) RATIO, AVG_PGA FROM (SELECT INST_ID, SUM(READTIM) RDTIME, SUM(PHYRDS) PHYRDS, SUM(WRITETIM) WRTSTIME, SUM(PHYWRTS) PHYWRTS FROM GV$FILESTAT GROUP BY INST_ID) T1 JOIN (SELECT INST_ID, ROUND(SUM(PGA_USED_MEM) / 1024 / 1024, 2) USED_PGA, ROUND(SUM(PGA_ALLOC_MEM) / 1024 / 1024, 2) ALLOC_PGA, ROUND(AVG(PGA_ALLOC_MEM) / 1024 / 1024, 2) AVG_PGA FROM GV$PROCESS GROUP BY INST_ID) T5 ON T1.INST_ID = T5.INST_ID Rationale The SQL statement executed in the root container. Rationale The SQL spent only 14% 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 "8sas62tckw5d7" was executed 29 times and had an average elapsed time of 31 seconds. Rationale At least one execution of the statement ran in parallel. Finding 2: Undersized PGA Impact is 6.49 active sessions, 60.66% of total activity. --------------------------------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. The value of parameter "pga_aggregate_target" was "4096 M" during the analysis period. No recommendations are available. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is 7.58 active sessions, 70.81% of total activity. Finding 3: Unusual "User I/O" Wait Event Impact is .51 active sessions, 4.72% of total activity. ------------------------------------------------------- Wait event "Disk file Mirror Read" in wait class "User I/O" was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .51 active sessions, 4.72% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "Disk file Mirror Read" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Recommendation 2: Application Analysis Estimated benefit is .51 active sessions, 4.72% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "Disk file Mirror Read" waits with P1,P2,P3 ("fileno, blkno, filetype") values "0", "1" and "1" respectively. Recommendation 3: Application Analysis Estimated benefit is .38 active sessions, 3.58% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "Disk file Mirror Read" waits in Module "newdt". Rationale The module activity was in the root container. Recommendation 4: Application Analysis Estimated benefit is .38 active sessions, 3.58% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "Disk file Mirror Read" waits in Service "yydsdb". Rationale The service connected to the root container. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is 7.58 active sessions, 70.81% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Warnings -------- I/O capacity issues cannot be diagnosed because of incomplete AWR snapshots. The flushing of snapshots 4675 and 4676 took 820 seconds which is 22% of the analysis period time. This may reduce the reliability of the ADDM analysis. Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Cluster" 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. The network latency of the cluster interconnect was within acceptable limits of 1 milliseconds. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time.