DETAILED ADDM REPORT FOR TASK 'TASK_8233' WITH ID 8233 ------------------------------------------------------ Analysis Period: from 25-JUL-2011 15:00 to 26-JUL-2011 00:00 Database ID/Instance: Database/Instance Names: Host Name: Database Version: 10.2.0.4.0 Snapshot Range: from 4149 to 4158 Database Time: 169058 seconds Average Database Load: 5.2 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 81% impact (137447 seconds) -------------------------------------- Wait event "enq: TS - contention" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 81% benefit (137447 seconds) ACTION: Investigate the cause for high "enq: TS - contention" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation. RATIONALE: The SQL statement with SQL_ID "czvfg1255s5zg" was found waiting for "enq: TS - contention" wait event. RELEVANT OBJECT: SQL statement with SQL_ID czvfg1255s5zg WITH UNQIDX AS (SELECT /*+ index(cc) */ CD.CON#,CC.INTCOL# FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND CD.INTCOLS <= :B1 AND CD.TYPE# IN (2,3) UNION ALL SELECT /*+ index(i) index(ic) */ I.OBJ#,IC.INTCOL# FROM SYS.IND$ I, SYS.ICOL$ IC WHERE I.BO# = :B2 AND I.OBJ# = IC.OBJ# AND I.INTCOLS <= :B1 AND BITAND(PROPERTY,1) = 1 AND BITAND(FLAGS,1025) = 0) SELECT CON# FROM UNQIDX WHERE CON# NOT IN (SELECT /*+ no_unnest */ CON# FROM UNQIDX WHERE INTCOL# NOT IN (SELECT /*+ no_unnest index(ic) */ INTCOL# FROM ICOL$ IC WHERE OBJ#=:B3 )) AND ROWNUM <= 1 RATIONALE: The SQL statement with SQL_ID "0x0tn0fgm910m" was found waiting for "enq: TS - contention" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 0x0tn0fgm910m RECOMMENDATION 2: Application Analysis, 81% benefit (137447 seconds) ACTION: Investigate the cause for high "enq: TS - contention" waits with P1,P2,P3 ("name|mode, tablespace ID, dba") values "1414725636", "3" and "2" respectively. RECOMMENDATION 3: Application Analysis, 81% benefit (136828 seconds) ACTION: Investigate the cause for high "enq: TS - contention" waits in Module "JDBC Thin Client". RECOMMENDATION 4: Application Analysis, 81% benefit (136828 seconds) ACTION: Investigate the cause for high "enq: TS - contention" waits in Service "tism.usps.gov". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (85% impact [143573 seconds]) FINDING 2: 9.2% impact (15627 seconds) -------------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 3.9% benefit (6553 seconds) ACTION: Tune the PL/SQL block with SQL_ID "4q4r0d1u84qqx". Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference" RELEVANT OBJECT: SQL statement with SQL_ID 4q4r0d1u84qqx BEGIN cen_SYNCH_RECPT4_PKG.SAVE(:1 ); END; RATIONALE: SQL statement with SQL_ID "4q4r0d1u84qqx" was executed 246134 times and had an average elapsed time of 0.027 seconds. RECOMMENDATION 2: SQL Tuning, 2.9% benefit (4974 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7930x6h2adrqz". RELEVANT OBJECT: SQL statement with SQL_ID 7930x6h2adrqz and PLAN_HASH 964254352 select count(*) as col_0_0_ from SYNCH_META_DATA synchmetad0_ where synchmetad0_.SQL_TBL_NAME=:1 and synchmetad0_.DB_VER_ID=:2 ACTION: Investigate the SQL statement with SQL_ID "7930x6h2adrqz" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7930x6h2adrqz and PLAN_HASH 964254352 select count(*) as col_0_0_ from SYNCH_META_DATA synchmetad0_ where synchmetad0_.SQL_TBL_NAME=:1 and synchmetad0_.DB_VER_ID=:2 RATIONALE: SQL statement with SQL_ID "7930x6h2adrqz" was executed 145457 times and had an average elapsed time of 0.034 seconds. RATIONALE: At least one execution of the statement ran in parallel. RATIONALE: Waiting for event "enq: PS - contention" in wait class "Other" accounted for 36% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RATIONALE: Waiting for event "rdbms ipc reply" in wait class "Other" accounted for 16% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RATIONALE: Waiting for event "reliable message" in wait class "Other" accounted for 4% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RECOMMENDATION 3: SQL Tuning, 2.4% benefit (4124 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "0k8m2q7a126ss". RELEVANT OBJECT: SQL statement with SQL_ID 0k8m2q7a126ss INSERT INTO RECPT ( RECPT_SEQ_NBR, RECPT_SERVR_SITE_CD, LD_ID, CURR_DAT_SRCE_CD, CURR_IRREG_CD, ML_CAT_CD, ML_CL_CD, ML_SBCL_CD, M_BAG_IND, FNL_BAG_IND, RGTD_INSD_IND, RECPT_SER_NBR, DNGRUS_GDS_IND, EQUIP_TYPE_CD, FMT_OF_CTNS_CD, HANDLNG_CL_CD, EXP_IND, EXMPT_ITEM_IND, RECPT_WT_AMT, RECPT_WT_TYPE_CD, ITM_OR_PCE_CNT, NON_RTNBLE_IND, RECPT_RTND_CNT, RECPT_SEAL_NBR, CLSD_TRST_IND, DELTN_IND, LBL_TYPE_CD, DISP_AND_RTEG_ID, EXMPTN_STS_CD, EDL_BCD_CD, OBSVTN_CD, UPU_BCD, SN_NBR, ML_STS_CD, MILTY_ZIP_CD, MLR_BCD, GSS_ITM_IND, DEADZNE_CD, RECPT_TAG_WT_AMT, LBL_UTC_DTM, LBL_LCL_DTM, DISP_SEQ_NBR, DISP_SERVR_SITE_CD, CNSGNMT_SEQ_NBR, CNSGNMT_SERVR_SITE_CD, CONTR_SEQ_NBR, CONTR_SERVR_SITE_CD, DOC_SEQ_NBR, DOC_SERVR_SITE_CD, RASCAL_BCD, CURR_STS_CD, SLIDE_LBL_BCD, TARE_WT_AMT, RECPT_SEAL_2ND_NBR, DUP_RECPT_CD, CIN_CD, REC_CRN_LCL_DTM, REC_CRN_UTC_DTM, DESTN_EXCH_OFC_CD, DESTN_CTRY_CD, IBRS_ITM_CNT, IBRS_ITM_WT_AMT, SIRVO_RECPT_CMTS_TEXT, PRMT_NBR, MLR_DISP_DT, NESTD_EQUIP_TYPE_CD, NESTD_EQUIP_CNT, POS_PSEUDO_ZIP_CD, ORGN_ZIP_CD, BR_OF_SVC_CD, INTRM_COLL_SEQ_NBR, INTRM_COLL_SERVR_SITE_CD, SPBS_BCD, BIN_NBR, USPS_EQUIP_TYPE_CD, SYNCH_STS_CD, UPDT_USER_ID, UPDT_USER_SITE_ID, LAST_UPDT_LCL_DTM, LAST_UPDT_UTC_DTM, DISP_DOC_IND, UPU_LBL_NBR, FNL_BAG_PRT_IND, MISSENT_WT, RECPT_NET_WT, CONFIRM_IND, EXMPT_WT_AMT, PRODT_TYPE_CD) VALUES (:B88 , :B87 , :B86 , :B85 , :B84 , :B83 , :B82 , :B81 , :B80 , :B79 , :B78 , :B77 , :B76 , :B75 , :B74 , :B73 , :B72 , :B71 , :B70 , :B69 , :B68 , :B67 , :B66 , :B65 , :B64 , :B63 , :B62 , :B61 , :B60 , :B59 , :B58 , :B57 , :B56 , :B55 , :B54 , :B53 , :B52 , :B51 , :B50 , :B49 , :B48 , :B47 , :B46 , :B45 , :B44 , :B43 , :B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) RATIONALE: SQL statement with SQL_ID "0k8m2q7a126ss" was executed 126610 times and had an average elapsed time of 0.032 seconds. FINDING 3: 4.5% impact (7535 seconds) ------------------------------------- Read and write contention on database blocks was consuming significant database time. NO RECOMMENDATIONS AVAILABLE SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Inter-instance messaging was consuming significant database time on this instance. (5.3% impact [9015 seconds]) SYMPTOM: Wait class "Cluster" was consuming significant database time. (5.4% impact [9140 seconds]) FINDING 4: 4.1% impact (6934 seconds) ------------------------------------- SQL statements responsible for significant inter-instance messaging were found RECOMMENDATION 1: SQL Tuning, 2.9% benefit (4974 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7930x6h2adrqz". RELEVANT OBJECT: SQL statement with SQL_ID 7930x6h2adrqz and PLAN_HASH 964254352 select count(*) as col_0_0_ from SYNCH_META_DATA synchmetad0_ where synchmetad0_.SQL_TBL_NAME=:1 and synchmetad0_.DB_VER_ID=:2 ACTION: Investigate the SQL statement with SQL_ID "7930x6h2adrqz" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7930x6h2adrqz and PLAN_HASH 964254352 select count(*) as col_0_0_ from SYNCH_META_DATA synchmetad0_ where synchmetad0_.SQL_TBL_NAME=:1 and synchmetad0_.DB_VER_ID=:2 RATIONALE: SQL statement with SQL_ID "7930x6h2adrqz" was executed 145457 times and had an average elapsed time of 0.034 seconds. RATIONALE: At least one execution of the statement ran in parallel. RATIONALE: Waiting for event "enq: PS - contention" in wait class "Other" accounted for 36% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RATIONALE: Waiting for event "rdbms ipc reply" in wait class "Other" accounted for 16% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RATIONALE: Waiting for event "reliable message" in wait class "Other" accounted for 4% of the database time spent in processing the SQL statement with SQL_ID "7930x6h2adrqz". RATIONALE: Average time spent in Cluster wait events per execution was 0 seconds. RECOMMENDATION 2: SQL Tuning, 2.4% benefit (4124 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "0k8m2q7a126ss". RELEVANT OBJECT: SQL statement with SQL_ID 0k8m2q7a126ss INSERT INTO RECPT ( RECPT_SEQ_NBR, RECPT_SERVR_SITE_CD, LD_ID, CURR_DAT_SRCE_CD, CURR_IRREG_CD, ML_CAT_CD, ML_CL_CD, ML_SBCL_CD, M_BAG_IND, FNL_BAG_IND, RGTD_INSD_IND, RECPT_SER_NBR, DNGRUS_GDS_IND, EQUIP_TYPE_CD, FMT_OF_CTNS_CD, HANDLNG_CL_CD, EXP_IND, EXMPT_ITEM_IND, RECPT_WT_AMT, RECPT_WT_TYPE_CD, ITM_OR_PCE_CNT, NON_RTNBLE_IND, RECPT_RTND_CNT, RECPT_SEAL_NBR, CLSD_TRST_IND, DELTN_IND, LBL_TYPE_CD, DISP_AND_RTEG_ID, EXMPTN_STS_CD, EDL_BCD_CD, OBSVTN_CD, UPU_BCD, SN_NBR, ML_STS_CD, MILTY_ZIP_CD, MLR_BCD, GSS_ITM_IND, DEADZNE_CD, RECPT_TAG_WT_AMT, LBL_UTC_DTM, LBL_LCL_DTM, DISP_SEQ_NBR, DISP_SERVR_SITE_CD, CNSGNMT_SEQ_NBR, CNSGNMT_SERVR_SITE_CD, CONTR_SEQ_NBR, CONTR_SERVR_SITE_CD, DOC_SEQ_NBR, DOC_SERVR_SITE_CD, RASCAL_BCD, CURR_STS_CD, SLIDE_LBL_BCD, TARE_WT_AMT, RECPT_SEAL_2ND_NBR, DUP_RECPT_CD, CIN_CD, REC_CRN_LCL_DTM, REC_CRN_UTC_DTM, DESTN_EXCH_OFC_CD, DESTN_CTRY_CD, IBRS_ITM_CNT, IBRS_ITM_WT_AMT, SIRVO_RECPT_CMTS_TEXT, PRMT_NBR, MLR_DISP_DT, NESTD_EQUIP_TYPE_CD, NESTD_EQUIP_CNT, POS_PSEUDO_ZIP_CD, ORGN_ZIP_CD, BR_OF_SVC_CD, INTRM_COLL_SEQ_NBR, INTRM_COLL_SERVR_SITE_CD, SPBS_BCD, BIN_NBR, USPS_EQUIP_TYPE_CD, SYNCH_STS_CD, UPDT_USER_ID, UPDT_USER_SITE_ID, LAST_UPDT_LCL_DTM, LAST_UPDT_UTC_DTM, DISP_DOC_IND, UPU_LBL_NBR, FNL_BAG_PRT_IND, MISSENT_WT, RECPT_NET_WT, CONFIRM_IND, EXMPT_WT_AMT, PRODT_TYPE_CD) VALUES (:B88 , :B87 , :B86 , :B85 , :B84 , :B83 , :B82 , :B81 , :B80 , :B79 , :B78 , :B77 , :B76 , :B75 , :B74 , :B73 , :B72 , :B71 , :B70 , :B69 , :B68 , :B67 , :B66 , :B65 , :B64 , :B63 , :B62 , :B61 , :B60 , :B59 , :B58 , :B57 , :B56 , :B55 , :B54 , :B53 , :B52 , :B51 , :B50 , :B49 , :B48 , :B47 , :B46 , :B45 , :B44 , :B43 , :B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) RATIONALE: SQL statement with SQL_ID "0k8m2q7a126ss" was executed 126610 times and had an average elapsed time of 0.032 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.023 seconds. RECOMMENDATION 3: SQL Tuning, 2.4% benefit (3983 seconds) ACTION: Tune the PL/SQL block with SQL_ID "4q4r0d1u84qqx". Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference" RELEVANT OBJECT: SQL statement with SQL_ID 4q4r0d1u84qqx BEGIN cen_SYNCH_RECPT4_PKG.SAVE(:1 ); END; RATIONALE: SQL statement with SQL_ID "4q4r0d1u84qqx" was executed 246134 times and had an average elapsed time of 0.027 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.016 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Cluster" was consuming significant database time. (5.4% impact [9140 seconds]) FINDING 5: 1.3% impact (2224 seconds) ------------------------------------- Read and write contention on database blocks was consuming significant database time in the cluster. RECOMMENDATION 1: Application Analysis, 1.3% benefit (2224 seconds) ACTION: Verify that the set of services used by the application to connect to the database are optimally distributed if response time is critical. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Inter-instance messaging was consuming significant database time on this instance. (5.3% impact [9015 seconds]) SYMPTOM: Wait class "Cluster" was consuming significant database time. (5.4% impact [9140 seconds]) FINDING 6: 1.1% impact (1839 seconds) ------------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 1.1% benefit (1839 seconds) ACTION: Consider increasing the throughput of the I/O subsystem. Oracle's recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance. Alternatively, consider using Oracle's Automatic Storage Management solution. RATIONALE: During the analysis period, the average data files' I/O throughput was 551 K per second for reads and 307 K per second for writes. The average response time for single block reads was 24 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (2.1% impact [3598 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL 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 22% of the analysis period. The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds. An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail.