SYS@ORASP1> SYS@ORASP1> SYS@ORASP1> SYS@ORASP1> SYS@ORASP1> SYS@ORASP1> 2 DETAILED ADDM REPORT FOR TASK 'TASK_67283' WITH ID 67283 -------------------------------------------------------- Analysis Period: from 24-MAR-2011 06:00 to 25-MAR-2011 18:00 Database ID/Instance: 3184325432/1 Database/Instance Names: ORASP/ORASP1 Host Name: prvrac0.provade.com Database Version: 10.2.0.4.0 Snapshot Range: from 1 to 37 Database Time: 21301 seconds Average Database Load: .2 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 64% impact (13716 seconds) ------------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 15% benefit (3131 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7x49avyr2au94". RELEVANT OBJECT: SQL statement with SQL_ID 7x49avyr2au94 and PLAN_HASH 343647695 select distinct T5086.BUSINESS_UNIT as c1, T5144.NAME as c2, T8160.CURRENCY_CD as c3, T8160.WO_RATE as c4, T8160.WORK_ORDER_ID as c5, T8160.JOB_TITLE as c6, case when T7363.PERIOD_END_DT >= TO_DATE('2011-03-25' , 'YYYY-MM-DD') - 7 then T7435.SP_TIME_QTY end as c7, T7435.SP_TIME_QTY as c8, T7435.DATE_UNDER_RPT as c9, T6648.DESCR as c10, T5090.DESCR as c11 from ( PS_SPA_TM_DTL_HRLY T7435 /* F_SPA_TM_DTL_HRLY */ left outer join ( ( ( ( ( ( PS_PRV_WO_TMAMT_VW T8160 /* F_SERVICES_MGMT */ left outer join PS_PRV_PROJECT_VW T5097 /* D_ACCT_PROJECT */ On T5097.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5097.PROJECT_ID = T8160.PROJECT_ID) left outer join PS_PRV_WORDERREC T10388 /* F_PRV_WORDERREC */ On T8160.BUSINESS_UNIT = T10388.BUSINESS_UNIT and T8160.WORK_ORDER_ID = T10388.WORK_ORDER_ID) left outer join PSOPRDEFN T9903 /* F_PRV_EXPENSE_APPROVER_EMAIL */ On T8160.SP_EXP_APPROVER = T9903.OPRID) left outer join PSOPRDEFN T9789 /* F_PRV_TIMESHEET_APPROVER_EMAIL */ On T8160.TE_APPROVER = T9789.OPRID) left outer join ( PS_PRV_DEPT_SP_VW T5090 /* D_DEPARTMENT */ left outer join PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ On T5090.BUSINESS_UNIT = T10522.BUSINESS_UNIT) On T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5090.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5090.DEPTID = T8160.DEPTID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T6648.LOCATION = T8160.LOCATION) On T7435.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7435.WORK_ORDER_ID = T8160.WORK_ORDER_ID) left outer join ( ( ( ( PS_SPA_TIME_DTL T7396 /* F_SPA_TIME_DTL */ left outer join PS_PRV_ACTVT_SP_VW T7684 /* D_ACTIVITY */ On T7396.BUSINESS_UNIT = T7684.BUSINESS_UNIT and T7396.ACTIVITY_ID = T7684.ACTIVITY_ID) left outer join PS_PRV_TIMHIST_APR T11231 /* F_PRV_TIMHIST_APR_SERVICES */ On T7396.BUSINESS_UNIT = T11231.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11231.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11231.WORK_ORDER_ID and T7396.LINE_NBR = T11231.LINE_NBR) left outer join PS_PRV_TIMHIST_DEN T11238 /* F_PRV_TIMHIST_DEN_SERVICES */ On T7396.BUSINESS_UNIT = T11238.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11238.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11238.WORK_ORDER_ID and T7396.LINE_NBR = T11238.LINE_NBR) left outer join PSOPRDEFN T8824 /* D_OPRDEFN */ On T8824.OPRID = T11231.NAME_DISPLAY) On T7396.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7396.WORK_ORDER_ID = T8160.WORK_ORDER_ID, PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , ( PS_PRV_SRVCP_SP_VW T5144 /* D_SERVICE_PROVIDER */ left outer join PS_PRV_SERVICE_PRV T11106 /* F_PRV_SERVICE_PRV */ On T5144.PERSON_ID = T11106.PERSON_ID) left outer join PS_PRV_HIRE_VW T11109 /* F_PRV_HIRE_VW */ On T5144.PERSON_ID = T11109.PERSON_ID, PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ , PS_SPA_TIME_HDR T7363 /* F_SPA_TIME_HDR */ , PS_PRV_TIME_STS_VW T8381 /* D_XLAT_TIME_HDR_STATUS */ where ( T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT ) and ( T5086.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T9898.PERSON_ID and T7363.BUSINESS_UNIT = T7396.BUSINESS_UNIT and T7363.TIME_SHEET_ID = T7396.TIME_SHEET_ID and T5086.BUSINESS_UNIT = 'HMINC' and T5090.BUSINESS_UNIT = 'HMINC' and T7363.TIME_HDR_STATUS = T8381.FIELDVALUE and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7435.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7396.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = 'HMINC' and T8381.XLATLONGNAME = 'Approved' and T9898.PRIMARY_FLAG = 'Y' and T10522.OPRID = 'Bacon' ) order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 RATIONALE: SQL statement with SQL_ID "7x49avyr2au94" was executed 1 times and had an average elapsed time of 3080 seconds. RECOMMENDATION 2: SQL Tuning, 14% benefit (3068 seconds) ACTION: Tune the PL/SQL block with SQL_ID "05s9358mm6vrr". 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 05s9358mm6vrr begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end; RATIONALE: SQL statement with SQL_ID "05s9358mm6vrr" was executed 1 times and had an average elapsed time of 3067 seconds. RECOMMENDATION 3: SQL Tuning, 14% benefit (3060 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "gahwjc3hzw9ch". RELEVANT OBJECT: SQL statement with SQL_ID gahwjc3hzw9ch and PLAN_HASH 343647695 select distinct T5086.BUSINESS_UNIT as c1, T5144.NAME as c2, T8160.CURRENCY_CD as c3, T8160.WO_RATE as c4, T8160.WORK_ORDER_ID as c5, T8160.JOB_TITLE as c6, case when T7363.PERIOD_END_DT >= TO_DATE('2011-03-25' , 'YYYY-MM-DD') then T7435.SP_TIME_QTY end as c7, T7435.SP_TIME_QTY as c8, T7435.DATE_UNDER_RPT as c9, T6648.DESCR as c10, T5090.DESCR as c11 from ( PS_SPA_TM_DTL_HRLY T7435 /* F_SPA_TM_DTL_HRLY */ left outer join ( ( ( ( ( ( PS_PRV_WO_TMAMT_VW T8160 /* F_SERVICES_MGMT */ left outer join PS_PRV_PROJECT_VW T5097 /* D_ACCT_PROJECT */ On T5097.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5097.PROJECT_ID = T8160.PROJECT_ID) left outer join PS_PRV_WORDERREC T10388 /* F_PRV_WORDERREC */ On T8160.BUSINESS_UNIT = T10388.BUSINESS_UNIT and T8160.WORK_ORDER_ID = T10388.WORK_ORDER_ID) left outer join PSOPRDEFN T9903 /* F_PRV_EXPENSE_APPROVER_EMAIL */ On T8160.SP_EXP_APPROVER = T9903.OPRID) left outer join PSOPRDEFN T9789 /* F_PRV_TIMESHEET_APPROVER_EMAIL */ On T8160.TE_APPROVER = T9789.OPRID) left outer join ( PS_PRV_DEPT_SP_VW T5090 /* D_DEPARTMENT */ left outer join PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ On T5090.BUSINESS_UNIT = T10522.BUSINESS_UNIT) On T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5090.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5090.DEPTID = T8160.DEPTID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T6648.LOCATION = T8160.LOCATION) On T7435.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7435.WORK_ORDER_ID = T8160.WORK_ORDER_ID) left outer join ( ( ( ( PS_SPA_TIME_DTL T7396 /* F_SPA_TIME_DTL */ left outer join PS_PRV_ACTVT_SP_VW T7684 /* D_ACTIVITY */ On T7396.BUSINESS_UNIT = T7684.BUSINESS_UNIT and T7396.ACTIVITY_ID = T7684.ACTIVITY_ID) left outer join PS_PRV_TIMHIST_APR T11231 /* F_PRV_TIMHIST_APR_SERVICES */ On T7396.BUSINESS_UNIT = T11231.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11231.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11231.WORK_ORDER_ID and T7396.LINE_NBR = T11231.LINE_NBR) left outer join PS_PRV_TIMHIST_DEN T11238 /* F_PRV_TIMHIST_DEN_SERVICES */ On T7396.BUSINESS_UNIT = T11238.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11238.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11238.WORK_ORDER_ID and T7396.LINE_NBR = T11238.LINE_NBR) left outer join PSOPRDEFN T8824 /* D_OPRDEFN */ On T8824.OPRID = T11231.NAME_DISPLAY) On T7396.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7396.WORK_ORDER_ID = T8160.WORK_ORDER_ID, PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , ( PS_PRV_SRVCP_SP_VW T5144 /* D_SERVICE_PROVIDER */ left outer join PS_PRV_SERVICE_PRV T11106 /* F_PRV_SERVICE_PRV */ On T5144.PERSON_ID = T11106.PERSON_ID) left outer join PS_PRV_HIRE_VW T11109 /* F_PRV_HIRE_VW */ On T5144.PERSON_ID = T11109.PERSON_ID, PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ , PS_SPA_TIME_HDR T7363 /* F_SPA_TIME_HDR */ , PS_PRV_TIME_STS_VW T8381 /* D_XLAT_TIME_HDR_STATUS */ where ( T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT ) and ( T5086.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T9898.PERSON_ID and T7363.BUSINESS_UNIT = T7396.BUSINESS_UNIT and T7363.TIME_SHEET_ID = T7396.TIME_SHEET_ID and T5086.BUSINESS_UNIT = 'HMINC' and T5090.BUSINESS_UNIT = 'HMINC' and T7363.TIME_HDR_STATUS = T8381.FIELDVALUE and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7435.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7396.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = 'HMINC' and T8381.XLATLONGNAME = 'Approved' and T9898.PRIMARY_FLAG = 'Y' and T10522.OPRID = 'Bacon' ) order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 RATIONALE: SQL statement with SQL_ID "gahwjc3hzw9ch" was executed 1 times and had an average elapsed time of 3019 seconds. RECOMMENDATION 4: SQL Tuning, 14% benefit (3041 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "43w0r9122v7jm". RELEVANT OBJECT: SQL statement with SQL_ID 43w0r9122v7jm and PLAN_HASH 3677915864 select max(bytes) from dba_segments RECOMMENDATION 5: SQL Tuning, 7.1% benefit (1514 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "5gp0520qnhgrb". RELEVANT OBJECT: SQL statement with SQL_ID 5gp0520qnhgrb and PLAN_HASH 3481439974 SELECT DISTINCT (PROCESSID) FROM PSAPMSGPUBHDR WHERE MACHINENAME=:1 AND PUBSTATUS IN (2, 3) ORDER BY PROCESSID RATIONALE: SQL statement with SQL_ID "5gp0520qnhgrb" was executed 8463 times and had an average elapsed time of 0.17 seconds. FINDING 2: 46% impact (9713 seconds) ------------------------------------ Time spent on the CPU by the instance was responsible for a substantial part of database time. RECOMMENDATION 1: SQL Tuning, 15% benefit (3131 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "7x49avyr2au94". RELEVANT OBJECT: SQL statement with SQL_ID 7x49avyr2au94 and PLAN_HASH 343647695 select distinct T5086.BUSINESS_UNIT as c1, T5144.NAME as c2, T8160.CURRENCY_CD as c3, T8160.WO_RATE as c4, T8160.WORK_ORDER_ID as c5, T8160.JOB_TITLE as c6, case when T7363.PERIOD_END_DT >= TO_DATE('2011-03-25' , 'YYYY-MM-DD') - 7 then T7435.SP_TIME_QTY end as c7, T7435.SP_TIME_QTY as c8, T7435.DATE_UNDER_RPT as c9, T6648.DESCR as c10, T5090.DESCR as c11 from ( PS_SPA_TM_DTL_HRLY T7435 /* F_SPA_TM_DTL_HRLY */ left outer join ( ( ( ( ( ( PS_PRV_WO_TMAMT_VW T8160 /* F_SERVICES_MGMT */ left outer join PS_PRV_PROJECT_VW T5097 /* D_ACCT_PROJECT */ On T5097.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5097.PROJECT_ID = T8160.PROJECT_ID) left outer join PS_PRV_WORDERREC T10388 /* F_PRV_WORDERREC */ On T8160.BUSINESS_UNIT = T10388.BUSINESS_UNIT and T8160.WORK_ORDER_ID = T10388.WORK_ORDER_ID) left outer join PSOPRDEFN T9903 /* F_PRV_EXPENSE_APPROVER_EMAIL */ On T8160.SP_EXP_APPROVER = T9903.OPRID) left outer join PSOPRDEFN T9789 /* F_PRV_TIMESHEET_APPROVER_EMAIL */ On T8160.TE_APPROVER = T9789.OPRID) left outer join ( PS_PRV_DEPT_SP_VW T5090 /* D_DEPARTMENT */ left outer join PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ On T5090.BUSINESS_UNIT = T10522.BUSINESS_UNIT) On T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5090.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5090.DEPTID = T8160.DEPTID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T6648.LOCATION = T8160.LOCATION) On T7435.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7435.WORK_ORDER_ID = T8160.WORK_ORDER_ID) left outer join ( ( ( ( PS_SPA_TIME_DTL T7396 /* F_SPA_TIME_DTL */ left outer join PS_PRV_ACTVT_SP_VW T7684 /* D_ACTIVITY */ On T7396.BUSINESS_UNIT = T7684.BUSINESS_UNIT and T7396.ACTIVITY_ID = T7684.ACTIVITY_ID) left outer join PS_PRV_TIMHIST_APR T11231 /* F_PRV_TIMHIST_APR_SERVICES */ On T7396.BUSINESS_UNIT = T11231.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11231.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11231.WORK_ORDER_ID and T7396.LINE_NBR = T11231.LINE_NBR) left outer join PS_PRV_TIMHIST_DEN T11238 /* F_PRV_TIMHIST_DEN_SERVICES */ On T7396.BUSINESS_UNIT = T11238.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11238.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11238.WORK_ORDER_ID and T7396.LINE_NBR = T11238.LINE_NBR) left outer join PSOPRDEFN T8824 /* D_OPRDEFN */ On T8824.OPRID = T11231.NAME_DISPLAY) On T7396.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7396.WORK_ORDER_ID = T8160.WORK_ORDER_ID, PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , ( PS_PRV_SRVCP_SP_VW T5144 /* D_SERVICE_PROVIDER */ left outer join PS_PRV_SERVICE_PRV T11106 /* F_PRV_SERVICE_PRV */ On T5144.PERSON_ID = T11106.PERSON_ID) left outer join PS_PRV_HIRE_VW T11109 /* F_PRV_HIRE_VW */ On T5144.PERSON_ID = T11109.PERSON_ID, PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ , PS_SPA_TIME_HDR T7363 /* F_SPA_TIME_HDR */ , PS_PRV_TIME_STS_VW T8381 /* D_XLAT_TIME_HDR_STATUS */ where ( T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT ) and ( T5086.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T9898.PERSON_ID and T7363.BUSINESS_UNIT = T7396.BUSINESS_UNIT and T7363.TIME_SHEET_ID = T7396.TIME_SHEET_ID and T5086.BUSINESS_UNIT = 'HMINC' and T5090.BUSINESS_UNIT = 'HMINC' and T7363.TIME_HDR_STATUS = T8381.FIELDVALUE and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7435.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7396.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = 'HMINC' and T8381.XLATLONGNAME = 'Approved' and T9898.PRIMARY_FLAG = 'Y' and T10522.OPRID = 'Bacon' ) order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 RATIONALE: SQL statement with SQL_ID "7x49avyr2au94" was executed 1 times and had an average elapsed time of 3080 seconds. RATIONALE: Average CPU used per execution was 2969 seconds. RECOMMENDATION 2: SQL Tuning, 14% benefit (3060 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "gahwjc3hzw9ch". RELEVANT OBJECT: SQL statement with SQL_ID gahwjc3hzw9ch and PLAN_HASH 343647695 select distinct T5086.BUSINESS_UNIT as c1, T5144.NAME as c2, T8160.CURRENCY_CD as c3, T8160.WO_RATE as c4, T8160.WORK_ORDER_ID as c5, T8160.JOB_TITLE as c6, case when T7363.PERIOD_END_DT >= TO_DATE('2011-03-25' , 'YYYY-MM-DD') then T7435.SP_TIME_QTY end as c7, T7435.SP_TIME_QTY as c8, T7435.DATE_UNDER_RPT as c9, T6648.DESCR as c10, T5090.DESCR as c11 from ( PS_SPA_TM_DTL_HRLY T7435 /* F_SPA_TM_DTL_HRLY */ left outer join ( ( ( ( ( ( PS_PRV_WO_TMAMT_VW T8160 /* F_SERVICES_MGMT */ left outer join PS_PRV_PROJECT_VW T5097 /* D_ACCT_PROJECT */ On T5097.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5097.PROJECT_ID = T8160.PROJECT_ID) left outer join PS_PRV_WORDERREC T10388 /* F_PRV_WORDERREC */ On T8160.BUSINESS_UNIT = T10388.BUSINESS_UNIT and T8160.WORK_ORDER_ID = T10388.WORK_ORDER_ID) left outer join PSOPRDEFN T9903 /* F_PRV_EXPENSE_APPROVER_EMAIL */ On T8160.SP_EXP_APPROVER = T9903.OPRID) left outer join PSOPRDEFN T9789 /* F_PRV_TIMESHEET_APPROVER_EMAIL */ On T8160.TE_APPROVER = T9789.OPRID) left outer join ( PS_PRV_DEPT_SP_VW T5090 /* D_DEPARTMENT */ left outer join PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ On T5090.BUSINESS_UNIT = T10522.BUSINESS_UNIT) On T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5090.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5090.DEPTID = T8160.DEPTID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T6648.LOCATION = T8160.LOCATION) On T7435.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7435.WORK_ORDER_ID = T8160.WORK_ORDER_ID) left outer join ( ( ( ( PS_SPA_TIME_DTL T7396 /* F_SPA_TIME_DTL */ left outer join PS_PRV_ACTVT_SP_VW T7684 /* D_ACTIVITY */ On T7396.BUSINESS_UNIT = T7684.BUSINESS_UNIT and T7396.ACTIVITY_ID = T7684.ACTIVITY_ID) left outer join PS_PRV_TIMHIST_APR T11231 /* F_PRV_TIMHIST_APR_SERVICES */ On T7396.BUSINESS_UNIT = T11231.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11231.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11231.WORK_ORDER_ID and T7396.LINE_NBR = T11231.LINE_NBR) left outer join PS_PRV_TIMHIST_DEN T11238 /* F_PRV_TIMHIST_DEN_SERVICES */ On T7396.BUSINESS_UNIT = T11238.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11238.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11238.WORK_ORDER_ID and T7396.LINE_NBR = T11238.LINE_NBR) left outer join PSOPRDEFN T8824 /* D_OPRDEFN */ On T8824.OPRID = T11231.NAME_DISPLAY) On T7396.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7396.WORK_ORDER_ID = T8160.WORK_ORDER_ID, PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , ( PS_PRV_SRVCP_SP_VW T5144 /* D_SERVICE_PROVIDER */ left outer join PS_PRV_SERVICE_PRV T11106 /* F_PRV_SERVICE_PRV */ On T5144.PERSON_ID = T11106.PERSON_ID) left outer join PS_PRV_HIRE_VW T11109 /* F_PRV_HIRE_VW */ On T5144.PERSON_ID = T11109.PERSON_ID, PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ , PS_SPA_TIME_HDR T7363 /* F_SPA_TIME_HDR */ , PS_PRV_TIME_STS_VW T8381 /* D_XLAT_TIME_HDR_STATUS */ where ( T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT ) and ( T5086.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T9898.PERSON_ID and T7363.BUSINESS_UNIT = T7396.BUSINESS_UNIT and T7363.TIME_SHEET_ID = T7396.TIME_SHEET_ID and T5086.BUSINESS_UNIT = 'HMINC' and T5090.BUSINESS_UNIT = 'HMINC' and T7363.TIME_HDR_STATUS = T8381.FIELDVALUE and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7435.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7396.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = 'HMINC' and T8381.XLATLONGNAME = 'Approved' and T9898.PRIMARY_FLAG = 'Y' and T10522.OPRID = 'Bacon' ) order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 RATIONALE: SQL statement with SQL_ID "gahwjc3hzw9ch" was executed 1 times and had an average elapsed time of 3019 seconds. RATIONALE: Average CPU used per execution was 2940 seconds. RECOMMENDATION 3: SQL Tuning, 7.1% benefit (1514 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "5gp0520qnhgrb". RELEVANT OBJECT: SQL statement with SQL_ID 5gp0520qnhgrb and PLAN_HASH 3481439974 SELECT DISTINCT (PROCESSID) FROM PSAPMSGPUBHDR WHERE MACHINENAME=:1 AND PUBSTATUS IN (2, 3) ORDER BY PROCESSID RATIONALE: SQL statement with SQL_ID "5gp0520qnhgrb" was executed 8463 times and had an average elapsed time of 0.17 seconds. RATIONALE: Average CPU used per execution was 0.17 seconds. RECOMMENDATION 4: SQL Tuning, 5.6% benefit (1182 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "2my8a2bjvdmyz". RELEVANT OBJECT: SQL statement with SQL_ID 2my8a2bjvdmyz and PLAN_HASH 1442702467 SELECT R.PRCSINSTANCE ,R.ORIGPRCSINSTANCE ,R.RECURORIGPRCSINST,R.MAINJOBINSTANCE ,R.PRCSJOBSEQ ,R.PRCSJOBNAME ,R.PRCSNAME ,R.PRCSTYPE ,R.RECURNAME ,R.TIMEZONE FROM PSPRCSQUE R ,PS_PRCSRECUR S WHERE ((R.RUNSTATUS IN ('9', '17') AND S.INITIATEWHEN = 1) OR (R.RUNSTATUS IN ('3','6', '7', '9','10','12', '17') AND S.INITIATEWHEN = 0)) AND R.INITIATEDNEXT = 0 AND R.RUNLOCATION = '2' AND R.RECURNAME <> ' ' AND R.PRCSJOBSEQ = 0 AND R.RECURNAME = S.RECURNAME RATIONALE: SQL statement with SQL_ID "2my8a2bjvdmyz" was executed 8483 times and had an average elapsed time of 0.13 seconds. RATIONALE: Average CPU used per execution was 0.13 seconds. RECOMMENDATION 5: SQL Tuning, 5.3% benefit (1128 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "dusqjj9mrv969". RELEVANT OBJECT: SQL statement with SQL_ID dusqjj9mrv969 and PLAN_HASH 3791553982 select distinct T5086.BUSINESS_UNIT as c1, T5144.NAME as c2, T8160.CURRENCY_CD as c3, T8160.WO_RATE as c4, T8160.WORK_ORDER_ID as c5, T8160.JOB_TITLE as c6, case when T7363.PERIOD_END_DT >= TO_DATE('2011-03-25' , 'YYYY-MM-DD') - 7 then T7435.SP_TIME_QTY end as c7, T7435.SP_TIME_QTY as c8, T7435.DATE_UNDER_RPT as c9, T6648.DESCR as c10, T5090.DESCR as c11 from ( PS_SPA_TM_DTL_HRLY T7435 /* F_SPA_TM_DTL_HRLY */ left outer join ( ( ( ( ( ( PS_PRV_WO_TMAMT_VW T8160 /* F_SERVICES_MGMT */ left outer join PS_PRV_PROJECT_VW T5097 /* D_ACCT_PROJECT */ On T5097.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5097.PROJECT_ID = T8160.PROJECT_ID) left outer join PS_PRV_WORDERREC T10388 /* F_PRV_WORDERREC */ On T8160.BUSINESS_UNIT = T10388.BUSINESS_UNIT and T8160.WORK_ORDER_ID = T10388.WORK_ORDER_ID) left outer join PSOPRDEFN T9903 /* F_PRV_EXPENSE_APPROVER_EMAIL */ On T8160.SP_EXP_APPROVER = T9903.OPRID) left outer join PSOPRDEFN T9789 /* F_PRV_TIMESHEET_APPROVER_EMAIL */ On T8160.TE_APPROVER = T9789.OPRID) left outer join ( PS_PRV_DEPT_SP_VW T5090 /* D_DEPARTMENT */ left outer join PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ On T5090.BUSINESS_UNIT = T10522.BUSINESS_UNIT) On T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5090.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5090.DEPTID = T8160.DEPTID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T6648.LOCATION = T8160.LOCATION) On T7435.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7435.WORK_ORDER_ID = T8160.WORK_ORDER_ID) left outer join ( ( ( ( PS_SPA_TIME_DTL T7396 /* F_SPA_TIME_DTL */ left outer join PS_PRV_ACTVT_SP_VW T7684 /* D_ACTIVITY */ On T7396.BUSINESS_UNIT = T7684.BUSINESS_UNIT and T7396.ACTIVITY_ID = T7684.ACTIVITY_ID) left outer join PS_PRV_TIMHIST_APR T11231 /* F_PRV_TIMHIST_APR_SERVICES */ On T7396.BUSINESS_UNIT = T11231.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11231.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11231.WORK_ORDER_ID and T7396.LINE_NBR = T11231.LINE_NBR) left outer join PS_PRV_TIMHIST_DEN T11238 /* F_PRV_TIMHIST_DEN_SERVICES */ On T7396.BUSINESS_UNIT = T11238.BUSINESS_UNIT and T7396.TIME_SHEET_ID = T11238.TIME_SHEET_ID and T7396.WORK_ORDER_ID = T11238.WORK_ORDER_ID and T7396.LINE_NBR = T11238.LINE_NBR) left outer join PSOPRDEFN T8824 /* D_OPRDEFN */ On T8824.OPRID = T11231.NAME_DISPLAY) On T7396.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T7396.WORK_ORDER_ID = T8160.WORK_ORDER_ID, PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , ( PS_PRV_SRVCP_SP_VW T5144 /* D_SERVICE_PROVIDER */ left outer join PS_PRV_SERVICE_PRV T11106 /* F_PRV_SERVICE_PRV */ On T5144.PERSON_ID = T11106.PERSON_ID) left outer join PS_PRV_HIRE_VW T11109 /* F_PRV_HIRE_VW */ On T5144.PERSON_ID = T11109.PERSON_ID, PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ , PS_SPA_TIME_HDR T7363 /* F_SPA_TIME_HDR */ , PS_PRV_TIME_STS_VW T8381 /* D_XLAT_TIME_HDR_STATUS */ where ( T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T8160.WO_CONID and T5144.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5144.BUSINESS_UNIT = T10522.BUSINESS_UNIT ) and ( T5086.BUSINESS_UNIT = T8160.BUSINESS_UNIT and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5144.PERSON_ID = T9898.PERSON_ID and T7363.BUSINESS_UNIT = T7396.BUSINESS_UNIT and T7363.TIME_SHEET_ID = T7396.TIME_SHEET_ID and T5086.BUSINESS_UNIT = 'HMINC' and T5090.BUSINESS_UNIT = 'HMINC' and T5090.DESCR = 'Act Instlln-Ldrshp-Sr Prod-CAS' and T7363.TIME_HDR_STATUS = T8381.FIELDVALUE and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7435.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T7396.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T8160.BUSINESS_UNIT = 'HMINC' and T8381.XLATLONGNAME = 'Approved' and T9898.PRIMARY_FLAG = 'Y' and T10522.OPRID = 'Bacon' ) order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 RATIONALE: SQL statement with SQL_ID "dusqjj9mrv969" was executed 2 times and had an average elapsed time of 561 seconds. RATIONALE: Average CPU used per execution was 553 seconds. FINDING 3: 37% impact (7908 seconds) ------------------------------------ RMAN I/O activity had a significant impact on the throughput of the I/O subsystem. RECOMMENDATION 1: DB Configuration, 37% benefit (7908 seconds) ACTION: Consider slowing down RMAN activity, or scheduling RMAN jobs when user activity is lower. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: The throughput of the I/O subsystem was significantly lower than expected. (14% impact [2884 seconds]) SYMPTOM: Wait class "User I/O" was consuming significant database time. (37% impact [7908 seconds]) FINDING 4: 16% impact (3372 seconds) ------------------------------------ The SGA was inadequately sized, causing additional I/O or hard parses. RECOMMENDATION 1: DB Configuration, 16% benefit (3372 seconds) ACTION: Increase the size of the SGA by setting the parameter "sga_target" to 3584 M. ADDITIONAL INFORMATION: The value of parameter "sga_target" was "2048 M" during the analysis period. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (37% impact [7908 seconds]) SYMPTOM: Hard parsing of SQL statements was consuming significant database time. (3.7% impact [777 seconds]) FINDING 5: 14% impact (2884 seconds) ------------------------------------ The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 14% benefit (2884 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 73 K per second for reads and 8.3 K per second for writes. The average response time for single block reads was 14 milliseconds. RECOMMENDATION 2: Host Configuration, 5.5% benefit (1177 seconds) ACTION: The performance of file +U06/orasp/psindex.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "+U06/orasp/psindex.dbf" RATIONALE: The average response time for single block reads for this file was 17 milliseconds. RECOMMENDATION 3: Host Configuration, 1.2% benefit (259 seconds) ACTION: The performance of file +U04/orasp/sysaux01.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "+U04/orasp/sysaux01.dbf" RATIONALE: The average response time for single block reads for this file was 24 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (37% impact [7908 seconds]) FINDING 6: 3.5% impact (739 seconds) ------------------------------------ The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. RECOMMENDATION 1: DB Configuration, 0.1% benefit (22 seconds) ACTION: Increase the size of the PGA by setting the value of parameter "pga_aggregate_target" to 192 M. ADDITIONAL INFORMATION: The value of parameter "pga_aggregate_target" was "24 M" during the analysis period. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (37% impact [7908 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL 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. Wait class "Network" was not consuming significant database time. Session connect and disconnect calls were 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. SYS@ORASP1> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Real Application Clusters and Real Application Testing options