DETAILED ADDM REPORT FOR TASK 'TASK_67280' WITH ID 67280 -------------------------------------------------------- Analysis Period: from 28-MAR-2011 09:15 to 29-MAR-2011 03: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 38 to 56 Database Time: 7996 seconds Average Database Load: .1 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 50% impact (3972 seconds) ------------------------------------ SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 18% benefit (1459 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "fvsgud1zganhv". RELEVANT OBJECT: SQL statement with SQL_ID fvsgud1zganhv and PLAN_HASH 2014500434 select distinct D1.c8 as c1, D1.c7 as c2, D1.c9 as c3, D1.c6 as c4, D1.c9 as c5, D1.c5 as c6, D1.c4 as c7, D1.c10 as c8, D1.c11 as c9, D1.c3 as c10, D1.c12 as c11, D1.c13 as c12, D1.c2 as c13, D1.c1 as c14, D1.c14 as c15, D1.c15 as c16 from (select distinct T6684.VENDOR_ID as c1, T6684.SOURCING_DATE as c2, T5107.OPRDEFNDESC as c3, T6684.END_DATE as c4, T6684.START_DATE as c5, T6684.DATETIME_STAMP as c6, T6684.SP_REQ_ID as c7, T5086.BUSINESS_UNIT as c8, T6274.XLATLONGNAME as c9, T5204.PROJ_ROLE as c10, T11112.ROLE_TYPE as c11, T6648.LOCATION as c12, T6648.DESCR as c13, T5516.DESCR as c14, T5101.NAME1 as c15 from PS_PRV_XLATITEM_VW T6274 /* D_XLAT_SP_REQ_STATUS */ , PS_PRV_REQVSTR_VW T5107 /* D_REQUESTOR */ , ( ( ( ( PS_PRV_BID_LINE_VW T6684 /* F_SPF_BID_LINE */ left outer join PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ On T6684.SRVC_PROVIDER_ID = T9898.PERSON_ID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T6648.LOCATION = T6684.LOCATION) left outer join PS_PRV_VNDR_SP_VW T5101 /* D_SUPPLIER */ On T5101.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5101.VENDOR_ID = T6684.VENDOR_ID) left outer join PS_PRV_SRVCS_SP_VW T5204 /* D_SERVICE */ On T5204.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5204.PROJ_ROLE = T6684.PROJ_ROLE) left outer join PS_PRV_SRVC_SP_VW T11112 /* D_SERVICE_TYPE */ On T6684.BUSINESS_UNIT = T11112.BUSINESS_UNIT and T6684.ROLE_TYPE = T11112.ROLE_TYPE, PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ , PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , PS_PRV_BIDACTN_VW T5516 /* D_BID_ACTION */ where ( T5107.REQUESTOR_ID = T6684.REQUESTOR_ID and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5516.BID_ACTION_TYPE = T6684.BID_ACTION_TYPE and T5516.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.CODE = T6684.CODE and T6274.FIELDNAME = 'SP_REQ_STATUS' and T6274.FIELDVALUE = T6684.SP_REQ_STATUS and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5101.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5204.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T6684.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T10522.OPRID = 'ELWOLPERN' and T10522.BUSINESS_UNIT = T11112.BUSINESS_UNIT and (T5086.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and (T5516.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) ) ) D1 order by c1, c2, c3, c4, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16 RATIONALE: SQL statement with SQL_ID "fvsgud1zganhv" was executed 1 times and had an average elapsed time of 1457 seconds. RECOMMENDATION 2: SQL Tuning, 12% benefit (936 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "aga2f1k5uuqrd". RELEVANT OBJECT: SQL statement with SQL_ID aga2f1k5uuqrd and PLAN_HASH 2014500434 select distinct D1.c8 as c1, D1.c7 as c2, D1.c9 as c3, D1.c6 as c4, D1.c9 as c5, D1.c5 as c6, D1.c4 as c7, D1.c10 as c8, D1.c11 as c9, D1.c3 as c10, D1.c12 as c11, D1.c13 as c12, D1.c2 as c13, D1.c1 as c14, D1.c14 as c15, D1.c15 as c16 from (select distinct T6684.VENDOR_ID as c1, T6684.SOURCING_DATE as c2, T5107.OPRDEFNDESC as c3, T6684.END_DATE as c4, T6684.START_DATE as c5, T6684.DATETIME_STAMP as c6, T6684.SP_REQ_ID as c7, T5086.BUSINESS_UNIT as c8, T6274.XLATLONGNAME as c9, T5204.PROJ_ROLE as c10, T11112.ROLE_TYPE as c11, T6648.LOCATION as c12, T6648.DESCR as c13, T5516.DESCR as c14, T5101.NAME1 as c15 from PS_PRV_XLATITEM_VW T6274 /* D_XLAT_SP_REQ_STATUS */ , PS_PRV_REQVSTR_VW T5107 /* D_REQUESTOR */ , ( ( ( ( PS_PRV_BID_LINE_VW T6684 /* F_SPF_BID_LINE */ left outer join PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ On T6684.SRVC_PROVIDER_ID = T9898.PERSON_ID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T6648.LOCATION = T6684.LOCATION) left outer join PS_PRV_VNDR_SP_VW T5101 /* D_SUPPLIER */ On T5101.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5101.VENDOR_ID = T6684.VENDOR_ID) left outer join PS_PRV_SRVCS_SP_VW T5204 /* D_SERVICE */ On T5204.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5204.PROJ_ROLE = T6684.PROJ_ROLE) left outer join PS_PRV_SRVC_SP_VW T11112 /* D_SERVICE_TYPE */ On T6684.BUSINESS_UNIT = T11112.BUSINESS_UNIT and T6684.ROLE_TYPE = T11112.ROLE_TYPE, PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ , PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , PS_PRV_BIDACTN_VW T5516 /* D_BID_ACTION */ where ( T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5107.REQUESTOR_ID = T6684.REQUESTOR_ID and T5516.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5516.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.BID_ACTION_TYPE = T6684.BID_ACTION_TYPE and T5516.CODE = T6684.CODE and T6274.FIELDNAME = 'SP_REQ_STATUS' and T6274.FIELDVALUE = T6684.SP_REQ_STATUS and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5101.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5204.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T6684.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T10522.OPRID = 'ELWOLPERN' and T10522.BUSINESS_UNIT = T11112.BUSINESS_UNIT and (T5086.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and (T5516.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and T6684.DATETIME_STAMP between TO_DATE('2010-10-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2011-03-29 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ) ) D1 order by c1, c2, c3, c4, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16 RATIONALE: SQL statement with SQL_ID "aga2f1k5uuqrd" was executed 1 times and had an average elapsed time of 935 seconds. RECOMMENDATION 3: SQL Tuning, 9.1% benefit (726 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 ACTION: Investigate the SQL statement with SQL_ID "2my8a2bjvdmyz" for possible performance improvements. 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 2870 times and had an average elapsed time of 0.13 seconds. RECOMMENDATION 4: SQL Tuning, 9% benefit (720 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 4185 times and had an average elapsed time of 0.17 seconds. RECOMMENDATION 5: SQL Tuning, 6.1% benefit (486 seconds) ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc ( ) RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1 times and had an average elapsed time of 486 seconds. FINDING 2: 48% impact (3863 seconds) ------------------------------------ Time spent on the CPU by the instance was responsible for a substantial part of database time. RECOMMENDATION 1: SQL Tuning, 18% benefit (1459 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "fvsgud1zganhv". RELEVANT OBJECT: SQL statement with SQL_ID fvsgud1zganhv and PLAN_HASH 2014500434 select distinct D1.c8 as c1, D1.c7 as c2, D1.c9 as c3, D1.c6 as c4, D1.c9 as c5, D1.c5 as c6, D1.c4 as c7, D1.c10 as c8, D1.c11 as c9, D1.c3 as c10, D1.c12 as c11, D1.c13 as c12, D1.c2 as c13, D1.c1 as c14, D1.c14 as c15, D1.c15 as c16 from (select distinct T6684.VENDOR_ID as c1, T6684.SOURCING_DATE as c2, T5107.OPRDEFNDESC as c3, T6684.END_DATE as c4, T6684.START_DATE as c5, T6684.DATETIME_STAMP as c6, T6684.SP_REQ_ID as c7, T5086.BUSINESS_UNIT as c8, T6274.XLATLONGNAME as c9, T5204.PROJ_ROLE as c10, T11112.ROLE_TYPE as c11, T6648.LOCATION as c12, T6648.DESCR as c13, T5516.DESCR as c14, T5101.NAME1 as c15 from PS_PRV_XLATITEM_VW T6274 /* D_XLAT_SP_REQ_STATUS */ , PS_PRV_REQVSTR_VW T5107 /* D_REQUESTOR */ , ( ( ( ( PS_PRV_BID_LINE_VW T6684 /* F_SPF_BID_LINE */ left outer join PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ On T6684.SRVC_PROVIDER_ID = T9898.PERSON_ID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T6648.LOCATION = T6684.LOCATION) left outer join PS_PRV_VNDR_SP_VW T5101 /* D_SUPPLIER */ On T5101.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5101.VENDOR_ID = T6684.VENDOR_ID) left outer join PS_PRV_SRVCS_SP_VW T5204 /* D_SERVICE */ On T5204.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5204.PROJ_ROLE = T6684.PROJ_ROLE) left outer join PS_PRV_SRVC_SP_VW T11112 /* D_SERVICE_TYPE */ On T6684.BUSINESS_UNIT = T11112.BUSINESS_UNIT and T6684.ROLE_TYPE = T11112.ROLE_TYPE, PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ , PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , PS_PRV_BIDACTN_VW T5516 /* D_BID_ACTION */ where ( T5107.REQUESTOR_ID = T6684.REQUESTOR_ID and T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5516.BID_ACTION_TYPE = T6684.BID_ACTION_TYPE and T5516.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.CODE = T6684.CODE and T6274.FIELDNAME = 'SP_REQ_STATUS' and T6274.FIELDVALUE = T6684.SP_REQ_STATUS and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5101.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5204.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T6684.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T10522.OPRID = 'ELWOLPERN' and T10522.BUSINESS_UNIT = T11112.BUSINESS_UNIT and (T5086.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and (T5516.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) ) ) D1 order by c1, c2, c3, c4, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16 RATIONALE: SQL statement with SQL_ID "fvsgud1zganhv" was executed 1 times and had an average elapsed time of 1457 seconds. RATIONALE: Average CPU used per execution was 1456 seconds. RECOMMENDATION 2: SQL Tuning, 12% benefit (936 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "aga2f1k5uuqrd". RELEVANT OBJECT: SQL statement with SQL_ID aga2f1k5uuqrd and PLAN_HASH 2014500434 select distinct D1.c8 as c1, D1.c7 as c2, D1.c9 as c3, D1.c6 as c4, D1.c9 as c5, D1.c5 as c6, D1.c4 as c7, D1.c10 as c8, D1.c11 as c9, D1.c3 as c10, D1.c12 as c11, D1.c13 as c12, D1.c2 as c13, D1.c1 as c14, D1.c14 as c15, D1.c15 as c16 from (select distinct T6684.VENDOR_ID as c1, T6684.SOURCING_DATE as c2, T5107.OPRDEFNDESC as c3, T6684.END_DATE as c4, T6684.START_DATE as c5, T6684.DATETIME_STAMP as c6, T6684.SP_REQ_ID as c7, T5086.BUSINESS_UNIT as c8, T6274.XLATLONGNAME as c9, T5204.PROJ_ROLE as c10, T11112.ROLE_TYPE as c11, T6648.LOCATION as c12, T6648.DESCR as c13, T5516.DESCR as c14, T5101.NAME1 as c15 from PS_PRV_XLATITEM_VW T6274 /* D_XLAT_SP_REQ_STATUS */ , PS_PRV_REQVSTR_VW T5107 /* D_REQUESTOR */ , ( ( ( ( PS_PRV_BID_LINE_VW T6684 /* F_SPF_BID_LINE */ left outer join PS_SPB_PERS_EMAIL T9898 /* D_SERVICE_PROVD_EMAIL */ On T6684.SRVC_PROVIDER_ID = T9898.PERSON_ID) left outer join PS_PRV_LCTN_SP_VW T6648 /* D_LOCATION */ On T6648.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T6648.LOCATION = T6684.LOCATION) left outer join PS_PRV_VNDR_SP_VW T5101 /* D_SUPPLIER */ On T5101.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5101.VENDOR_ID = T6684.VENDOR_ID) left outer join PS_PRV_SRVCS_SP_VW T5204 /* D_SERVICE */ On T5204.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5204.PROJ_ROLE = T6684.PROJ_ROLE) left outer join PS_PRV_SRVC_SP_VW T11112 /* D_SERVICE_TYPE */ On T6684.BUSINESS_UNIT = T11112.BUSINESS_UNIT and T6684.ROLE_TYPE = T11112.ROLE_TYPE, PS_SEC_BU_OPR T10522 /* D_SEC_BU_OPR */ , PS_SP_BU_SP_NONVW T5086 /* D_BUSINESS_UNIT */ , PS_PRV_BIDACTN_VW T5516 /* D_BID_ACTION */ where ( T5086.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5107.REQUESTOR_ID = T6684.REQUESTOR_ID and T5516.BUSINESS_UNIT = T6684.BUSINESS_UNIT and T5516.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5516.BID_ACTION_TYPE = T6684.BID_ACTION_TYPE and T5516.CODE = T6684.CODE and T6274.FIELDNAME = 'SP_REQ_STATUS' and T6274.FIELDVALUE = T6684.SP_REQ_STATUS and T6648.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5101.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T5204.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T6684.BUSINESS_UNIT = T10522.BUSINESS_UNIT and T10522.OPRID = 'ELWOLPERN' and T10522.BUSINESS_UNIT = T11112.BUSINESS_UNIT and (T5086.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and (T5516.BUSINESS_UNIT in ('INI01', 'INI02', 'INI03')) and T6684.DATETIME_STAMP between TO_DATE('2010-10-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2011-03-29 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ) ) D1 order by c1, c2, c3, c4, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16 RATIONALE: SQL statement with SQL_ID "aga2f1k5uuqrd" was executed 1 times and had an average elapsed time of 935 seconds. RATIONALE: Average CPU used per execution was 929 seconds. RECOMMENDATION 3: SQL Tuning, 9.1% benefit (726 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 ACTION: Investigate the SQL statement with SQL_ID "2my8a2bjvdmyz" for possible performance improvements. 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 2870 times and had an average elapsed time of 0.13 seconds. RATIONALE: Average CPU used per execution was 0.13 seconds. RECOMMENDATION 4: SQL Tuning, 9% benefit (720 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 4185 times and had an average elapsed time of 0.17 seconds. RATIONALE: Average CPU used per execution was 0.17 seconds. RECOMMENDATION 5: SQL Tuning, 4.6% benefit (370 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "4muka6gxc3paw". RELEVANT OBJECT: SQL statement with SQL_ID 4muka6gxc3paw and PLAN_HASH 1436092116 SELECT DISTINCT (PROCESSID) FROM PSAPMSGSUBCON WHERE MACHINENAME=:1 AND SUBCONSTATUS IN (2, 3) ORDER BY PROCESSID RATIONALE: SQL statement with SQL_ID "4muka6gxc3paw" was executed 4148 times and had an average elapsed time of 0.089 seconds. RATIONALE: Average CPU used per execution was 0.089 seconds. FINDING 3: 9.3% impact (745 seconds) ------------------------------------ The SGA was inadequately sized, causing additional I/O or hard parses. RECOMMENDATION 1: DB Configuration, 9.3% benefit (745 seconds) ACTION: Increase the size of the SGA by setting the parameter "sga_target" to 3072 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. (28% impact [2207 seconds]) SYMPTOM: Hard parsing of SQL statements was consuming significant database time. (7.7% impact [616 seconds]) FINDING 4: 4.9% impact (391 seconds) ------------------------------------ The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 4.9% benefit (391 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 90 K per second for reads and 11 K per second for writes. The average response time for single block reads was 12 milliseconds. RECOMMENDATION 2: Host Configuration, 2.7% benefit (219 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 14 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (28% impact [2207 seconds]) FINDING 5: 4.3% impact (346 seconds) ------------------------------------ Individual database segments responsible for significant user I/O wait were found. RECOMMENDATION 1: Segment Tuning, 2.4% benefit (188 seconds) ACTION: Investigate application logic involving I/O on INDEX "SYS.RECYCLEBIN$_TS" with object id 704. RELEVANT OBJECT: database object with id 704 RATIONALE: The I/O usage statistics for the object are: 0 full object scans, 0 physical reads, 0 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "gpv3kb4n2f2q1" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID gpv3kb4n2f2q1 SELECT COUNT(FILE#) FROM V$DATAFILE_HEADER WHERE RECOVER ='YES' OR ERROR IS NOT NULL RECOMMENDATION 2: Segment Tuning, 2% benefit (158 seconds) ACTION: Run "Segment Advisor" on TABLE "SYSADM.PSWORKLIST" with object id 38062. RELEVANT OBJECT: database object with id 38062 ACTION: Investigate application logic involving I/O on TABLE "SYSADM.PSWORKLIST" with object id 38062. RELEVANT OBJECT: database object with id 38062 RATIONALE: The I/O usage statistics for the object are: 1 full object scans, 46494 physical reads, 1 physical writes and 0 direct reads. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (28% impact [2207 seconds]) FINDING 6: 3.9% impact (311 seconds) ------------------------------------ Individual SQL statements responsible for significant user I/O wait were found. RECOMMENDATION 1: SQL Tuning, 3.9% benefit (312 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "gpv3kb4n2f2q1". RELEVANT OBJECT: SQL statement with SQL_ID gpv3kb4n2f2q1 and PLAN_HASH 2441418372 SELECT COUNT(FILE#) FROM V$DATAFILE_HEADER WHERE RECOVER ='YES' OR ERROR IS NOT NULL RATIONALE: SQL statement with SQL_ID "gpv3kb4n2f2q1" was executed 71 times and had an average elapsed time of 4.4 seconds. RATIONALE: Average time spent in User I/O wait events per execution was 4.3 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (28% impact [2207 seconds]) FINDING 7: 2.3% impact (187 seconds) ------------------------------------ The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. RECOMMENDATION 1: DB Configuration, 1.3% benefit (103 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. (28% impact [2207 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. 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 45% 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.