DETAILED ADDM REPORT FOR TASK 'TASK_162106' WITH ID 162106
          ----------------------------------------------------------
 
              Analysis Period: from 09-JUN-2011 22:00 to 10-JUN-2011 05:00
         Database ID/Instance: 4127787440/2
      Database/Instance Names: CAPGENT/capgent2
                    Host Name: rac2.capgent.com
             Database Version: 10.2.0.4.0
               Snapshot Range: from 21453 to 21460
                Database Time: 186876 seconds
        Average Database Load: 7.4 active sessions
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
FINDING 1: 40% impact (73873 seconds)
-------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in 
additional hard parses which were consuming significant database time.
 
   RECOMMENDATION 1: Application Analysis, 40% benefit (73873 seconds)
      ACTION: Investigate application logic for possible use of bind variables 
         instead of literals.
      ACTION: Alternatively, you may set the parameter "cursor_sharing" to 
         "force".
      RATIONALE: At least 25 SQL statements with PLAN_HASH_VALUE 2437727371 
         were found to be using literals. Look in V$SQL for examples of such 
         SQL statements.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Hard parsing of SQL statements was consuming significant 
               database time. (75% impact [140323 seconds])
         SYMPTOM: Contention for latches related to the shared pool was 
                  consuming significant database time. (95% impact [177553 
                  seconds])
            INFO: Waits for "library cache lock" amounted to 86% of database 
                  time.
                  Waits for "cursor: pin S wait on X" amounted to 8% of 
                  database time.
            SYMPTOM: Wait class "Concurrency" was consuming significant 
                     database time. (95% impact [177565 seconds])
 
