DETAILED ADDM REPORT FOR TASK 'TASK_2525' WITH ID 2525 ------------------------------------------------------ Analysis Period: 21-NOV-2006 from 14:29:46 to 15:30:42 Database ID/Instance: 417593882/2 Database/Instance Names: SSKIDB/SSKIDB2 Host Name: sskidb2 Database Version: 10.2.0.2.0 Snapshot Range: from 1172 to 1174 Database Time: 2864 seconds Average Database Load: .8 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 32% impact (919 seconds) ----------------------------------- Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time. RECOMMENDATION 1: Application Analysis, 32% benefit (919 seconds) ACTION: Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. RATIONALE: The application was performing 4965 transactions per minute with an average redo size of 1840 bytes per transaction. RECOMMENDATION 2: Host Configuration, 32% benefit (919 seconds) 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 1 K and the average time per write was 1 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Commit" was consuming significant database time. (32% impact [919 seconds]) FINDING 2: 24% impact (688 seconds) ----------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 8.4% benefit (240 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7mbks1b9mj0pn". RELEVANT OBJECT: SQL statement with SQL_ID 7mbks1b9mj0pn and PLAN_HASH 2022473933 INSERT INTO ORDER_HISTORY ( ORDER_HISTORY_ID , ORDER_ID , DP_ID , EXCHANGE_CODE , CUSTOMER_ID , SHAREKHAN_SCRIP_CODE , CHANNEL_CODE , CHANNEL_USER , ORDER_QTY , ORDER_PRICE , ORS_ORDER_ID , EXCH_ORDER_ID , ORDER_DATE_TIME , GOOD_TILL , GOOD_TILL_DATE , BUY_SELL , EXCH_ACK_DATE_TIME , ORDER_STATUS , ORDER_DISCLOSED_QTY , ORDER_EXEC_QTY , ORDER_TRIGGER_PRICE , SEGMENT_CODE , AFTER_HOUR , DP_CLIENT_ID , ORS_TOKEN_ID , UPDATE_DATE , UPDATE_USER , MOD_ORD_QTY , MOD_ORD_PRICE , MOD_ORD_TRIGGER_PRICE , MOD_DATE_TIME , MOD_DQ_QTY , SK_SCRIP_ID , CTCL_CODE , ORDER_EXE_PRICE , ORDER_DISPLAY_STATUS , MKT_ORDER_PRICE , MOD_MKT_PRICE , MP_COVER_ORDER_ID , CTCL_BROKER_ID , RMS_CODE , REQUEST_STATUS , ERROR_MSG ) VALUES ( ORDER_HISTORY_NEW_SEQ.NEXTVAL, :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 ) ACTION: Investigate the SQL statement with SQL_ID "7mbks1b9mj0pn" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7mbks1b9mj0pn and PLAN_HASH 2022473933 INSERT INTO ORDER_HISTORY ( ORDER_HISTORY_ID , ORDER_ID , DP_ID , EXCHANGE_CODE , CUSTOMER_ID , SHAREKHAN_SCRIP_CODE , CHANNEL_CODE , CHANNEL_USER , ORDER_QTY , ORDER_PRICE , ORS_ORDER_ID , EXCH_ORDER_ID , ORDER_DATE_TIME , GOOD_TILL , GOOD_TILL_DATE , BUY_SELL , EXCH_ACK_DATE_TIME , ORDER_STATUS , ORDER_DISCLOSED_QTY , ORDER_EXEC_QTY , ORDER_TRIGGER_PRICE , SEGMENT_CODE , AFTER_HOUR , DP_CLIENT_ID , ORS_TOKEN_ID , UPDATE_DATE , UPDATE_USER , MOD_ORD_QTY , MOD_ORD_PRICE , MOD_ORD_TRIGGER_PRICE , MOD_DATE_TIME , MOD_DQ_QTY , SK_SCRIP_ID , CTCL_CODE , ORDER_EXE_PRICE , ORDER_DISPLAY_STATUS , MKT_ORDER_PRICE , MOD_MKT_PRICE , MP_COVER_ORDER_ID , CTCL_BROKER_ID , RMS_CODE , REQUEST_STATUS , ERROR_MSG ) VALUES ( ORDER_HISTORY_NEW_SEQ.NEXTVAL, :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 ) RATIONALE: SQL statement with SQL_ID "7mbks1b9mj0pn" was executed 16695 times and had an average elapsed time of 0.014 seconds. RECOMMENDATION 2: SQL Tuning, 4.1% benefit (118 seconds) ACTION: Tune the PL/SQL block with SQL_ID "817xqt5ufmbjv". 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 817xqt5ufmbjv BEGIN DPR_CALC_CCB (:1,:2,:3,:4,:5); END; RATIONALE: SQL statement with SQL_ID "817xqt5ufmbjv" was executed 5629 times and had an average elapsed time of 0.021 seconds. RECOMMENDATION 3: SQL Tuning, 4% benefit (114 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7gxynv3mp129w". RELEVANT OBJECT: SQL statement with SQL_ID 7gxynv3mp129w INSERT INTO ORDER_TRACKER(ORDER_ID, DP_ID, EXCHANGE_CODE, CUSTOMER_ID, SHAREKHAN_SCRIP_CODE, CHANNEL_CODE, CHANNEL_USER, ORDER_QTY, ORDER_PRICE, ORS_ORDER_ID, EXCH_ORDER_ID, ORDER_DATE_TIME, GOOD_TILL, GOOD_TILL_DATE, BUY_SELL, EXCH_ACK_DATE_TIME, ORDER_STATUS, ORDER_DISCLOSED_QTY, ORDER_EXEC_QTY, ORDER_TRIGGER_PRICE, SEGMENT_CODE, AFTER_HOUR, DP_CLIENT_ID, ORS_TOKEN_ID, UPDATE_DATE, UPDATE_USER, MOD_ORD_QTY, MOD_ORD_PRICE, MOD_ORD_TRIGGER_PRICE, MOD_DQ_QTY, SK_SCRIP_ID, CTCL_CODE, ORDER_EXE_PRICE, ORDER_DISPLAY_STATUS, MKT_ORDER_PRICE, MOD_MKT_PRICE, MP_COVER_ORDER_ID, CTCL_BROKER_ID, RMS_CODE, REQUEST_STATUS) VALUES(:B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , SYSDATE, :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , SYSDATE, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) ACTION: Investigate the SQL statement with SQL_ID "7gxynv3mp129w" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7gxynv3mp129w INSERT INTO ORDER_TRACKER(ORDER_ID, DP_ID, EXCHANGE_CODE, CUSTOMER_ID, SHAREKHAN_SCRIP_CODE, CHANNEL_CODE, CHANNEL_USER, ORDER_QTY, ORDER_PRICE, ORS_ORDER_ID, EXCH_ORDER_ID, ORDER_DATE_TIME, GOOD_TILL, GOOD_TILL_DATE, BUY_SELL, EXCH_ACK_DATE_TIME, ORDER_STATUS, ORDER_DISCLOSED_QTY, ORDER_EXEC_QTY, ORDER_TRIGGER_PRICE, SEGMENT_CODE, AFTER_HOUR, DP_CLIENT_ID, ORS_TOKEN_ID, UPDATE_DATE, UPDATE_USER, MOD_ORD_QTY, MOD_ORD_PRICE, MOD_ORD_TRIGGER_PRICE, MOD_DQ_QTY, SK_SCRIP_ID, CTCL_CODE, ORDER_EXE_PRICE, ORDER_DISPLAY_STATUS, MKT_ORDER_PRICE, MOD_MKT_PRICE, MP_COVER_ORDER_ID, CTCL_BROKER_ID, RMS_CODE, REQUEST_STATUS) VALUES(:B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , SYSDATE, :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , SYSDATE, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) RATIONALE: SQL statement with SQL_ID "7gxynv3mp129w" was executed 5470 times and had an average elapsed time of 0.02 seconds. RECOMMENDATION 4: SQL Tuning, 3.7% benefit (105 seconds) ACTION: Tune the PL/SQL block with SQL_ID "1x5w9da591v26". 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 1x5w9da591v26 BEGIN EQUITY_INSERT_OT(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14 ,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31, :32,:33,:34,:35,:36,:37,:38,:39,:40); END; RATIONALE: SQL statement with SQL_ID "1x5w9da591v26" was executed 3335 times and had an average elapsed time of 0.031 seconds. RECOMMENDATION 5: SQL Tuning, 2.9% benefit (82 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "26sdvthsdh33n". RELEVANT OBJECT: SQL statement with SQL_ID 26sdvthsdh33n and PLAN_HASH 3150235404 update CTCL_ACK_HANDLER_OBJECT set TRAN_STATUS = :1, FINISHED_TIME = sysdate, ERROR_STRING = :2 where ORDER_ID = :3 and TRAN_TYPE = :4 and VERSION_NO = :5 RATIONALE: SQL statement with SQL_ID "26sdvthsdh33n" was executed 7718 times and had an average elapsed time of 0.014 seconds. FINDING 3: 12% impact (356 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. (21% impact [606 seconds]) SYMPTOM: Wait class "Cluster" was consuming significant database time. (21% impact [606 seconds]) FINDING 4: 7.6% impact (219 seconds) ------------------------------------ Wait event "DFS lock handle" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 7.6% benefit (219 seconds) ACTION: Investigate the cause for high "DFS lock handle" 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 "bkjx564mnxjqf" was found waiting for "DFS lock handle" wait event. RELEVANT OBJECT: SQL statement with SQL_ID bkjx564mnxjqf SELECT ACK_OBJECT_SEQ.NEXTVAL FROM DUAL RECOMMENDATION 2: Application Analysis, 7.6% benefit (219 seconds) ACTION: Investigate the cause for high "DFS lock handle" waits in Service "SSKIDB". RECOMMENDATION 3: Application Analysis, 3.7% benefit (105 seconds) ACTION: Investigate the cause for high "DFS lock handle" waits in Module "java@weblogic-sski9 (TNS V1-V3)". RECOMMENDATION 4: Application Analysis, 3.7% benefit (105 seconds) ACTION: Investigate the cause for high "DFS lock handle" waits with P1,P2,P3 ("type|mode, id1, id2") values "1398145029", "40388" and "0" respectively. RECOMMENDATION 5: Application Analysis, 3% benefit (86 seconds) ACTION: Investigate the cause for high "DFS lock handle" waits in Module "java@weblogic-sski6 (TNS V1-V3)". RECOMMENDATION 6: Application Analysis, 3% benefit (86 seconds) ACTION: Investigate the cause for high "DFS lock handle" waits with P1,P2,P3 ("type|mode, id1, id2") values "1398145029", "40386" and "0" respectively. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (17% impact [487 seconds]) FINDING 5: 7.3% impact (210 seconds) ------------------------------------ SQL statements responsible for significant inter-instance messaging were found RECOMMENDATION 1: SQL Tuning, 8.4% benefit (240 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7mbks1b9mj0pn". RELEVANT OBJECT: SQL statement with SQL_ID 7mbks1b9mj0pn and PLAN_HASH 2022473933 INSERT INTO ORDER_HISTORY ( ORDER_HISTORY_ID , ORDER_ID , DP_ID , EXCHANGE_CODE , CUSTOMER_ID , SHAREKHAN_SCRIP_CODE , CHANNEL_CODE , CHANNEL_USER , ORDER_QTY , ORDER_PRICE , ORS_ORDER_ID , EXCH_ORDER_ID , ORDER_DATE_TIME , GOOD_TILL , GOOD_TILL_DATE , BUY_SELL , EXCH_ACK_DATE_TIME , ORDER_STATUS , ORDER_DISCLOSED_QTY , ORDER_EXEC_QTY , ORDER_TRIGGER_PRICE , SEGMENT_CODE , AFTER_HOUR , DP_CLIENT_ID , ORS_TOKEN_ID , UPDATE_DATE , UPDATE_USER , MOD_ORD_QTY , MOD_ORD_PRICE , MOD_ORD_TRIGGER_PRICE , MOD_DATE_TIME , MOD_DQ_QTY , SK_SCRIP_ID , CTCL_CODE , ORDER_EXE_PRICE , ORDER_DISPLAY_STATUS , MKT_ORDER_PRICE , MOD_MKT_PRICE , MP_COVER_ORDER_ID , CTCL_BROKER_ID , RMS_CODE , REQUEST_STATUS , ERROR_MSG ) VALUES ( ORDER_HISTORY_NEW_SEQ.NEXTVAL, :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 ) ACTION: Investigate the SQL statement with SQL_ID "7mbks1b9mj0pn" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7mbks1b9mj0pn and PLAN_HASH 2022473933 INSERT INTO ORDER_HISTORY ( ORDER_HISTORY_ID , ORDER_ID , DP_ID , EXCHANGE_CODE , CUSTOMER_ID , SHAREKHAN_SCRIP_CODE , CHANNEL_CODE , CHANNEL_USER , ORDER_QTY , ORDER_PRICE , ORS_ORDER_ID , EXCH_ORDER_ID , ORDER_DATE_TIME , GOOD_TILL , GOOD_TILL_DATE , BUY_SELL , EXCH_ACK_DATE_TIME , ORDER_STATUS , ORDER_DISCLOSED_QTY , ORDER_EXEC_QTY , ORDER_TRIGGER_PRICE , SEGMENT_CODE , AFTER_HOUR , DP_CLIENT_ID , ORS_TOKEN_ID , UPDATE_DATE , UPDATE_USER , MOD_ORD_QTY , MOD_ORD_PRICE , MOD_ORD_TRIGGER_PRICE , MOD_DATE_TIME , MOD_DQ_QTY , SK_SCRIP_ID , CTCL_CODE , ORDER_EXE_PRICE , ORDER_DISPLAY_STATUS , MKT_ORDER_PRICE , MOD_MKT_PRICE , MP_COVER_ORDER_ID , CTCL_BROKER_ID , RMS_CODE , REQUEST_STATUS , ERROR_MSG ) VALUES ( ORDER_HISTORY_NEW_SEQ.NEXTVAL, :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 ) RATIONALE: SQL statement with SQL_ID "7mbks1b9mj0pn" was executed 16695 times and had an average elapsed time of 0.014 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.004 seconds. RECOMMENDATION 2: SQL Tuning, 4% benefit (114 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7gxynv3mp129w". RELEVANT OBJECT: SQL statement with SQL_ID 7gxynv3mp129w INSERT INTO ORDER_TRACKER(ORDER_ID, DP_ID, EXCHANGE_CODE, CUSTOMER_ID, SHAREKHAN_SCRIP_CODE, CHANNEL_CODE, CHANNEL_USER, ORDER_QTY, ORDER_PRICE, ORS_ORDER_ID, EXCH_ORDER_ID, ORDER_DATE_TIME, GOOD_TILL, GOOD_TILL_DATE, BUY_SELL, EXCH_ACK_DATE_TIME, ORDER_STATUS, ORDER_DISCLOSED_QTY, ORDER_EXEC_QTY, ORDER_TRIGGER_PRICE, SEGMENT_CODE, AFTER_HOUR, DP_CLIENT_ID, ORS_TOKEN_ID, UPDATE_DATE, UPDATE_USER, MOD_ORD_QTY, MOD_ORD_PRICE, MOD_ORD_TRIGGER_PRICE, MOD_DQ_QTY, SK_SCRIP_ID, CTCL_CODE, ORDER_EXE_PRICE, ORDER_DISPLAY_STATUS, MKT_ORDER_PRICE, MOD_MKT_PRICE, MP_COVER_ORDER_ID, CTCL_BROKER_ID, RMS_CODE, REQUEST_STATUS) VALUES(:B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , SYSDATE, :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , SYSDATE, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) ACTION: Investigate the SQL statement with SQL_ID "7gxynv3mp129w" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 7gxynv3mp129w INSERT INTO ORDER_TRACKER(ORDER_ID, DP_ID, EXCHANGE_CODE, CUSTOMER_ID, SHAREKHAN_SCRIP_CODE, CHANNEL_CODE, CHANNEL_USER, ORDER_QTY, ORDER_PRICE, ORS_ORDER_ID, EXCH_ORDER_ID, ORDER_DATE_TIME, GOOD_TILL, GOOD_TILL_DATE, BUY_SELL, EXCH_ACK_DATE_TIME, ORDER_STATUS, ORDER_DISCLOSED_QTY, ORDER_EXEC_QTY, ORDER_TRIGGER_PRICE, SEGMENT_CODE, AFTER_HOUR, DP_CLIENT_ID, ORS_TOKEN_ID, UPDATE_DATE, UPDATE_USER, MOD_ORD_QTY, MOD_ORD_PRICE, MOD_ORD_TRIGGER_PRICE, MOD_DQ_QTY, SK_SCRIP_ID, CTCL_CODE, ORDER_EXE_PRICE, ORDER_DISPLAY_STATUS, MKT_ORDER_PRICE, MOD_MKT_PRICE, MP_COVER_ORDER_ID, CTCL_BROKER_ID, RMS_CODE, REQUEST_STATUS) VALUES(:B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , SYSDATE, :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , SYSDATE, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) RATIONALE: SQL statement with SQL_ID "7gxynv3mp129w" was executed 5470 times and had an average elapsed time of 0.02 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.0076 seconds. RECOMMENDATION 3: SQL Tuning, 1.3% benefit (39 seconds) ACTION: Tune the PL/SQL block with SQL_ID "fv2ncy2wp63sm". 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 fv2ncy2wp63sm BEGIN INSERT_ACK_OBJECT(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:1 4,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29); END; RATIONALE: SQL statement with SQL_ID "fv2ncy2wp63sm" was executed 4264 times and had an average elapsed time of 0.014 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.009 seconds. RECOMMENDATION 4: SQL Tuning, 1.3% benefit (36 seconds) ACTION: Tune the PL/SQL block with SQL_ID "37wgm71sdhuwz". 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 37wgm71sdhuwz BEGIN INSERT_ACK_OBJECT(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:1 4,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29); END; RATIONALE: SQL statement with SQL_ID "37wgm71sdhuwz" was executed 5481 times and had an average elapsed time of 0.015 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.0066 seconds. RECOMMENDATION 5: SQL Tuning, 0.87% benefit (25 seconds) ACTION: Tune the PL/SQL block with SQL_ID "1x5w9da591v26". 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 1x5w9da591v26 BEGIN EQUITY_INSERT_OT(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14 ,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31, :32,:33,:34,:35,:36,:37,:38,:39,:40); END; RATIONALE: SQL statement with SQL_ID "1x5w9da591v26" was executed 3335 times and had an average elapsed time of 0.031 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.0074 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Cluster" was consuming significant database time. (21% impact [606 seconds]) FINDING 6: 5% impact (143 seconds) ---------------------------------- SQL statements were found waiting for row lock waits. RECOMMENDATION 1: Application Analysis, 1.9% benefit (55 seconds) ACTION: Significant row contention was detected in the TABLE "SKOWNER.SUMMARY_ORDER_BOOK" with object id 41845. Trace the cause of row contention in the application logic using the given blocked SQL. RELEVANT OBJECT: database object with id 41845 SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Application" was consuming significant database time. (5% impact [143 seconds]) FINDING 7: 4.7% impact (134 seconds) ------------------------------------ Time spent on the CPU by the instance was responsible for a substantial part of database time. RECOMMENDATION 1: SQL Tuning, 2.9% benefit (82 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "26sdvthsdh33n". RELEVANT OBJECT: SQL statement with SQL_ID 26sdvthsdh33n and PLAN_HASH 3150235404 update CTCL_ACK_HANDLER_OBJECT set TRAN_STATUS = :1, FINISHED_TIME = sysdate, ERROR_STRING = :2 where ORDER_ID = :3 and TRAN_TYPE = :4 and VERSION_NO = :5 RATIONALE: SQL statement with SQL_ID "26sdvthsdh33n" was executed 7718 times and had an average elapsed time of 0.014 seconds. RATIONALE: Average CPU used per execution was 0.0089 seconds. RECOMMENDATION 2: SQL Tuning, 2.3% benefit (65 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "a5xhycawdyw0t". RELEVANT OBJECT: SQL statement with SQL_ID a5xhycawdyw0t and PLAN_HASH 741281283 SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE=TO_NUMBER(:hash) ORDER BY PIECE RATIONALE: SQL statement with SQL_ID "a5xhycawdyw0t" was executed 1620 times and had an average elapsed time of 0.04 seconds. RATIONALE: Average CPU used per execution was 0.039 seconds. FINDING 8: 4.5% impact (129 seconds) ------------------------------------ Wait event "KJC: Wait for msg sends to complete" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 4.5% benefit (129 seconds) ACTION: Investigate the cause for high "KJC: Wait for msg sends to complete" waits. Refer to Oracle's "Database Reference" for the description of this wait event. RECOMMENDATION 2: Application Analysis, 4.5% benefit (129 seconds) ACTION: Investigate the cause for high "KJC: Wait for msg sends to complete" waits in Service "SSKIDB". RECOMMENDATION 3: Application Analysis, 1.2% benefit (34 seconds) ACTION: Investigate the cause for high "KJC: Wait for msg sends to complete" waits in Module "java@weblogic-sski6 (TNS V1-V3)". RECOMMENDATION 4: Application Analysis, 1.2% benefit (34 seconds) ACTION: Investigate the cause for high "KJC: Wait for msg sends to complete" waits in Module "java@weblogic-sski9 (TNS V1-V3)". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (17% impact [487 seconds]) FINDING 9: 2.4% impact (67 seconds) ----------------------------------- Wait event "wait for scn ack" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 2.4% benefit (67 seconds) ACTION: Investigate the cause for high "wait for scn ack" waits. Refer to Oracle's "Database Reference" for the description of this wait event. RECOMMENDATION 2: Application Analysis, 2.4% benefit (67 seconds) ACTION: Investigate the cause for high "wait for scn ack" waits in Service "SYS$BACKGROUND". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (17% impact [487 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. Wait class "Network" was not consuming significant database time. Wait class "User I/O" 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 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.