Findings and Recommendations ---------------------------- Finding 1: Table Locks Impact is 9.15 active sessions, 97.95% of total activity. --------------------------------------------------------- Contention on table lock waits was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is 9.15 active sessions, 97.95% of total activity. -------------------------------------------------------------------- Action Investigate application logic involving DDL and DML on provided blocked objects. Rationale The database object with object ID "1124948" was locked for a significant time. Related Object Database object with ID 1124948. Rationale The database object with object ID "1124968" was locked for a significant time. Related Object Database object with ID 1124968. Rationale The database object with object ID "1124966" was locked for a significant time. Related Object Database object with ID 1124966. Symptoms That Led to the Finding: --------------------------------- Wait class "Application" was consuming significant database time. Impact is 9.15 active sessions, 97.95% of total activity. Finding 2: Top SQL Statements Impact is 9.08 active sessions, 97.16% 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 7.44 active sessions, 79.59% of total activity. -------------------------------------------------------------------- Action Investigate the INSERT statement with SQL_ID "3fc82425mmtgs" 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 3fc82425mmtgs. insert into Task (archived, allowedToDelegate, formName, taskInitiator_id, priority, subTaskStrategy, activationTime, actualOwner_id, createdBy_id, createdOn, deploymentId, documentAccessType, documentContentId, documentType, expirationTime, faultAccessType, faultContentId, faultName, faultType, outputAccessType, outputContentId, outputType, parentId, previousStatus, processId, processInstanceId, processSessionId, skipable, status, workItemId, taskType, OPTLOCK, id) values (: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 ) Rationale The SQL spent only 0% 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 "3fc82425mmtgs" was executed 643 times and had an average elapsed time of 10 seconds. Rationale Waiting for event "enq: TM - contention" in wait class "Application" accounted for 100% of the database time spent in processing the SQL statement with SQL_ID "3fc82425mmtgs". Recommendation 2: SQL Tuning Estimated benefit is .86 active sessions, 9.22% of total activity. ------------------------------------------------------------------ Action Investigate the DELETE statement with SQL_ID "10czs4xz91cym" 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 10czs4xz91cym. delete from Delegation_delegates where task_id=:1 Rationale The SQL spent only 0% 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 "10czs4xz91cym" was executed 260 times and had an average elapsed time of 3.9 seconds. Rationale Waiting for event "enq: TM - contention" in wait class "Application" accounted for 100% of the database time spent in processing the SQL statement with SQL_ID "10czs4xz91cym". Recommendation 3: SQL Tuning Estimated benefit is .78 active sessions, 8.35% of total activity. ------------------------------------------------------------------ Action Investigate the DELETE statement with SQL_ID "2wyv1nhut5590" for possible performance improvements. You can supplement the information given here with an ASH report for this SQL_ID. ===================================================== Here are the actual objects that referenced above(numbers).... OBJECT_ID OWNER OBJECT_NAME ---------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- 1124968 JBPMEAP DELEGATION_DELEGATES 1124966 JBPMEAP DEADLINE 1124948 JBPMEAP ATTACHMENT ===================================================== Mostly same object: SERNAME SID,SESSION# OBJECT OBJECT_TYPE STATUS MODE_HELD -------------------- -------------------- ------------------------------ ------------------- ------------ ---------- JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.TASKEVENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.TASKEVENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.TASKEVENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.WORKITEMINFO TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.ATTACHMENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.DEADLINE TABLE Not Blocking None JBPMEAP (tibas1a) 857,24773 JBPMEAP.CONTENT TABLE Global Row-X (SX) JBPMEAP (tibas1a) 857,24773 JBPMEAP.TASKEVENT TABLE Global Row-X (SX) This is the insert statement: OSUSER USERNAME SID SERIAL# SPID SQL_TEXT ------------------------------ ------------------------------ ---------- ---------- ------------------------ ---------------------------------------------------------------- tibas1a JBPMEAP 545 37081 6364 insert into NodeInstanceLog (connection, log_date, externalId, n tibas1a JBPMEAP 545 37081 6364 odeId, nodeInstanceId, nodeName, nodeType, processId, processIns tibas1a JBPMEAP 545 37081 6364 tanceId, type, workItemId, id) values (:1 , :2 , :3 , :4 , :5 , tibas1a JBPMEAP 545 37081 6364 :6 , :7 , :8 , :9 , :10 , :11 , :12 )