FINDING 2: 39% impact (73357 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
 
   RECOMMENDATION 1: SQL Tuning, 11% benefit (20988 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "a13kmf3wukbux" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID a13kmf3wukbux and 
         PLAN_HASH 889916849
         select csthdr0_.CS_ID as CS1_22_, csthdr0_.CS_PID as CS2_22_, 
         csthdr0_.CS_NAME as CS3_22_, csthdr0_.CS_ACTIVE as CS4_22_, 
         csthdr0_.CS_ALIAS as CS5_22_, csthdr0_.CS_REF1 as CS6_22_, 
         csthdr0_.CS_REF2 as CS7_22_, csthdr0_.CS_OREF as CS8_22_, 
         csthdr0_.CS_OSUPP as CS9_22_, csthdr0_.CS_CDSCT as CS10_22_, 
         csthdr0_.CS_DELM as CS11_22_, csthdr0_.CS_SLPN as CS12_22_, 
         csthdr0_.CS_RESP as CS13_22_, csthdr0_.CS_VATC as CS14_22_, 
         csthdr0_.CS_CURR as CS15_22_, csthdr0_.CS_XCHTPE as CS16_22_, 
         csthdr0_.CS_DTEFRM as CS17_22_, csthdr0_.CS_PAYR as CS18_22_, 
         csthdr0_.CS_STATS as CS19_22_, csthdr0_.CS_CRLM1 as CS20_22_, 
         csthdr0_.CS_CRLM2 as CS21_22_, csthdr0_.CS_CRLM3 as CS22_22_, 
         csthdr0_.CS_STOP as CS23_22_, csthdr0_.CS_OSINAMT as CS24_22_, 
         csthdr0_.CS_ODINAMT as CS25_22_, csthdr0_.CS_LINVDTE as CS26_22_, 
         csthdr0_.CS_EDTE as CS27_22_, csthdr0_.CS_SUMINV as CS28_22_, 
         csthdr0_.CS_INTINV as CS29_22_, csthdr0_.CS_INTRUL as CS30_22_, 
         csthdr0_.CS_CRATING as CS31_22_, csthdr0_.CS_CREVIEW as CS32_22_, 
         csthdr0_.CS_CRUPDTE as CS33_22_, csthdr0_.CS_DB as CS34_22_, 
         csthdr0_.CS_LANG as CS35_22_, csthdr0_.CS_PSTID as CS36_22_, 
         csthdr0_.CS_REGION as CS37_22_, csthdr0_.CS_RMKS as CS38_22_, 
         csthdr0_.CS_STMT as CS39_22_, csthdr0_.CS_CRTDBY as CS40_22_, 
         csthdr0_.CS_MODBY as CS41_22_, csthdr0_.CS_CRTDDT as CS42_22_, 
         csthdr0_.CS_MODDT as CS43_22_, csthdr0_.CS_CSGID as CS44_22_, 
         csthdr0_.EN_ID as EN45_22_, csthdr0_.STE_ID as STE46_22_, 
         csthdr0_.CS_STR1 as CS47_22_, csthdr0_.CS_STR2 as CS48_22_, 
         csthdr0_.CS_STR3 as CS49_22_, csthdr0_.CS_STR4 as CS50_22_, 
         csthdr0_.CS_STR5 as CS51_22_, csthdr0_.CS_STR6 as CS52_22_, 
         csthdr0_.CS_STR7 as CS53_22_, csthdr0_.CS_STR8 as CS54_22_, 
         csthdr0_.CS_STR9 as CS55_22_, csthdr0_.CS_STR10 as CS56_22_, 
         csthdr0_.CS_STR11 as CS57_22_, csthdr0_.CS_STR12 as CS58_22_, 
         csthdr0_.CS_STR13 as CS59_22_, csthdr0_.CS_STR14 as CS60_22_, 
         csthdr0_.CS_STR15 as CS61_22_, csthdr0_.CS_STR16 as CS62_22_, 
         csthdr0_.CS_STR17 as CS63_22_, csthdr0_.CS_STR18 as CS64_22_, 
         csthdr0_.CS_STR19 as CS65_22_, csthdr0_.CS_STR20 as CS66_22_, 
         csthdr0_.CS_STR21 as CS67_22_, csthdr0_.CS_STR22 as CS68_22_, 
         csthdr0_.CS_STR23 as CS69_22_, csthdr0_.CS_STR24 as CS70_22_, 
         csthdr0_.CS_STR25 as CS71_22_, csthdr0_.CS_NUM1 as CS72_22_, 
         csthdr0_.CS_NUM2 as CS73_22_, csthdr0_.CS_NUM3 as CS74_22_, 
         csthdr0_.CS_NUM4 as CS75_22_, csthdr0_.CS_NUM5 as CS76_22_, 
         csthdr0_.CS_NUM6 as CS77_22_, csthdr0_.CS_NUM7 as CS78_22_, 
         csthdr0_.CS_NUM8 as CS79_22_, csthdr0_.CS_NUM9 as CS80_22_, 
         csthdr0_.CS_NUM10 as CS81_22_, csthdr0_.CS_NUM11 as CS82_22_, 
         csthdr0_.CS_NUM12 as CS83_22_, csthdr0_.CS_NUM13 as CS84_22_, 
         csthdr0_.CS_NUM14 as CS85_22_, csthdr0_.CS_NUM15 as CS86_22_, 
         csthdr0_.CS_NUM16 as CS87_22_, csthdr0_.CS_NUM17 as CS88_22_, 
         csthdr0_.CS_NUM18 as CS89_22_, csthdr0_.CS_NUM19 as CS90_22_, 
         csthdr0_.CS_NUM20 as CS91_22_, csthdr0_.CS_DATE1 as CS92_22_, 
         csthdr0_.CS_DATE2 as CS93_22_, csthdr0_.CS_DATE3 as CS94_22_, 
         csthdr0_.CS_DATE4 as CS95_22_, csthdr0_.CS_DATE5 as CS96_22_, 
         csthdr0_.CS_DATE6 as CS97_22_, csthdr0_.CS_DATE7 as CS98_22_, 
         csthdr0_.CS_DATE8 as CS99_22_, csthdr0_.CS_DATE9 as CS100_22_, 
         csthdr0_.CS_DATE10 as CS101_22_, csthdr0_.CS_TERMS as CS102_22_, 
         csthdr0_.CS_KEYMARK as CS103_22_, csthdr0_.AID as AID22_, 
         csthdr0_.CS_IND as CS105_22_, csthdr0_.CS_URL as CS106_22_, 
         csthdr0_.AC_TYPE as AC107_22_, csthdr0_.CS_AVGDP as CS108_22_, 
         csthdr0_.CS_BKT12 as CS109_22_, csthdr0_.CS_ERAM as CS110_22_, 
         csthdr0_.rowid as rowid111_22_, csthdr0_.PY_CODE as PY112_22_, 
         csthdr0_.PC_ID as PC113_22_, csthdr0_.CY_ID as CY114_22_, 
         csthdr0_.PR_ID as PR115_22_, csthdr0_.CS_TYPE as CS116_22_, 
         csthdr0_.DT_ID as DT117_22_ from CST_HDR csthdr0_, CSTPE_HDR 
         cstpehdr1_ where csthdr0_.CS_TYPE=cstpehdr1_.CS_TYPE and 
         cstpehdr1_.CS_NAME=:1 and csthdr0_.EN_ID=:2 and (csthdr0_.CS_REF1 in 
         (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48))
      RATIONALE: SQL statement with SQL_ID "a13kmf3wukbux" was executed 1 
         times and had an average elapsed time of 20988 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 99% of the database time spent in 
         processing the SQL statement with SQL_ID "a13kmf3wukbux".
 
   RECOMMENDATION 2: SQL Tuning, 8.9% benefit (16630 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "1x965w3jzwdn3" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 1x965w3jzwdn3 and 
         PLAN_HASH 2950666639
         select * from ( select attmtslog0_.AT_DESC as col_0_0_, 
         attmtslog0_.AT_URL as col_1_0_, attmtslog0_.AT_CRTDBY as col_2_0_, 
         attmtslog0_.AT_CRTDDT as col_3_0_, attmtslog0_.ALOG_ID as col_4_0_, 
         attmtslog0_.AT_IVID as col_5_0_ from ATTMTS_LOG attmtslog0_, INV_TXN 
         invtxn1_ where attmtslog0_.AT_IVID=invtxn1_.IV_ID and 
         attmtslog0_.AT_DATE=invtxn1_.IV_DATE and invtxn1_.CS_ID='1509248' and 
         invtxn1_.IV_TYPE='Account' ) where rownum <= :1
      RATIONALE: SQL statement with SQL_ID "1x965w3jzwdn3" was executed 2 
         times and had an average elapsed time of 8315 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 51% of the database time spent in 
         processing the SQL statement with SQL_ID "1x965w3jzwdn3".
      RATIONALE: Waiting for event "cursor: pin S wait on X" in wait class 
         "Concurrency" accounted for 48% of the database time spent in 
         processing the SQL statement with SQL_ID "1x965w3jzwdn3".
 
   RECOMMENDATION 3: SQL Tuning, 8.5% benefit (15829 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "8shfgpn9xdqhc" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 8shfgpn9xdqhc and 
         PLAN_HASH 790217091
         select * from ( select stshdr3_.ST_NAME as col_0_0_, 
         invtxn0_.IV_GPINVNO as col_1_0_, invtxn0_.IV_DATE as col_2_0_, 
         invtxn0_.IV_DUEDATE as col_3_0_, invtxn0_.IV_TLAMT as col_4_0_, 
         accrtxn4_.AR_OUTAMT as col_5_0_, invtxn0_.IV_STR6 as col_6_0_, 
         trunc(current_date-invtxn0_.IV_DUEDATE) as col_7_0_, 
         attmtslogs2_.AT_URL as col_8_0_, cmtslogs1_.CM_TEXT as col_9_0_, 
         invtxn0_.IV_STR4 as col_10_0_, invtxn0_.IV_STR5 as col_11_0_, 
         invtxn0_.IV_ID as col_12_0_, invtxn0_.CS_ID as col_13_0_, 
         attmtslogs2_.AT_DESC as col_14_0_, attmtslogs2_.ALOG_ID as col_15_0_, 
         csthdr5_.CS_NAME as col_16_0_, csthdr5_.CS_REF1 as col_17_0_, 
         stshdr3_.ST_ICON as col_18_0_ from INV_TXN invtxn0_, CMTS_LOG 
         cmtslogs1_, ATTMTS_LOG attmtslogs2_, STS_HDR stshdr3_, ACCR_TXN 
         accrtxn4_, CST_HDR csthdr5_ where 
         invtxn0_.IV_ID=cmtslogs1_.CM_IVID(+) and 
         invtxn0_.IV_DATE=cmtslogs1_.CM_IVDATE(+) and ( 
         cmtslogs1_.cm_iscurr(+)=1) and invtxn0_.IV_ID=attmtslogs2_.AT_IVID(+) 
         and invtxn0_.IV_DATE=attmtslogs2_.AT_DATE(+) and 
         (attmtslogs2_.AT_ISINV(+)=1) and invtxn0_.IV_STID=stshdr3_.ST_ID(+) 
         and invtxn0_.IV_ID=accrtxn4_.IV_ID and 
         invtxn0_.IV_DATE=accrtxn4_.IV_DATE and invtxn0_.CS_ID=csthdr5_.CS_ID 
         and (invtxn0_.EN_ID in (4300)) and invtxn0_.CS_ID='3619829' and 
         invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 order by 
         invtxn0_.IV_DUEDATE ASC ) where rownum <= :1
      RATIONALE: SQL statement with SQL_ID "8shfgpn9xdqhc" was executed 2 
         times and had an average elapsed time of 7913 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 50% of the database time spent in 
         processing the SQL statement with SQL_ID "8shfgpn9xdqhc".
      RATIONALE: Waiting for event "cursor: pin S wait on X" in wait class 
         "Concurrency" accounted for 49% of the database time spent in 
         processing the SQL statement with SQL_ID "8shfgpn9xdqhc".
 
   RECOMMENDATION 4: SQL Tuning, 5.6% benefit (10527 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "8zhhvajkdysc5" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 8zhhvajkdysc5 and 
         PLAN_HASH 2661797612
         select csthdr0_.CS_ID as CS1_22_, csthdr0_.CS_PID as CS2_22_, 
         csthdr0_.CS_NAME as CS3_22_, csthdr0_.CS_ACTIVE as CS4_22_, 
         csthdr0_.CS_ALIAS as CS5_22_, csthdr0_.CS_REF1 as CS6_22_, 
         csthdr0_.CS_REF2 as CS7_22_, csthdr0_.CS_OREF as CS8_22_, 
         csthdr0_.CS_OSUPP as CS9_22_, csthdr0_.CS_CDSCT as CS10_22_, 
         csthdr0_.CS_DELM as CS11_22_, csthdr0_.CS_SLPN as CS12_22_, 
         csthdr0_.CS_RESP as CS13_22_, csthdr0_.CS_VATC as CS14_22_, 
         csthdr0_.CS_CURR as CS15_22_, csthdr0_.CS_XCHTPE as CS16_22_, 
         csthdr0_.CS_DTEFRM as CS17_22_, csthdr0_.CS_PAYR as CS18_22_, 
         csthdr0_.CS_STATS as CS19_22_, csthdr0_.CS_CRLM1 as CS20_22_, 
         csthdr0_.CS_CRLM2 as CS21_22_, csthdr0_.CS_CRLM3 as CS22_22_, 
         csthdr0_.CS_STOP as CS23_22_, csthdr0_.CS_OSINAMT as CS24_22_, 
         csthdr0_.CS_ODINAMT as CS25_22_, csthdr0_.CS_LINVDTE as CS26_22_, 
         csthdr0_.CS_EDTE as CS27_22_, csthdr0_.CS_SUMINV as CS28_22_, 
         csthdr0_.CS_INTINV as CS29_22_, csthdr0_.CS_INTRUL as CS30_22_, 
         csthdr0_.CS_CRATING as CS31_22_, csthdr0_.CS_CREVIEW as CS32_22_, 
         csthdr0_.CS_CRUPDTE as CS33_22_, csthdr0_.CS_DB as CS34_22_, 
         csthdr0_.CS_LANG as CS35_22_, csthdr0_.CS_PSTID as CS36_22_, 
         csthdr0_.CS_REGION as CS37_22_, csthdr0_.CS_RMKS as CS38_22_, 
         csthdr0_.CS_STMT as CS39_22_, csthdr0_.CS_CRTDBY as CS40_22_, 
         csthdr0_.CS_MODBY as CS41_22_, csthdr0_.CS_CRTDDT as CS42_22_, 
         csthdr0_.CS_MODDT as CS43_22_, csthdr0_.CS_CSGID as CS44_22_, 
         csthdr0_.EN_ID as EN45_22_, csthdr0_.STE_ID as STE46_22_, 
         csthdr0_.CS_STR1 as CS47_22_, csthdr0_.CS_STR2 as CS48_22_, 
         csthdr0_.CS_STR3 as CS49_22_, csthdr0_.CS_STR4 as CS50_22_, 
         csthdr0_.CS_STR5 as CS51_22_, csthdr0_.CS_STR6 as CS52_22_, 
         csthdr0_.CS_STR7 as CS53_22_, csthdr0_.CS_STR8 as CS54_22_, 
         csthdr0_.CS_STR9 as CS55_22_, csthdr0_.CS_STR10 as CS56_22_, 
         csthdr0_.CS_STR11 as CS57_22_, csthdr0_.CS_STR12 as CS58_22_, 
         csthdr0_.CS_STR13 as CS59_22_, csthdr0_.CS_STR14 as CS60_22_, 
         csthdr0_.CS_STR15 as CS61_22_, csthdr0_.CS_STR16 as CS62_22_, 
         csthdr0_.CS_STR17 as CS63_22_, csthdr0_.CS_STR18 as CS64_22_, 
         csthdr0_.CS_STR19 as CS65_22_, csthdr0_.CS_STR20 as CS66_22_, 
         csthdr0_.CS_STR21 as CS67_22_, csthdr0_.CS_STR22 as CS68_22_, 
         csthdr0_.CS_STR23 as CS69_22_, csthdr0_.CS_STR24 as CS70_22_, 
         csthdr0_.CS_STR25 as CS71_22_, csthdr0_.CS_NUM1 as CS72_22_, 
         csthdr0_.CS_NUM2 as CS73_22_, csthdr0_.CS_NUM3 as CS74_22_, 
         csthdr0_.CS_NUM4 as CS75_22_, csthdr0_.CS_NUM5 as CS76_22_, 
         csthdr0_.CS_NUM6 as CS77_22_, csthdr0_.CS_NUM7 as CS78_22_, 
         csthdr0_.CS_NUM8 as CS79_22_, csthdr0_.CS_NUM9 as CS80_22_, 
         csthdr0_.CS_NUM10 as CS81_22_, csthdr0_.CS_NUM11 as CS82_22_, 
         csthdr0_.CS_NUM12 as CS83_22_, csthdr0_.CS_NUM13 as CS84_22_, 
         csthdr0_.CS_NUM14 as CS85_22_, csthdr0_.CS_NUM15 as CS86_22_, 
         csthdr0_.CS_NUM16 as CS87_22_, csthdr0_.CS_NUM17 as CS88_22_, 
         csthdr0_.CS_NUM18 as CS89_22_, csthdr0_.CS_NUM19 as CS90_22_, 
         csthdr0_.CS_NUM20 as CS91_22_, csthdr0_.CS_DATE1 as CS92_22_, 
         csthdr0_.CS_DATE2 as CS93_22_, csthdr0_.CS_DATE3 as CS94_22_, 
         csthdr0_.CS_DATE4 as CS95_22_, csthdr0_.CS_DATE5 as CS96_22_, 
         csthdr0_.CS_DATE6 as CS97_22_, csthdr0_.CS_DATE7 as CS98_22_, 
         csthdr0_.CS_DATE8 as CS99_22_, csthdr0_.CS_DATE9 as CS100_22_, 
         csthdr0_.CS_DATE10 as CS101_22_, csthdr0_.CS_TERMS as CS102_22_, 
         csthdr0_.CS_KEYMARK as CS103_22_, csthdr0_.AID as AID22_, 
         csthdr0_.CS_IND as CS105_22_, csthdr0_.CS_URL as CS106_22_, 
         csthdr0_.AC_TYPE as AC107_22_, csthdr0_.CS_AVGDP as CS108_22_, 
         csthdr0_.CS_BKT12 as CS109_22_, csthdr0_.CS_ERAM as CS110_22_, 
         csthdr0_.rowid as rowid111_22_, csthdr0_.PY_CODE as PY112_22_, 
         csthdr0_.PC_ID as PC113_22_, csthdr0_.CY_ID as CY114_22_, 
         csthdr0_.PR_ID as PR115_22_, csthdr0_.CS_TYPE as CS116_22_, 
         csthdr0_.DT_ID as DT117_22_ from CST_HDR csthdr0_, CSTPE_HDR 
         cstpehdr1_ where csthdr0_.CS_TYPE=cstpehdr1_.CS_TYPE and 
         cstpehdr1_.CS_NAME=:1 and csthdr0_.EN_ID=:2 and (csthdr0_.CS_REF1 in 
         (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , 
         :50 , :51 , :52))
      RATIONALE: SQL statement with SQL_ID "8zhhvajkdysc5" was executed 25 
         times and had an average elapsed time of 421 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 99% of the database time spent in 
         processing the SQL statement with SQL_ID "8zhhvajkdysc5".
 
   RECOMMENDATION 5: SQL Tuning, 5% benefit (9384 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "70bkj69raa1kf" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 70bkj69raa1kf and 
         PLAN_HASH 790217091
         select * from ( select stshdr3_.ST_NAME as col_0_0_, 
         invtxn0_.IV_GPINVNO as col_1_0_, invtxn0_.IV_STR11 as col_2_0_, 
         invtxn0_.IV_DATE as col_3_0_, invtxn0_.IV_TLAMT as col_4_0_, 
         invtxn0_.IV_DUEDATE as col_5_0_, 
         trunc(current_date-invtxn0_.IV_DUEDATE) as col_6_0_, 
         accrtxn4_.AR_OUTAMT as col_7_0_, attmtslogs2_.AT_URL as col_8_0_, 
         cmtslogs1_.CM_TEXT as col_9_0_, invtxn0_.IV_STR4 as col_10_0_, 
         invtxn0_.IV_STR5 as col_11_0_, invtxn0_.IV_ASSGNTO as col_12_0_, 
         invtxn0_.IV_ID as col_13_0_, invtxn0_.CS_ID as col_14_0_, 
         attmtslogs2_.AT_DESC as col_15_0_, attmtslogs2_.ALOG_ID as col_16_0_, 
         csthdr5_.CS_NAME as col_17_0_, csthdr5_.CS_REF1 as col_18_0_, 
         stshdr3_.ST_ICON as col_19_0_ from INV_TXN invtxn0_, CMTS_LOG 
         cmtslogs1_, ATTMTS_LOG attmtslogs2_, STS_HDR stshdr3_, ACCR_TXN 
         accrtxn4_, CST_HDR csthdr5_ where 
         invtxn0_.IV_ID=cmtslogs1_.CM_IVID(+) and 
         invtxn0_.IV_DATE=cmtslogs1_.CM_IVDATE(+) and ( 
         cmtslogs1_.cm_iscurr(+)=1) and invtxn0_.IV_ID=attmtslogs2_.AT_IVID(+) 
         and invtxn0_.IV_DATE=attmtslogs2_.AT_DATE(+) and 
         (attmtslogs2_.AT_ISINV(+)=1) and invtxn0_.IV_STID=stshdr3_.ST_ID(+) 
         and invtxn0_.IV_ID=accrtxn4_.IV_ID and 
         invtxn0_.IV_DATE=accrtxn4_.IV_DATE and invtxn0_.CS_ID=csthdr5_.CS_ID 
         and (invtxn0_.EN_ID in (4060)) and invtxn0_.CS_ID='1581848' and 
         invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 order by 
         invtxn0_.IV_DUEDATE ASC ) where rownum <= :1
      RATIONALE: SQL statement with SQL_ID "70bkj69raa1kf" was executed 1 
         times and had an average elapsed time of 9384 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 99% of the database time spent in 
         processing the SQL statement with SQL_ID "70bkj69raa1kf".
 
FINDING 3: 26% impact (48753 seconds)
-------------------------------------
Hard parsing SQL statements that encountered parse errors was consuming 
significant database time.
 
   RECOMMENDATION 1: Application Analysis, 26% benefit (48753 seconds)
      ACTION: Investigate application logic to eliminate parse errors.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Hard parsing of SQL statements was consuming significant 
               database time. (75% impact [140323 seconds])
         SYMPTOM: Contention for latches related to the shared pool was 
                  consuming significant database time. (95% impact [177553 
                  seconds])
            INFO: Waits for "library cache lock" amounted to 86% of database 
                  time.
                  Waits for "cursor: pin S wait on X" amounted to 8% of 
                  database time.
            SYMPTOM: Wait class "Concurrency" was consuming significant 
                     database time. (95% impact [177565 seconds])
 
FINDING 4: 11% impact (21337 seconds)
-------------------------------------
PL/SQL execution consumed significant database time.
 
   RECOMMENDATION 1: SQL Tuning, 12% benefit (23288 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 23288 seconds.
      RATIONALE: Waiting for event "library cache lock" in wait class 
         "Concurrency" accounted for 91% of the database time spent in 
         processing the SQL statement with SQL_ID "b6usrg82hwsa3".
      RATIONALE: Average time spent in PL/SQL execution was 21337 seconds.
 
FINDING 5: 9.5% impact (17698 seconds)
--------------------------------------
SQL statements with the same text were not shared because of cursor 
environment mismatch. This resulted in additional hard parses which were 
consuming significant database time.
 
   RECOMMENDATION 1: Application Analysis, 9.5% benefit (17698 seconds)
      ACTION: Look for top reason for cursor environment mismatch in 
         V$SQL_SHARED_CURSOR.
 
   ADDITIONAL INFORMATION:
      Common causes of environment mismatch are session NLS settings, SQL 
      trace settings and optimizer parameters.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Hard parsing of SQL statements was consuming significant 
               database time. (75% impact [140323 seconds])
         SYMPTOM: Contention for latches related to the shared pool was 
                  consuming significant database time. (95% impact [177553 
                  seconds])
            INFO: Waits for "library cache lock" amounted to 86% of database 
                  time.
                  Waits for "cursor: pin S wait on X" amounted to 8% of 
                  database time.
            SYMPTOM: Wait class "Concurrency" was consuming significant 
                     database time. (95% impact [177565 seconds])
 
FINDING 6: 8.5% impact (15958 seconds)
--------------------------------------
Soft parsing of SQL statements was consuming significant database time.
 
   RECOMMENDATION 1: Application Analysis, 8.5% benefit (15958 seconds)
      ACTION: Investigate application logic to keep open the frequently used 
         cursors. Note that cursors are closed by both cursor close calls and 
         session disconnects.
 
   RECOMMENDATION 2: DB Configuration, 8.5% benefit (15958 seconds)
      ACTION: Consider increasing the maximum number of open cursors a session 
         can have by increasing the value of parameter "open_cursors".
      ACTION: Consider increasing the session cursor cache size by increasing 
         the value of parameter "session_cached_cursors".
      RATIONALE: The value of parameter "open_cursors" was "2000" during the 
         analysis period.
      RATIONALE: The value of parameter "session_cached_cursors" was "20" 
         during the analysis period.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Contention for latches related to the shared pool was consuming 
               significant database time. (95% impact [177553 seconds])
         INFO: Waits for "library cache lock" amounted to 86% of database 
               time.
               Waits for "cursor: pin S wait on X" amounted to 8% of database 
               time.
         SYMPTOM: Wait class "Concurrency" was consuming significant database 
                  time. (95% impact [177565 seconds])
 
FINDING 7: 7% impact (13119 seconds)
------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
 
   RECOMMENDATION 1: DB Configuration, 7% benefit (13119 seconds)
      ACTION: Increase the size of the SGA by setting the parameter 
         "sga_target" to 4800 M.
 
   ADDITIONAL INFORMATION:
      The value of parameter "sga_target" was "3840 M" during the analysis 
      period.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Hard parsing of SQL statements was consuming significant 
               database time. (75% impact [140323 seconds])
         SYMPTOM: Contention for latches related to the shared pool was 
                  consuming significant database time. (95% impact [177553 
                  seconds])
            INFO: Waits for "library cache lock" amounted to 86% of database 
                  time.
                  Waits for "cursor: pin S wait on X" amounted to 8% of 
                  database time.
            SYMPTOM: Wait class "Concurrency" was consuming significant 
                     database time. (95% impact [177565 seconds])
      SYMPTOM: Wait class "User I/O" was consuming significant database time. 
               (3.1% impact [5841 seconds])
 
FINDING 8: 1.4% impact (2677 seconds)
-------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
 
   RECOMMENDATION 1: Host Configuration, 1.4% benefit (2677 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 138 K per second for reads and 13 K per second for 
         writes. The average response time for single block reads was 18 
         milliseconds.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "User I/O" was consuming significant database time. 
               (3.1% impact [5841 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 "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.
 
The database's maintenance windows were active during 100% 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.