DETAILED ADDM REPORT FOR TASK 'TASK_129415' WITH ID 129415
----------------------------------------------------------
Analysis Period: 20-JAN-2011 from 04:00:12 to 11:00:47
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 18075 to 18082
Database Time: 172378 seconds
Average Database Load: 6.8 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (172389 seconds)
---------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 100% benefit (172389 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 3840 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "3072 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (36% impact [62389 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(19% impact [32792 seconds])
FINDING 2: 39% impact (67412 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 9.8% benefit (16891 seconds)
ACTION: Investigate the SQL statement with SQL_ID "62f98228n4yjp" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 62f98228n4yjp and
PLAN_HASH 3024242220
update sts_log set st_iscurr=0 where iv_id in(select doc_id from
pyttxn_det where py_id ='bf0302272d8980a3012d9fa6dae3001d') and
st_iscurr=1
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "62f98228n4yjp".
RECOMMENDATION 2: SQL Tuning, 8.2% benefit (14126 seconds)
ACTION: Investigate the SQL statement with SQL_ID "25rnvg1q4z9a0" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 25rnvg1q4z9a0 and
PLAN_HASH 47488921
update sts_log set st_iscurr=0 where log_id
in(442679,442680,442681,442682,442683,442684,442685,442686,442687,442
688)
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "25rnvg1q4z9a0".
RECOMMENDATION 3: SQL Tuning, 7.7% benefit (13204 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"dcnp1pu6hd5fs".
RELEVANT OBJECT: SQL statement with SQL_ID dcnp1pu6hd5fs and
PLAN_HASH 3177380161
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
csthdr1_.CS_ID as CS1_22_1_, accrtxn2_.IV_ID as IV1_1_2_,
accrtxn2_.IV_DATE as IV2_1_2_, invtxn0_.IV_PINVID as IV3_36_0_,
invtxn0_.IV_SBID as IV4_36_0_, invtxn0_.IV_CURR as IV5_36_0_,
invtxn0_.IV_CHGS as IV6_36_0_, invtxn0_.IV_TLAMT as IV7_36_0_,
invtxn0_.IV_SHPADD as IV8_36_0_, invtxn0_.IV_BLNO as IV9_36_0_,
invtxn0_.IV_SHMODE as IV10_36_0_, invtxn0_.IV_CARR as IV11_36_0_,
invtxn0_.IV_BILLADD as IV12_36_0_, invtxn0_.IV_CRTRMS as IV13_36_0_,
invtxn0_.IV_TERMS as IV14_36_0_, invtxn0_.IV_ATTDTLS as IV15_36_0_,
invtxn0_.IV_TEXT as IV16_36_0_, invtxn0_.IV_BTCHDTE as IV17_36_0_,
invtxn0_.IV_ADVPAID as IV18_36_0_, invtxn0_.IV_ADVADJ as IV19_36_0_,
invtxn0_.IV_PRNTD as IV20_36_0_, invtxn0_.IV_MODE as IV21_36_0_,
invtxn0_.IV_DUEDATE as IV22_36_0_, invtxn0_.IV_TARIFF as IV23_36_0_,
invtxn0_.IV_TXNDTE as IV24_36_0_, invtxn0_.IV_GPINVNO as IV25_36_0_,
invtxn0_.IV_CRTDBY as IV26_36_0_, invtxn0_.IV_MODBY as IV27_36_0_,
invtxn0_.IV_CRTDDT as IV28_36_0_, invtxn0_.IV_MODDT as IV29_36_0_,
invtxn0_.IV_CUSNO as IV30_36_0_, invtxn0_.IV_ASSGTPE as IV31_36_0_,
invtxn0_.IV_ASSGNTO as IV32_36_0_, invtxn0_.IV_STR1 as IV33_36_0_,
invtxn0_.IV_STR2 as IV34_36_0_, invtxn0_.IV_STR3 as IV35_36_0_,
invtxn0_.IV_STR4 as IV36_36_0_, invtxn0_.IV_STR5 as IV37_36_0_,
invtxn0_.IV_STR6 as IV38_36_0_, invtxn0_.IV_STR7 as IV39_36_0_,
invtxn0_.IV_STR8 as IV40_36_0_, invtxn0_.IV_STR9 as IV41_36_0_,
invtxn0_.IV_STR10 as IV42_36_0_, invtxn0_.IV_STR11 as IV43_36_0_,
invtxn0_.IV_STR12 as IV44_36_0_, invtxn0_.IV_STR13 as IV45_36_0_,
invtxn0_.IV_STR14 as IV46_36_0_, invtxn0_.IV_STR15 as IV47_36_0_,
invtxn0_.IV_STR16 as IV48_36_0_, invtxn0_.IV_STR17 as IV49_36_0_,
invtxn0_.IV_STR18 as IV50_36_0_, invtxn0_.IV_STR19 as IV51_36_0_,
invtxn0_.IV_STR20 as IV52_36_0_, invtxn0_.IV_STR21 as IV53_36_0_,
invtxn0_.IV_STR22 as IV54_36_0_, invtxn0_.IV_STR23 as IV55_36_0_,
invtxn0_.IV_STR24 as IV56_36_0_, invtxn0_.IV_STR25 as IV57_36_0_,
invtxn0_.IV_NUM1 as IV58_36_0_, invtxn0_.IV_NUM2 as IV59_36_0_,
invtxn0_.IV_NUM3 as IV60_36_0_, invtxn0_.IV_NUM4 as IV61_36_0_,
invtxn0_.IV_NUM5 as IV62_36_0_, invtxn0_.IV_NUM6 as IV63_36_0_,
invtxn0_.IV_NUM7 as IV64_36_0_, invtxn0_.IV_NUM8 as IV65_36_0_,
invtxn0_.IV_NUM9 as IV66_36_0_, invtxn0_.IV_NUM10 as IV67_36_0_,
invtxn0_.IV_DATE1 as IV68_36_0_, invtxn0_.IV_DATE2 as IV69_36_0_,
invtxn0_.IV_DATE3 as IV70_36_0_, invtxn0_.IV_DATE4 as IV71_36_0_,
invtxn0_.IV_DATE5 as IV72_36_0_, invtxn0_.IV_DATE6 as IV73_36_0_,
invtxn0_.IV_DATE8 as IV74_36_0_, invtxn0_.IV_DATE9 as IV75_36_0_,
invtxn0_.IV_DATE10 as IV76_36_0_, invtxn0_.IV_ORDNO as IV77_36_0_,
invtxn0_.IV_FOR as IV78_36_0_, invtxn0_.IV_REMTO as IV79_36_0_,
invtxn0_.IV_PRODCDE as IV80_36_0_, invtxn0_.IV_CNTRLNO as IV81_36_0_,
invtxn0_.IV_IONUM as IV82_36_0_, invtxn0_.IV_AUTHNO as IV83_36_0_,
invtxn0_.IV_DEPTNO as IV84_36_0_, invtxn0_.IV_LOC as IV85_36_0_,
invtxn0_.IV_CLOSED as IV86_36_0_, invtxn0_.TID as TID36_0_,
invtxn0_.AID as AID36_0_, invtxn0_.IV_SOURCE as IV89_36_0_,
invtxn0_.IV_RECTYPE as IV90_36_0_, invtxn0_.IV_SLSORDNO as
IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_, invtxn0_.IV_TXAMT as
IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_, invtxn0_.IV_BU as
IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_, invtxn0_.IV_SHCLASS as
IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_, invtxn0_.IV_PODATE as
IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_, invtxn0_.IV_BLDATEFRO
as IV101_36_0_, invtxn0_.IV_BLDATETO as IV102_36_0_,
invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as IV104_36_0_,
invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET as
IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_, csthdr1_.CS_PID as CS2_22_1_, csthdr1_.CS_NAME as
CS3_22_1_, csthdr1_.CS_ACTIVE as CS4_22_1_, csthdr1_.CS_ALIAS as
CS5_22_1_, csthdr1_.CS_REF1 as CS6_22_1_, csthdr1_.CS_REF2 as
CS7_22_1_, csthdr1_.CS_OREF as CS8_22_1_, csthdr1_.CS_OSUPP as
CS9_22_1_, csthdr1_.CS_CDSCT as CS10_22_1_, csthdr1_.CS_DELM as
CS11_22_1_, csthdr1_.CS_SLPN as CS12_22_1_, csthdr1_.CS_RESP as
CS13_22_1_, csthdr1_.CS_VATC as CS14_22_1_, csthdr1_.CS_CURR as
CS15_22_1_, csthdr1_.CS_XCHTPE as CS16_22_1_, csthdr1_.CS_DTEFRM as
CS17_22_1_, csthdr1_.CS_PAYR as CS18_22_1_, csthdr1_.CS_STATS as
CS19_22_1_, csthdr1_.CS_CRLM1 as CS20_22_1_, csthdr1_.CS_CRLM2 as
CS21_22_1_, csthdr1_.CS_CRLM3 as CS22_22_1_, csthdr1_.CS_STOP as
CS23_22_1_, csthdr1_.CS_OSINAMT as CS24_22_1_, csthdr1_.CS_ODINAMT as
CS25_22_1_, csthdr1_.CS_LINVDTE as CS26_22_1_, csthdr1_.CS_EDTE as
CS27_22_1_, csthdr1_.CS_SUMINV as CS28_22_1_, csthdr1_.CS_INTINV as
CS29_22_1_, csthdr1_.CS_INTRUL as CS30_22_1_, csthdr1_.CS_CRATING as
CS31_22_1_, csthdr1_.CS_CREVIEW as CS32_22_1_, csthdr1_.CS_CRUPDTE as
CS33_22_1_, csthdr1_.CS_DB as CS34_22_1_, csthdr1_.CS_LANG as
CS35_22_1_, csthdr1_.CS_PSTID as CS36_22_1_, csthdr1_.CS_REGION as
CS37_22_1_, csthdr1_.CS_RMKS as CS38_22_1_, csthdr1_.CS_STMT as
CS39_22_1_, csthdr1_.CS_CRTDBY as CS40_22_1_, csthdr1_.CS_MODBY as
CS41_22_1_, csthdr1_.CS_CRTDDT as CS42_22_1_, csthdr1_.CS_MODDT as
CS43_22_1_, csthdr1_.CS_CSGID as CS44_22_1_, csthdr1_.EN_ID as
EN45_22_1_, csthdr1_.STE_ID as STE46_22_1_, csthdr1_.CS_STR1 as
CS47_22_1_, csthdr1_.CS_STR2 as CS48_22_1_, csthdr1_.CS_STR3 as
CS49_22_1_, csthdr1_.CS_STR4 as CS50_22_1_, csthdr1_.CS_STR5 as
CS51_22_1_, csthdr1_.CS_STR6 as CS52_22_1_, csthdr1_.CS_STR7 as
CS53_22_1_, csthdr1_.CS_STR8 as CS54_22_1_, csthdr1_.CS_STR9 as
CS55_22_1_, csthdr1_.CS_STR10 as CS56_22_1_, csthdr1_.CS_STR11 as
CS57_22_1_, csthdr1_.CS_STR12 as CS58_22_1_, csthdr1_.CS_STR13 as
CS59_22_1_, csthdr1_.CS_STR14 as CS60_22_1_, csthdr1_.CS_STR15 as
CS61_22_1_, csthdr1_.CS_STR16 as CS62_22_1_, csthdr1_.CS_STR17 as
CS63_22_1_, csthdr1_.CS_STR18 as CS64_22_1_, csthdr1_.CS_STR19 as
CS65_22_1_, csthdr1_.CS_STR20 as CS66_22_1_, csthdr1_.CS_STR21 as
CS67_22_1_, csthdr1_.CS_STR22 as CS68_22_1_, csthdr1_.CS_STR23 as
CS69_22_1_, csthdr1_.CS_STR24 as CS70_22_1_, csthdr1_.CS_STR25 as
CS71_22_1_, csthdr1_.CS_NUM1 as CS72_22_1_, csthdr1_.CS_NUM2 as
CS73_22_1_, csthdr1_.CS_NUM3 as CS74_22_1_, csthdr1_.CS_NUM4 as
CS75_22_1_, csthdr1_.CS_NUM5 as CS76_22_1_, csthdr1_.CS_NUM6 as
CS77_22_1_, csthdr1_.CS_NUM7 as CS78_22_1_, csthdr1_.CS_NUM8 as
CS79_22_1_, csthdr1_.CS_NUM9 as CS80_22_1_, csthdr1_.CS_NUM10 as
CS81_22_1_, csthdr1_.CS_NUM11 as CS82_22_1_, csthdr1_.CS_NUM12 as
CS83_22_1_, csthdr1_.CS_NUM13 as CS84_22_1_, csthdr1_.CS_NUM14 as
CS85_22_1_, csthdr1_.CS_NUM15 as CS86_22_1_, csthdr1_.CS_NUM16 as
CS87_22_1_, csthdr1_.CS_NUM17 as CS88_22_1_, csthdr1_.CS_NUM18 as
CS89_22_1_, csthdr1_.CS_NUM19 as CS90_22_1_, csthdr1_.CS_NUM20 as
CS91_22_1_, csthdr1_.CS_DATE1 as CS92_22_1_, csthdr1_.CS_DATE2 as
CS93_22_1_, csthdr1_.CS_DATE3 as CS94_22_1_, csthdr1_.CS_DATE4 as
CS95_22_1_, csthdr1_.CS_DATE5 as CS96_22_1_, csthdr1_.CS_DATE6 as
CS97_22_1_, csthdr1_.CS_DATE7 as CS98_22_1_, csthdr1_.CS_DATE8 as
CS99_22_1_, csthdr1_.CS_DATE9 as CS100_22_1_, csthdr1_.CS_DATE10 as
CS101_22_1_, csthdr1_.CS_TERMS as CS102_22_1_, csthdr1_.CS_KEYMARK as
CS103_22_1_, csthdr1_.AID as AID22_1_, csthdr1_.CS_IND as
CS105_22_1_, csthdr1_.CS_URL as CS106_22_1_, csthdr1_.AC_TYPE as
AC107_22_1_, csthdr1_.CS_AVGDP as CS108_22_1_, csthdr1_.CS_BKT12 as
CS109_22_1_, csthdr1_.CS_ERAM as CS110_22_1_, csthdr1_.rowid as
rowid111_22_1_, csthdr1_.PY_CODE as PY112_22_1_, csthdr1_.PC_ID as
PC113_22_1_, csthdr1_.CY_ID as CY114_22_1_, csthdr1_.PR_ID as
PR115_22_1_, csthdr1_.CS_TYPE as CS116_22_1_, csthdr1_.DT_ID as
DT117_22_1_, accrtxn2_.SB_ID as SB3_1_2_, accrtxn2_.AR_BTHDTE as
AR4_1_2_, accrtxn2_.AR_PAYR as AR5_1_2_, accrtxn2_.AR_INVVNS as
AR6_1_2_, accrtxn2_.AR_INVVNO as AR7_1_2_, accrtxn2_.AR_DESC as
AR8_1_2_, accrtxn2_.AR_SLSPRN as AR9_1_2_, accrtxn2_.AR_CUSGRP as
AR10_1_2_, accrtxn2_.AR_XCHRTPE as AR11_1_2_, accrtxn2_.AR_XCHRTE as
AR12_1_2_, accrtxn2_.AR_FCURAMT as AR13_1_2_, accrtxn2_.AR_VATAMT as
AR14_1_2_, accrtxn2_.AR_CHGTPE as AR15_1_2_, accrtxn2_.AR_MRKUP as
AR16_1_2_, accrtxn2_.AR_FWUDTE as AR17_1_2_, accrtxn2_.AR_ACCDTE as
AR18_1_2_, accrtxn2_.ACC_CODE as ACC19_1_2_, accrtxn2_.AR_BANK as
AR20_1_2_, accrtxn2_.AR_CDSCT as AR21_1_2_, accrtxn2_.AR_PYTMTD as
AR22_1_2_, accrtxn2_.AR_RECCDE as AR23_1_2_, accrtxn2_.AR_RECDTE as
AR24_1_2_, accrtxn2_.AR_ADVCDE as AR25_1_2_, accrtxn2_.AR_PYREM as
AR26_1_2_, accrtxn2_.AR_LREMDTE as AR27_1_2_, accrtxn2_.AR_PTREMDTE
as AR28_1_2_, accrtxn2_.AR_STPBY as AR29_1_2_, accrtxn2_.AR_STPDTE as
AR30_1_2_, accrtxn2_.AR_NOPYTRM as AR31_1_2_, accrtxn2_.AR_INTINVB as
AR32_1_2_, accrtxn2_.AR_INTINVS as AR33_1_2_, accrtxn2_.AR_CINTAMT as
AR34_1_2_, accrtxn2_.AR_FLGCDE as AR35_1_2_, accrtxn2_.AR_GPINDTE as
AR36_1_2_, accrtxn2_.AR_TXT as AR37_1_2_, accrtxn2_.AR_DOCLNK as
AR38_1_2_, accrtxn2_.AR_DRTPRD as AR39_1_2_, accrtxn2_.AR_DRPRDTE as
AR40_1_2_, accrtxn2_.AR_PRODGRP as AR41_1_2_, accrtxn2_.AR_ORDNO as
AR42_1_2_, accrtxn2_.AR_TXNDTE as AR43_1_2_, accrtxn2_.AR_FRTTRM as
AR44_1_2_, accrtxn2_.AR_NBR as AR45_1_2_, accrtxn2_.AR_PRINT as
AR46_1_2_, accrtxn2_.AR_CRTDBY as AR47_1_2_, accrtxn2_.AR_MODBY as
AR48_1_2_, accrtxn2_.AR_CRTDDT as AR49_1_2_, accrtxn2_.AR_MODDT as
AR50_1_2_, accrtxn2_.EN_ID as EN51_1_2_, accrtxn2_.STE_ID as
STE52_1_2_, accrtxn2_.AR_AMT as AR53_1_2_, accrtxn2_.AR_OUTAMT as
AR54_1_2_, accrtxn2_.AR_STR1 as AR55_1_2_, accrtxn2_.AR_STR2 as
AR56_1_2_, accrtxn2_.AR_STR3 as AR57_1_2_, accrtxn2_.AR_STR4 as
AR58_1_2_, accrtxn2_.AR_STR5 as AR59_1_2_, accrtxn2_.AR_STR6 as
AR60_1_2_, accrtxn2_.AR_STR7 as AR61_1_2_, accrtxn2_.AR_STR8 as
AR62_1_2_, accrtxn2_.AR_STR9 as AR63_1_2_, accrtxn2_.AR_STR10 as
AR64_1_2_, accrtxn2_.AR_STR11 as AR65_1_2_, accrtxn2_.AR_STR12 as
AR66_1_2_, accrtxn2_.AR_STR13 as AR67_1_2_, accrtxn2_.AR_STR14 as
AR68_1_2_, accrtxn2_.AR_STR15 as AR69_1_2_, accrtxn2_.AR_NUM1 as
AR70_1_2_, accrtxn2_.AR_NUM2 as AR71_1_2_, accrtxn2_.AR_NUM3 as
AR72_1_2_, accrtxn2_.AR_NUM4 as AR73_1_2_, accrtxn2_.AR_NUM5 as
AR74_1_2_, accrtxn2_.AR_NUM6 as AR75_1_2_, accrtxn2_.AR_NUM7 as
AR76_1_2_, accrtxn2_.AR_NUM8 as AR77_1_2_, accrtxn2_.AR_NUM9 as
AR78_1_2_, accrtxn2_.AR_NUM10 as AR79_1_2_, accrtxn2_.AR_DATE1 as
AR80_1_2_, accrtxn2_.AR_DATE2 as AR81_1_2_, accrtxn2_.AR_DATE3 as
AR82_1_2_, accrtxn2_.AR_DATE4 as AR83_1_2_, accrtxn2_.AR_DATE5 as
AR84_1_2_, accrtxn2_.AR_DATE6 as AR85_1_2_, accrtxn2_.AR_DATE7 as
AR86_1_2_, accrtxn2_.AR_DATE8 as AR87_1_2_, accrtxn2_.AR_DATE9 as
AR88_1_2_, accrtxn2_.AR_DATE10 as AR89_1_2_, accrtxn2_.AR_PROMDTE as
AR90_1_2_, accrtxn2_.TID as TID1_2_, accrtxn2_.PY_TERMS as PY92_1_2_,
accrtxn2_.PR_ID as PR93_1_2_ from INV_TXN invtxn0_, CST_HDR csthdr1_,
ACCR_TXN accrtxn2_, CSTPE_HDR cstpehdr4_ where
invtxn0_.CS_ID=csthdr1_.CS_ID(+) and
invtxn0_.IV_ID=accrtxn2_.IV_ID(+) and
invtxn0_.IV_DATE=accrtxn2_.IV_DATE(+) and
csthdr1_.CS_TYPE=cstpehdr4_.CS_TYPE and (invtxn0_.CS_ID in (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 ,
:50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61
, :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 ,
:73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84
, :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 ,
:96 , :97 , :98 , :99 , :100)) and
invtxn0_.IV_TYPE=cstpehdr4_.CS_NAME and invtxn0_.IV_CLOSED=0
RATIONALE: SQL statement with SQL_ID "dcnp1pu6hd5fs" was executed 129
times and had an average elapsed time of 102 seconds.
RECOMMENDATION 4: SQL Tuning, 7.3% benefit (12635 seconds)
ACTION: Investigate the SQL statement with SQL_ID "acrx1pwub04n1" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID acrx1pwub04n1 and
PLAN_HASH 3467736873
update sts_log set st_iscurr=0 where log_id=442679
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "acrx1pwub04n1".
RECOMMENDATION 5: SQL Tuning, 6.1% benefit (10568 seconds)
ACTION: Investigate the SQL statement with SQL_ID "cvnfcdk2fnnsv" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID cvnfcdk2fnnsv
call ALL_ENTITY_AGE_CALCULATIONS ( )
RATIONALE: SQL statement with SQL_ID "cvnfcdk2fnnsv" was executed 1
times and had an average elapsed time of 10567 seconds.
FINDING 3: 34% impact (59063 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, 34% benefit (59063 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 737 SQL statements with PLAN_HASH_VALUE 3931611195
were found to be using literals. An example is SQL statement with
SQL_ID "8a8x1vkta76dw" .
RELEVANT OBJECT: SQL statement with SQL_ID 8a8x1vkta76dw and
PLAN_HASH 3931611195
UPDATE CST_AGEHDR SET CS_AgeBucket6=1261.92 WHERE CS_ID = 2396984
RATIONALE: At least 113 SQL statements with PLAN_HASH_VALUE 2437727371
were found to be using literals. Look in V$SQL for examples of such
SQL statements.
RATIONALE: At least 17 SQL statements with PLAN_HASH_VALUE 2919750023
were found to be using literals. An example is SQL statement with
SQL_ID "g68bf3cfj36nj" .
RELEVANT OBJECT: SQL statement with SQL_ID g68bf3cfj36nj and
PLAN_HASH 2919750023
select * from ( select invtxn1_.IV_GPINVNO as col_0_0_,
cmtslog0_.CM_TEXT as col_1_0_, cmtslog0_.CM_CRTDT as col_2_0_,
cmtslog0_.CLOG_ID as col_3_0_, invtxn1_.CS_ID as col_4_0_,
cmtslog0_.CM_IVID as col_5_0_, cmtslog0_.CM_IVDATE as col_6_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_ where
cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and (invtxn1_.EN_ID in (3910))
and invtxn1_.CS_ID='231445' and invtxn1_.IV_TYPE='Invoice' order by
cmtslog0_.CM_CRTDT DESC ) where rownum <= :1
RATIONALE: At least 17 SQL statements with PLAN_HASH_VALUE 2919750023
were found to be using literals. An example is SQL statement with
SQL_ID "du590dqku48xw" .
RELEVANT OBJECT: SQL statement with SQL_ID du590dqku48xw and
PLAN_HASH 2919750023
select * from ( select invtxn1_.IV_GPINVNO as col_0_0_,
cmtslog0_.CM_TEXT as col_1_0_, cmtslog0_.CM_CRTDT as col_2_0_,
cmtslog0_.CLOG_ID as col_3_0_, invtxn1_.CS_ID as col_4_0_,
cmtslog0_.CM_IVID as col_5_0_, cmtslog0_.CM_IVDATE as col_6_0_ from
CMTS_LOG cmtslog0_, INV_TXN invtxn1_ where
cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and (invtxn1_.EN_ID in (4060))
and invtxn1_.CS_ID='1411379' and invtxn1_.IV_TYPE='Invoice' order by
cmtslog0_.CM_CRTDT DESC ) where rownum <= :1
RATIONALE: At least 16 SQL statements with PLAN_HASH_VALUE 1686042556
were found to be using literals. An example is SQL statement with
SQL_ID "chpgh9wz6a3u9" .
RELEVANT OBJECT: SQL statement with SQL_ID chpgh9wz6a3u9 and
PLAN_HASH 1686042556
select * from ( select stshdr4_.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_, accrtxn5_.AR_OUTAMT as col_4_0_,
trunc(current_date-invtxn0_.IV_DUEDATE) as col_5_0_,
attmtslogs3_.AT_URL as col_6_0_, cmtslogs2_.CM_TEXT as col_7_0_,
invtxn0_.IV_STR4 as col_8_0_, invtxn0_.IV_STR5 as col_9_0_,
invtxn0_.IV_ID as col_10_0_, invtxn0_.CS_ID as col_11_0_,
attmtslogs3_.AT_DESC as col_12_0_, invtxn0_.IV_TLAMT as col_13_0_,
attmtslogs3_.ALOG_ID as col_14_0_, csthdr6_.CS_NAME as col_15_0_,
csthdr6_.CS_REF1 as col_16_0_, stshdr4_.ST_ICON as col_17_0_ from
INV_TXN invtxn0_, STS_LOG stslogs1_, STS_HDR stshdr4_, CMTS_LOG
cmtslogs2_, ATTMTS_LOG attmtslogs3_, ACCR_TXN accrtxn5_, CST_HDR
csthdr6_ where invtxn0_.IV_ID=stslogs1_.IV_ID(+) and
invtxn0_.IV_DATE=stslogs1_.IV_DATE(+) and ( stslogs1_.st_iscurr(+)=1
and stslogs1_.st_txntpe(+)='Receivables') and
stslogs1_.ST_ID=stshdr4_.ST_ID and
invtxn0_.IV_ID=cmtslogs2_.CM_IVID(+) and
invtxn0_.IV_DATE=cmtslogs2_.CM_IVDATE(+) and (
cmtslogs2_.cm_iscurr(+)=1) and invtxn0_.IV_ID=attmtslogs3_.AT_IVID(+)
and invtxn0_.IV_DATE=attmtslogs3_.AT_DATE(+) and
(attmtslogs3_.AT_ISINV(+)=1) and invtxn0_.IV_ID=accrtxn5_.IV_ID and
invtxn0_.IV_DATE=accrtxn5_.IV_DATE and invtxn0_.CS_ID=csthdr6_.CS_ID
and (invtxn0_.EN_ID in (2820)) and invtxn0_.CS_ID='51716' and
invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 and
stslogs1_.ST_ISCURR=1 order by invtxn0_.IV_DUEDATE ASC ) where rownum
<= :1
RATIONALE: At least 16 SQL statements with PLAN_HASH_VALUE 1686042556
were found to be using literals. An example is SQL statement with
SQL_ID "0vvmyznn145nz" .
RELEVANT OBJECT: SQL statement with SQL_ID 0vvmyznn145nz and
PLAN_HASH 1686042556
select * from ( select stshdr4_.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_, accrtxn5_.AR_OUTAMT as col_4_0_,
trunc(current_date-invtxn0_.IV_DUEDATE) as col_5_0_,
attmtslogs3_.AT_URL as col_6_0_, cmtslogs2_.CM_TEXT as col_7_0_,
invtxn0_.IV_STR4 as col_8_0_, invtxn0_.IV_STR5 as col_9_0_,
invtxn0_.IV_ID as col_10_0_, invtxn0_.CS_ID as col_11_0_,
attmtslogs3_.AT_DESC as col_12_0_, invtxn0_.IV_TLAMT as col_13_0_,
attmtslogs3_.ALOG_ID as col_14_0_, csthdr6_.CS_NAME as col_15_0_,
csthdr6_.CS_REF1 as col_16_0_, stshdr4_.ST_ICON as col_17_0_ from
INV_TXN invtxn0_, STS_LOG stslogs1_, STS_HDR stshdr4_, CMTS_LOG
cmtslogs2_, ATTMTS_LOG attmtslogs3_, ACCR_TXN accrtxn5_, CST_HDR
csthdr6_ where invtxn0_.IV_ID=stslogs1_.IV_ID(+) and
invtxn0_.IV_DATE=stslogs1_.IV_DATE(+) and ( stslogs1_.st_iscurr(+)=1
and stslogs1_.st_txntpe(+)='Receivables') and
stslogs1_.ST_ID=stshdr4_.ST_ID and
invtxn0_.IV_ID=cmtslogs2_.CM_IVID(+) and
invtxn0_.IV_DATE=cmtslogs2_.CM_IVDATE(+) and (
cmtslogs2_.cm_iscurr(+)=1) and invtxn0_.IV_ID=attmtslogs3_.AT_IVID(+)
and invtxn0_.IV_DATE=attmtslogs3_.AT_DATE(+) and
(attmtslogs3_.AT_ISINV(+)=1) and invtxn0_.IV_ID=accrtxn5_.IV_ID and
invtxn0_.IV_DATE=accrtxn5_.IV_DATE and invtxn0_.CS_ID=csthdr6_.CS_ID
and (invtxn0_.EN_ID in (3910)) and invtxn0_.CS_ID='229936' and
invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 and
stslogs1_.ST_ISCURR=1 order by invtxn0_.IV_DUEDATE ASC ) where rownum
<= :1
RATIONALE: At least 15 SQL statements with PLAN_HASH_VALUE 1928032458
were found to be using literals. An example is SQL statement with
SQL_ID "4ppu4ydcfj88h" .
RELEVANT OBJECT: SQL statement with SQL_ID 4ppu4ydcfj88h and
PLAN_HASH 1928032458
select cmtslog0_.CM_CRTDBY as col_0_0_, cmtslog0_.CM_CRTDT as
col_1_0_, cmtslog0_.CM_ACTION as col_2_0_, cmtslog0_.CM_TEXT as
col_3_0_ from CMTS_LOG cmtslog0_, INV_TXN invtxn1_ where
cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and (invtxn1_.EN_ID in (4060))
and cmtslog0_.CM_IVID='5127823' order by cmtslog0_.CM_CRTDT DESC
RATIONALE: At least 15 SQL statements with PLAN_HASH_VALUE 1928032458
were found to be using literals. An example is SQL statement with
SQL_ID "5bxru8zarxarf" .
RELEVANT OBJECT: SQL statement with SQL_ID 5bxru8zarxarf and
PLAN_HASH 1928032458
select cmtslog0_.CM_CRTDBY as col_0_0_, cmtslog0_.CM_CRTDT as
col_1_0_, cmtslog0_.CM_ACTION as col_2_0_, cmtslog0_.CM_TEXT as
col_3_0_ from CMTS_LOG cmtslog0_, INV_TXN invtxn1_ where
cmtslog0_.CM_IVID=invtxn1_.IV_ID and
cmtslog0_.CM_IVDATE=invtxn1_.IV_DATE and (invtxn1_.EN_ID in (4110))
and cmtslog0_.CM_IVID='4684973' order by cmtslog0_.CM_CRTDT DESC
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (36% impact [62389 seconds])
FINDING 4: 27% impact (45885 seconds)
-------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 25% benefit (43693 seconds)
ACTION: Significant row contention was detected in the TABLE
"NCO.STS_LOG" with object id 113262. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 113262
RATIONALE: The SQL statement with SQL_ID "62f98228n4yjp" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 62f98228n4yjp
update sts_log set st_iscurr=0 where iv_id in(select doc_id from
pyttxn_det where py_id ='bf0302272d8980a3012d9fa6dae3001d') and
st_iscurr=1
RATIONALE: The SQL statement with SQL_ID "25rnvg1q4z9a0" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 25rnvg1q4z9a0
update sts_log set st_iscurr=0 where log_id
in(442679,442680,442681,442682,442683,442684,442685,442686,442687,442
688)
RATIONALE: The SQL statement with SQL_ID "acrx1pwub04n1" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID acrx1pwub04n1
update sts_log set st_iscurr=0 where log_id=442679
RATIONALE: Session with ID "211", User ID "91", Program "JDBC Thin
Client" and Module "JDBC Thin Client" was the blocking session
responsible for 99% of this recommendation's benefit.
RECOMMENDATION 2: Application Analysis, 1.3% benefit (2191 seconds)
ACTION: Significant row contention was detected in the TABLE
"DNBI.CST_AGEHDR" with object id 99805. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 99805
RATIONALE: The SQL statement with SQL_ID "bv25n45d81g64" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID bv25n45d81g64
CALL MODIFIED_ACCOUNT_AGE_CALC(:1, :2)
RATIONALE: The SQL statement with SQL_ID "6bmt7gxqc91yv" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 6bmt7gxqc91yv
DELETE FROM CST_AGEHDR WHERE CS_ID IN (SELECT CS_ID FROM CST_HDR
WHERE EN_ID=:B1 )
RATIONALE: Session with ID "300", User ID "65", Program
"oracle@rac2.capgent.com (J000)" and Module "DBMS_SCHEDULER" was the
blocking session responsible for 47% of this recommendation's
benefit.
RATIONALE: Session with ID "190", User ID "65", Program "JDBC Thin
Client" and Module "JDBC Thin Client" was the blocking session
responsible for 20% of this recommendation's benefit.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (27% impact [45904 seconds])
FINDING 5: 10% impact (17411 seconds)
-------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 10% benefit (17411 seconds)
ACTION: Run "Segment Advisor" on TABLE "DNBI.INV_TXN" with object id
56469.
RELEVANT OBJECT: database object with id 56469
ACTION: Investigate application logic involving I/O on TABLE
"DNBI.INV_TXN" with object id 56469.
RELEVANT OBJECT: database object with id 56469
RATIONALE: The I/O usage statistics for the object are: 178 full object
scans, 5333365 physical reads, 12564 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "dcnp1pu6hd5fs" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID dcnp1pu6hd5fs
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
csthdr1_.CS_ID as CS1_22_1_, accrtxn2_.IV_ID as IV1_1_2_,
accrtxn2_.IV_DATE as IV2_1_2_, invtxn0_.IV_PINVID as IV3_36_0_,
invtxn0_.IV_SBID as IV4_36_0_, invtxn0_.IV_CURR as IV5_36_0_,
invtxn0_.IV_CHGS as IV6_36_0_, invtxn0_.IV_TLAMT as IV7_36_0_,
invtxn0_.IV_SHPADD as IV8_36_0_, invtxn0_.IV_BLNO as IV9_36_0_,
invtxn0_.IV_SHMODE as IV10_36_0_, invtxn0_.IV_CARR as IV11_36_0_,
invtxn0_.IV_BILLADD as IV12_36_0_, invtxn0_.IV_CRTRMS as IV13_36_0_,
invtxn0_.IV_TERMS as IV14_36_0_, invtxn0_.IV_ATTDTLS as IV15_36_0_,
invtxn0_.IV_TEXT as IV16_36_0_, invtxn0_.IV_BTCHDTE as IV17_36_0_,
invtxn0_.IV_ADVPAID as IV18_36_0_, invtxn0_.IV_ADVADJ as IV19_36_0_,
invtxn0_.IV_PRNTD as IV20_36_0_, invtxn0_.IV_MODE as IV21_36_0_,
invtxn0_.IV_DUEDATE as IV22_36_0_, invtxn0_.IV_TARIFF as IV23_36_0_,
invtxn0_.IV_TXNDTE as IV24_36_0_, invtxn0_.IV_GPINVNO as IV25_36_0_,
invtxn0_.IV_CRTDBY as IV26_36_0_, invtxn0_.IV_MODBY as IV27_36_0_,
invtxn0_.IV_CRTDDT as IV28_36_0_, invtxn0_.IV_MODDT as IV29_36_0_,
invtxn0_.IV_CUSNO as IV30_36_0_, invtxn0_.IV_ASSGTPE as IV31_36_0_,
invtxn0_.IV_ASSGNTO as IV32_36_0_, invtxn0_.IV_STR1 as IV33_36_0_,
invtxn0_.IV_STR2 as IV34_36_0_, invtxn0_.IV_STR3 as IV35_36_0_,
invtxn0_.IV_STR4 as IV36_36_0_, invtxn0_.IV_STR5 as IV37_36_0_,
invtxn0_.IV_STR6 as IV38_36_0_, invtxn0_.IV_STR7 as IV39_36_0_,
invtxn0_.IV_STR8 as IV40_36_0_, invtxn0_.IV_STR9 as IV41_36_0_,
invtxn0_.IV_STR10 as IV42_36_0_, invtxn0_.IV_STR11 as IV43_36_0_,
invtxn0_.IV_STR12 as IV44_36_0_, invtxn0_.IV_STR13 as IV45_36_0_,
invtxn0_.IV_STR14 as IV46_36_0_, invtxn0_.IV_STR15 as IV47_36_0_,
invtxn0_.IV_STR16 as IV48_36_0_, invtxn0_.IV_STR17 as IV49_36_0_,
invtxn0_.IV_STR18 as IV50_36_0_, invtxn0_.IV_STR19 as IV51_36_0_,
invtxn0_.IV_STR20 as IV52_36_0_, invtxn0_.IV_STR21 as IV53_36_0_,
invtxn0_.IV_STR22 as IV54_36_0_, invtxn0_.IV_STR23 as IV55_36_0_,
invtxn0_.IV_STR24 as IV56_36_0_, invtxn0_.IV_STR25 as IV57_36_0_,
invtxn0_.IV_NUM1 as IV58_36_0_, invtxn0_.IV_NUM2 as IV59_36_0_,
invtxn0_.IV_NUM3 as IV60_36_0_, invtxn0_.IV_NUM4 as IV61_36_0_,
invtxn0_.IV_NUM5 as IV62_36_0_, invtxn0_.IV_NUM6 as IV63_36_0_,
invtxn0_.IV_NUM7 as IV64_36_0_, invtxn0_.IV_NUM8 as IV65_36_0_,
invtxn0_.IV_NUM9 as IV66_36_0_, invtxn0_.IV_NUM10 as IV67_36_0_,
invtxn0_.IV_DATE1 as IV68_36_0_, invtxn0_.IV_DATE2 as IV69_36_0_,
invtxn0_.IV_DATE3 as IV70_36_0_, invtxn0_.IV_DATE4 as IV71_36_0_,
invtxn0_.IV_DATE5 as IV72_36_0_, invtxn0_.IV_DATE6 as IV73_36_0_,
invtxn0_.IV_DATE8 as IV74_36_0_, invtxn0_.IV_DATE9 as IV75_36_0_,
invtxn0_.IV_DATE10 as IV76_36_0_, invtxn0_.IV_ORDNO as IV77_36_0_,
invtxn0_.IV_FOR as IV78_36_0_, invtxn0_.IV_REMTO as IV79_36_0_,
invtxn0_.IV_PRODCDE as IV80_36_0_, invtxn0_.IV_CNTRLNO as IV81_36_0_,
invtxn0_.IV_IONUM as IV82_36_0_, invtxn0_.IV_AUTHNO as IV83_36_0_,
invtxn0_.IV_DEPTNO as IV84_36_0_, invtxn0_.IV_LOC as IV85_36_0_,
invtxn0_.IV_CLOSED as IV86_36_0_, invtxn0_.TID as TID36_0_,
invtxn0_.AID as AID36_0_, invtxn0_.IV_SOURCE as IV89_36_0_,
invtxn0_.IV_RECTYPE as IV90_36_0_, invtxn0_.IV_SLSORDNO as
IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_, invtxn0_.IV_TXAMT as
IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_, invtxn0_.IV_BU as
IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_, invtxn0_.IV_SHCLASS as
IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_, invtxn0_.IV_PODATE as
IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_, invtxn0_.IV_BLDATEFRO
as IV101_36_0_, invtxn0_.IV_BLDATETO as IV102_36_0_,
invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as IV104_36_0_,
invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET as
IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_, csthdr1_.CS_PID as CS2_22_1_, csthdr1_.CS_NAME as
CS3_22_1_, csthdr1_.CS_ACTIVE as CS4_22_1_, csthdr1_.CS_ALIAS as
CS5_22_1_, csthdr1_.CS_REF1 as CS6_22_1_, csthdr1_.CS_REF2 as
CS7_22_1_, csthdr1_.CS_OREF as CS8_22_1_, csthdr1_.CS_OSUPP as
CS9_22_1_, csthdr1_.CS_CDSCT as CS10_22_1_, csthdr1_.CS_DELM as
CS11_22_1_, csthdr1_.CS_SLPN as CS12_22_1_, csthdr1_.CS_RESP as
CS13_22_1_, csthdr1_.CS_VATC as CS14_22_1_, csthdr1_.CS_CURR as
CS15_22_1_, csthdr1_.CS_XCHTPE as CS16_22_1_, csthdr1_.CS_DTEFRM as
CS17_22_1_, csthdr1_.CS_PAYR as CS18_22_1_, csthdr1_.CS_STATS as
CS19_22_1_, csthdr1_.CS_CRLM1 as CS20_22_1_, csthdr1_.CS_CRLM2 as
CS21_22_1_, csthdr1_.CS_CRLM3 as CS22_22_1_, csthdr1_.CS_STOP as
CS23_22_1_, csthdr1_.CS_OSINAMT as CS24_22_1_, csthdr1_.CS_ODINAMT as
CS25_22_1_, csthdr1_.CS_LINVDTE as CS26_22_1_, csthdr1_.CS_EDTE as
CS27_22_1_, csthdr1_.CS_SUMINV as CS28_22_1_, csthdr1_.CS_INTINV as
CS29_22_1_, csthdr1_.CS_INTRUL as CS30_22_1_, csthdr1_.CS_CRATING as
CS31_22_1_, csthdr1_.CS_CREVIEW as CS32_22_1_, csthdr1_.CS_CRUPDTE as
CS33_22_1_, csthdr1_.CS_DB as CS34_22_1_, csthdr1_.CS_LANG as
CS35_22_1_, csthdr1_.CS_PSTID as CS36_22_1_, csthdr1_.CS_REGION as
CS37_22_1_, csthdr1_.CS_RMKS as CS38_22_1_, csthdr1_.CS_STMT as
CS39_22_1_, csthdr1_.CS_CRTDBY as CS40_22_1_, csthdr1_.CS_MODBY as
CS41_22_1_, csthdr1_.CS_CRTDDT as CS42_22_1_, csthdr1_.CS_MODDT as
CS43_22_1_, csthdr1_.CS_CSGID as CS44_22_1_, csthdr1_.EN_ID as
EN45_22_1_, csthdr1_.STE_ID as STE46_22_1_, csthdr1_.CS_STR1 as
CS47_22_1_, csthdr1_.CS_STR2 as CS48_22_1_, csthdr1_.CS_STR3 as
CS49_22_1_, csthdr1_.CS_STR4 as CS50_22_1_, csthdr1_.CS_STR5 as
CS51_22_1_, csthdr1_.CS_STR6 as CS52_22_1_, csthdr1_.CS_STR7 as
CS53_22_1_, csthdr1_.CS_STR8 as CS54_22_1_, csthdr1_.CS_STR9 as
CS55_22_1_, csthdr1_.CS_STR10 as CS56_22_1_, csthdr1_.CS_STR11 as
CS57_22_1_, csthdr1_.CS_STR12 as CS58_22_1_, csthdr1_.CS_STR13 as
CS59_22_1_, csthdr1_.CS_STR14 as CS60_22_1_, csthdr1_.CS_STR15 as
CS61_22_1_, csthdr1_.CS_STR16 as CS62_22_1_, csthdr1_.CS_STR17 as
CS63_22_1_, csthdr1_.CS_STR18 as CS64_22_1_, csthdr1_.CS_STR19 as
CS65_22_1_, csthdr1_.CS_STR20 as CS66_22_1_, csthdr1_.CS_STR21 as
CS67_22_1_, csthdr1_.CS_STR22 as CS68_22_1_, csthdr1_.CS_STR23 as
CS69_22_1_, csthdr1_.CS_STR24 as CS70_22_1_, csthdr1_.CS_STR25 as
CS71_22_1_, csthdr1_.CS_NUM1 as CS72_22_1_, csthdr1_.CS_NUM2 as
CS73_22_1_, csthdr1_.CS_NUM3 as CS74_22_1_, csthdr1_.CS_NUM4 as
CS75_22_1_, csthdr1_.CS_NUM5 as CS76_22_1_, csthdr1_.CS_NUM6 as
CS77_22_1_, csthdr1_.CS_NUM7 as CS78_22_1_, csthdr1_.CS_NUM8 as
CS79_22_1_, csthdr1_.CS_NUM9 as CS80_22_1_, csthdr1_.CS_NUM10 as
CS81_22_1_, csthdr1_.CS_NUM11 as CS82_22_1_, csthdr1_.CS_NUM12 as
CS83_22_1_, csthdr1_.CS_NUM13 as CS84_22_1_, csthdr1_.CS_NUM14 as
CS85_22_1_, csthdr1_.CS_NUM15 as CS86_22_1_, csthdr1_.CS_NUM16 as
CS87_22_1_, csthdr1_.CS_NUM17 as CS88_22_1_, csthdr1_.CS_NUM18 as
CS89_22_1_, csthdr1_.CS_NUM19 as CS90_22_1_, csthdr1_.CS_NUM20 as
CS91_22_1_, csthdr1_.CS_DATE1 as CS92_22_1_, csthdr1_.CS_DATE2 as
CS93_22_1_, csthdr1_.CS_DATE3 as CS94_22_1_, csthdr1_.CS_DATE4 as
CS95_22_1_, csthdr1_.CS_DATE5 as CS96_22_1_, csthdr1_.CS_DATE6 as
CS97_22_1_, csthdr1_.CS_DATE7 as CS98_22_1_, csthdr1_.CS_DATE8 as
CS99_22_1_, csthdr1_.CS_DATE9 as CS100_22_1_, csthdr1_.CS_DATE10 as
CS101_22_1_, csthdr1_.CS_TERMS as CS102_22_1_, csthdr1_.CS_KEYMARK as
CS103_22_1_, csthdr1_.AID as AID22_1_, csthdr1_.CS_IND as
CS105_22_1_, csthdr1_.CS_URL as CS106_22_1_, csthdr1_.AC_TYPE as
AC107_22_1_, csthdr1_.CS_AVGDP as CS108_22_1_, csthdr1_.CS_BKT12 as
CS109_22_1_, csthdr1_.CS_ERAM as CS110_22_1_, csthdr1_.rowid as
rowid111_22_1_, csthdr1_.PY_CODE as PY112_22_1_, csthdr1_.PC_ID as
PC113_22_1_, csthdr1_.CY_ID as CY114_22_1_, csthdr1_.PR_ID as
PR115_22_1_, csthdr1_.CS_TYPE as CS116_22_1_, csthdr1_.DT_ID as
DT117_22_1_, accrtxn2_.SB_ID as SB3_1_2_, accrtxn2_.AR_BTHDTE as
AR4_1_2_, accrtxn2_.AR_PAYR as AR5_1_2_, accrtxn2_.AR_INVVNS as
AR6_1_2_, accrtxn2_.AR_INVVNO as AR7_1_2_, accrtxn2_.AR_DESC as
AR8_1_2_, accrtxn2_.AR_SLSPRN as AR9_1_2_, accrtxn2_.AR_CUSGRP as
AR10_1_2_, accrtxn2_.AR_XCHRTPE as AR11_1_2_, accrtxn2_.AR_XCHRTE as
AR12_1_2_, accrtxn2_.AR_FCURAMT as AR13_1_2_, accrtxn2_.AR_VATAMT as
AR14_1_2_, accrtxn2_.AR_CHGTPE as AR15_1_2_, accrtxn2_.AR_MRKUP as
AR16_1_2_, accrtxn2_.AR_FWUDTE as AR17_1_2_, accrtxn2_.AR_ACCDTE as
AR18_1_2_, accrtxn2_.ACC_CODE as ACC19_1_2_, accrtxn2_.AR_BANK as
AR20_1_2_, accrtxn2_.AR_CDSCT as AR21_1_2_, accrtxn2_.AR_PYTMTD as
AR22_1_2_, accrtxn2_.AR_RECCDE as AR23_1_2_, accrtxn2_.AR_RECDTE as
AR24_1_2_, accrtxn2_.AR_ADVCDE as AR25_1_2_, accrtxn2_.AR_PYREM as
AR26_1_2_, accrtxn2_.AR_LREMDTE as AR27_1_2_, accrtxn2_.AR_PTREMDTE
as AR28_1_2_, accrtxn2_.AR_STPBY as AR29_1_2_, accrtxn2_.AR_STPDTE as
AR30_1_2_, accrtxn2_.AR_NOPYTRM as AR31_1_2_, accrtxn2_.AR_INTINVB as
AR32_1_2_, accrtxn2_.AR_INTINVS as AR33_1_2_, accrtxn2_.AR_CINTAMT as
AR34_1_2_, accrtxn2_.AR_FLGCDE as AR35_1_2_, accrtxn2_.AR_GPINDTE as
AR36_1_2_, accrtxn2_.AR_TXT as AR37_1_2_, accrtxn2_.AR_DOCLNK as
AR38_1_2_, accrtxn2_.AR_DRTPRD as AR39_1_2_, accrtxn2_.AR_DRPRDTE as
AR40_1_2_, accrtxn2_.AR_PRODGRP as AR41_1_2_, accrtxn2_.AR_ORDNO as
AR42_1_2_, accrtxn2_.AR_TXNDTE as AR43_1_2_, accrtxn2_.AR_FRTTRM as
AR44_1_2_, accrtxn2_.AR_NBR as AR45_1_2_, accrtxn2_.AR_PRINT as
AR46_1_2_, accrtxn2_.AR_CRTDBY as AR47_1_2_, accrtxn2_.AR_MODBY as
AR48_1_2_, accrtxn2_.AR_CRTDDT as AR49_1_2_, accrtxn2_.AR_MODDT as
AR50_1_2_, accrtxn2_.EN_ID as EN51_1_2_, accrtxn2_.STE_ID as
STE52_1_2_, accrtxn2_.AR_AMT as AR53_1_2_, accrtxn2_.AR_OUTAMT as
AR54_1_2_, accrtxn2_.AR_STR1 as AR55_1_2_, accrtxn2_.AR_STR2 as
AR56_1_2_, accrtxn2_.AR_STR3 as AR57_1_2_, accrtxn2_.AR_STR4 as
AR58_1_2_, accrtxn2_.AR_STR5 as AR59_1_2_, accrtxn2_.AR_STR6 as
AR60_1_2_, accrtxn2_.AR_STR7 as AR61_1_2_, accrtxn2_.AR_STR8 as
AR62_1_2_, accrtxn2_.AR_STR9 as AR63_1_2_, accrtxn2_.AR_STR10 as
AR64_1_2_, accrtxn2_.AR_STR11 as AR65_1_2_, accrtxn2_.AR_STR12 as
AR66_1_2_, accrtxn2_.AR_STR13 as AR67_1_2_, accrtxn2_.AR_STR14 as
AR68_1_2_, accrtxn2_.AR_STR15 as AR69_1_2_, accrtxn2_.AR_NUM1 as
AR70_1_2_, accrtxn2_.AR_NUM2 as AR71_1_2_, accrtxn2_.AR_NUM3 as
AR72_1_2_, accrtxn2_.AR_NUM4 as AR73_1_2_, accrtxn2_.AR_NUM5 as
AR74_1_2_, accrtxn2_.AR_NUM6 as AR75_1_2_, accrtxn2_.AR_NUM7 as
AR76_1_2_, accrtxn2_.AR_NUM8 as AR77_1_2_, accrtxn2_.AR_NUM9 as
AR78_1_2_, accrtxn2_.AR_NUM10 as AR79_1_2_, accrtxn2_.AR_DATE1 as
AR80_1_2_, accrtxn2_.AR_DATE2 as AR81_1_2_, accrtxn2_.AR_DATE3 as
AR82_1_2_, accrtxn2_.AR_DATE4 as AR83_1_2_, accrtxn2_.AR_DATE5 as
AR84_1_2_, accrtxn2_.AR_DATE6 as AR85_1_2_, accrtxn2_.AR_DATE7 as
AR86_1_2_, accrtxn2_.AR_DATE8 as AR87_1_2_, accrtxn2_.AR_DATE9 as
AR88_1_2_, accrtxn2_.AR_DATE10 as AR89_1_2_, accrtxn2_.AR_PROMDTE as
AR90_1_2_, accrtxn2_.TID as TID1_2_, accrtxn2_.PY_TERMS as PY92_1_2_,
accrtxn2_.PR_ID as PR93_1_2_ from INV_TXN invtxn0_, CST_HDR csthdr1_,
ACCR_TXN accrtxn2_, CSTPE_HDR cstpehdr4_ where
invtxn0_.CS_ID=csthdr1_.CS_ID(+) and
invtxn0_.IV_ID=accrtxn2_.IV_ID(+) and
invtxn0_.IV_DATE=accrtxn2_.IV_DATE(+) and
csthdr1_.CS_TYPE=cstpehdr4_.CS_TYPE and (invtxn0_.CS_ID in (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 ,
:50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61
, :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 ,
:73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84
, :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 ,
:96 , :97 , :98 , :99 , :100)) and
invtxn0_.IV_TYPE=cstpehdr4_.CS_NAME and invtxn0_.IV_CLOSED=0
RATIONALE: The SQL statement with SQL_ID "4fx1nsvwwbgm8" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 4fx1nsvwwbgm8
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
csthdr1_.CS_ID as CS1_22_1_, accrtxn2_.IV_ID as IV1_1_2_,
accrtxn2_.IV_DATE as IV2_1_2_, invtxn0_.IV_PINVID as IV3_36_0_,
invtxn0_.IV_SBID as IV4_36_0_, invtxn0_.IV_CURR as IV5_36_0_,
invtxn0_.IV_CHGS as IV6_36_0_, invtxn0_.IV_TLAMT as IV7_36_0_,
invtxn0_.IV_SHPADD as IV8_36_0_, invtxn0_.IV_BLNO as IV9_36_0_,
invtxn0_.IV_SHMODE as IV10_36_0_, invtxn0_.IV_CARR as IV11_36_0_,
invtxn0_.IV_BILLADD as IV12_36_0_, invtxn0_.IV_CRTRMS as IV13_36_0_,
invtxn0_.IV_TERMS as IV14_36_0_, invtxn0_.IV_ATTDTLS as IV15_36_0_,
invtxn0_.IV_TEXT as IV16_36_0_, invtxn0_.IV_BTCHDTE as IV17_36_0_,
invtxn0_.IV_ADVPAID as IV18_36_0_, invtxn0_.IV_ADVADJ as IV19_36_0_,
invtxn0_.IV_PRNTD as IV20_36_0_, invtxn0_.IV_MODE as IV21_36_0_,
invtxn0_.IV_DUEDATE as IV22_36_0_, invtxn0_.IV_TARIFF as IV23_36_0_,
invtxn0_.IV_TXNDTE as IV24_36_0_, invtxn0_.IV_GPINVNO as IV25_36_0_,
invtxn0_.IV_CRTDBY as IV26_36_0_, invtxn0_.IV_MODBY as IV27_36_0_,
invtxn0_.IV_CRTDDT as IV28_36_0_, invtxn0_.IV_MODDT as IV29_36_0_,
invtxn0_.IV_CUSNO as IV30_36_0_, invtxn0_.IV_ASSGTPE as IV31_36_0_,
invtxn0_.IV_ASSGNTO as IV32_36_0_, invtxn0_.IV_STR1 as IV33_36_0_,
invtxn0_.IV_STR2 as IV34_36_0_, invtxn0_.IV_STR3 as IV35_36_0_,
invtxn0_.IV_STR4 as IV36_36_0_, invtxn0_.IV_STR5 as IV37_36_0_,
invtxn0_.IV_STR6 as IV38_36_0_, invtxn0_.IV_STR7 as IV39_36_0_,
invtxn0_.IV_STR8 as IV40_36_0_, invtxn0_.IV_STR9 as IV41_36_0_,
invtxn0_.IV_STR10 as IV42_36_0_, invtxn0_.IV_STR11 as IV43_36_0_,
invtxn0_.IV_STR12 as IV44_36_0_, invtxn0_.IV_STR13 as IV45_36_0_,
invtxn0_.IV_STR14 as IV46_36_0_, invtxn0_.IV_STR15 as IV47_36_0_,
invtxn0_.IV_STR16 as IV48_36_0_, invtxn0_.IV_STR17 as IV49_36_0_,
invtxn0_.IV_STR18 as IV50_36_0_, invtxn0_.IV_STR19 as IV51_36_0_,
invtxn0_.IV_STR20 as IV52_36_0_, invtxn0_.IV_STR21 as IV53_36_0_,
invtxn0_.IV_STR22 as IV54_36_0_, invtxn0_.IV_STR23 as IV55_36_0_,
invtxn0_.IV_STR24 as IV56_36_0_, invtxn0_.IV_STR25 as IV57_36_0_,
invtxn0_.IV_NUM1 as IV58_36_0_, invtxn0_.IV_NUM2 as IV59_36_0_,
invtxn0_.IV_NUM3 as IV60_36_0_, invtxn0_.IV_NUM4 as IV61_36_0_,
invtxn0_.IV_NUM5 as IV62_36_0_, invtxn0_.IV_NUM6 as IV63_36_0_,
invtxn0_.IV_NUM7 as IV64_36_0_, invtxn0_.IV_NUM8 as IV65_36_0_,
invtxn0_.IV_NUM9 as IV66_36_0_, invtxn0_.IV_NUM10 as IV67_36_0_,
invtxn0_.IV_DATE1 as IV68_36_0_, invtxn0_.IV_DATE2 as IV69_36_0_,
invtxn0_.IV_DATE3 as IV70_36_0_, invtxn0_.IV_DATE4 as IV71_36_0_,
invtxn0_.IV_DATE5 as IV72_36_0_, invtxn0_.IV_DATE6 as IV73_36_0_,
invtxn0_.IV_DATE8 as IV74_36_0_, invtxn0_.IV_DATE9 as IV75_36_0_,
invtxn0_.IV_DATE10 as IV76_36_0_, invtxn0_.IV_ORDNO as IV77_36_0_,
invtxn0_.IV_FOR as IV78_36_0_, invtxn0_.IV_REMTO as IV79_36_0_,
invtxn0_.IV_PRODCDE as IV80_36_0_, invtxn0_.IV_CNTRLNO as IV81_36_0_,
invtxn0_.IV_IONUM as IV82_36_0_, invtxn0_.IV_AUTHNO as IV83_36_0_,
invtxn0_.IV_DEPTNO as IV84_36_0_, invtxn0_.IV_LOC as IV85_36_0_,
invtxn0_.IV_CLOSED as IV86_36_0_, invtxn0_.TID as TID36_0_,
invtxn0_.AID as AID36_0_, invtxn0_.IV_SOURCE as IV89_36_0_,
invtxn0_.IV_RECTYPE as IV90_36_0_, invtxn0_.IV_SLSORDNO as
IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_, invtxn0_.IV_TXAMT as
IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_, invtxn0_.IV_BU as
IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_, invtxn0_.IV_SHCLASS as
IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_, invtxn0_.IV_PODATE as
IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_, invtxn0_.IV_BLDATEFRO
as IV101_36_0_, invtxn0_.IV_BLDATETO as IV102_36_0_,
invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as IV104_36_0_,
invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET as
IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_, csthdr1_.CS_PID as CS2_22_1_, csthdr1_.CS_NAME as
CS3_22_1_, csthdr1_.CS_ACTIVE as CS4_22_1_, csthdr1_.CS_ALIAS as
CS5_22_1_, csthdr1_.CS_REF1 as CS6_22_1_, csthdr1_.CS_REF2 as
CS7_22_1_, csthdr1_.CS_OREF as CS8_22_1_, csthdr1_.CS_OSUPP as
CS9_22_1_, csthdr1_.CS_CDSCT as CS10_22_1_, csthdr1_.CS_DELM as
CS11_22_1_, csthdr1_.CS_SLPN as CS12_22_1_, csthdr1_.CS_RESP as
CS13_22_1_, csthdr1_.CS_VATC as CS14_22_1_, csthdr1_.CS_CURR as
CS15_22_1_, csthdr1_.CS_XCHTPE as CS16_22_1_, csthdr1_.CS_DTEFRM as
CS17_22_1_, csthdr1_.CS_PAYR as CS18_22_1_, csthdr1_.CS_STATS as
CS19_22_1_, csthdr1_.CS_CRLM1 as CS20_22_1_, csthdr1_.CS_CRLM2 as
CS21_22_1_, csthdr1_.CS_CRLM3 as CS22_22_1_, csthdr1_.CS_STOP as
CS23_22_1_, csthdr1_.CS_OSINAMT as CS24_22_1_, csthdr1_.CS_ODINAMT as
CS25_22_1_, csthdr1_.CS_LINVDTE as CS26_22_1_, csthdr1_.CS_EDTE as
CS27_22_1_, csthdr1_.CS_SUMINV as CS28_22_1_, csthdr1_.CS_INTINV as
CS29_22_1_, csthdr1_.CS_INTRUL as CS30_22_1_, csthdr1_.CS_CRATING as
CS31_22_1_, csthdr1_.CS_CREVIEW as CS32_22_1_, csthdr1_.CS_CRUPDTE as
CS33_22_1_, csthdr1_.CS_DB as CS34_22_1_, csthdr1_.CS_LANG as
CS35_22_1_, csthdr1_.CS_PSTID as CS36_22_1_, csthdr1_.CS_REGION as
CS37_22_1_, csthdr1_.CS_RMKS as CS38_22_1_, csthdr1_.CS_STMT as
CS39_22_1_, csthdr1_.CS_CRTDBY as CS40_22_1_, csthdr1_.CS_MODBY as
CS41_22_1_, csthdr1_.CS_CRTDDT as CS42_22_1_, csthdr1_.CS_MODDT as
CS43_22_1_, csthdr1_.CS_CSGID as CS44_22_1_, csthdr1_.EN_ID as
EN45_22_1_, csthdr1_.STE_ID as STE46_22_1_, csthdr1_.CS_STR1 as
CS47_22_1_, csthdr1_.CS_STR2 as CS48_22_1_, csthdr1_.CS_STR3 as
CS49_22_1_, csthdr1_.CS_STR4 as CS50_22_1_, csthdr1_.CS_STR5 as
CS51_22_1_, csthdr1_.CS_STR6 as CS52_22_1_, csthdr1_.CS_STR7 as
CS53_22_1_, csthdr1_.CS_STR8 as CS54_22_1_, csthdr1_.CS_STR9 as
CS55_22_1_, csthdr1_.CS_STR10 as CS56_22_1_, csthdr1_.CS_STR11 as
CS57_22_1_, csthdr1_.CS_STR12 as CS58_22_1_, csthdr1_.CS_STR13 as
CS59_22_1_, csthdr1_.CS_STR14 as CS60_22_1_, csthdr1_.CS_STR15 as
CS61_22_1_, csthdr1_.CS_STR16 as CS62_22_1_, csthdr1_.CS_STR17 as
CS63_22_1_, csthdr1_.CS_STR18 as CS64_22_1_, csthdr1_.CS_STR19 as
CS65_22_1_, csthdr1_.CS_STR20 as CS66_22_1_, csthdr1_.CS_STR21 as
CS67_22_1_, csthdr1_.CS_STR22 as CS68_22_1_, csthdr1_.CS_STR23 as
CS69_22_1_, csthdr1_.CS_STR24 as CS70_22_1_, csthdr1_.CS_STR25 as
CS71_22_1_, csthdr1_.CS_NUM1 as CS72_22_1_, csthdr1_.CS_NUM2 as
CS73_22_1_, csthdr1_.CS_NUM3 as CS74_22_1_, csthdr1_.CS_NUM4 as
CS75_22_1_, csthdr1_.CS_NUM5 as CS76_22_1_, csthdr1_.CS_NUM6 as
CS77_22_1_, csthdr1_.CS_NUM7 as CS78_22_1_, csthdr1_.CS_NUM8 as
CS79_22_1_, csthdr1_.CS_NUM9 as CS80_22_1_, csthdr1_.CS_NUM10 as
CS81_22_1_, csthdr1_.CS_NUM11 as CS82_22_1_, csthdr1_.CS_NUM12 as
CS83_22_1_, csthdr1_.CS_NUM13 as CS84_22_1_, csthdr1_.CS_NUM14 as
CS85_22_1_, csthdr1_.CS_NUM15 as CS86_22_1_, csthdr1_.CS_NUM16 as
CS87_22_1_, csthdr1_.CS_NUM17 as CS88_22_1_, csthdr1_.CS_NUM18 as
CS89_22_1_, csthdr1_.CS_NUM19 as CS90_22_1_, csthdr1_.CS_NUM20 as
CS91_22_1_, csthdr1_.CS_DATE1 as CS92_22_1_, csthdr1_.CS_DATE2 as
CS93_22_1_, csthdr1_.CS_DATE3 as CS94_22_1_, csthdr1_.CS_DATE4 as
CS95_22_1_, csthdr1_.CS_DATE5 as CS96_22_1_, csthdr1_.CS_DATE6 as
CS97_22_1_, csthdr1_.CS_DATE7 as CS98_22_1_, csthdr1_.CS_DATE8 as
CS99_22_1_, csthdr1_.CS_DATE9 as CS100_22_1_, csthdr1_.CS_DATE10 as
CS101_22_1_, csthdr1_.CS_TERMS as CS102_22_1_, csthdr1_.CS_KEYMARK as
CS103_22_1_, csthdr1_.AID as AID22_1_, csthdr1_.CS_IND as
CS105_22_1_, csthdr1_.CS_URL as CS106_22_1_, csthdr1_.AC_TYPE as
AC107_22_1_, csthdr1_.CS_AVGDP as CS108_22_1_, csthdr1_.CS_BKT12 as
CS109_22_1_, csthdr1_.CS_ERAM as CS110_22_1_, csthdr1_.rowid as
rowid111_22_1_, csthdr1_.PY_CODE as PY112_22_1_, csthdr1_.PC_ID as
PC113_22_1_, csthdr1_.CY_ID as CY114_22_1_, csthdr1_.PR_ID as
PR115_22_1_, csthdr1_.CS_TYPE as CS116_22_1_, csthdr1_.DT_ID as
DT117_22_1_, accrtxn2_.SB_ID as SB3_1_2_, accrtxn2_.AR_BTHDTE as
AR4_1_2_, accrtxn2_.AR_PAYR as AR5_1_2_, accrtxn2_.AR_INVVNS as
AR6_1_2_, accrtxn2_.AR_INVVNO as AR7_1_2_, accrtxn2_.AR_DESC as
AR8_1_2_, accrtxn2_.AR_SLSPRN as AR9_1_2_, accrtxn2_.AR_CUSGRP as
AR10_1_2_, accrtxn2_.AR_XCHRTPE as AR11_1_2_, accrtxn2_.AR_XCHRTE as
AR12_1_2_, accrtxn2_.AR_FCURAMT as AR13_1_2_, accrtxn2_.AR_VATAMT as
AR14_1_2_, accrtxn2_.AR_CHGTPE as AR15_1_2_, accrtxn2_.AR_MRKUP as
AR16_1_2_, accrtxn2_.AR_FWUDTE as AR17_1_2_, accrtxn2_.AR_ACCDTE as
AR18_1_2_, accrtxn2_.ACC_CODE as ACC19_1_2_, accrtxn2_.AR_BANK as
AR20_1_2_, accrtxn2_.AR_CDSCT as AR21_1_2_, accrtxn2_.AR_PYTMTD as
AR22_1_2_, accrtxn2_.AR_RECCDE as AR23_1_2_, accrtxn2_.AR_RECDTE as
AR24_1_2_, accrtxn2_.AR_ADVCDE as AR25_1_2_, accrtxn2_.AR_PYREM as
AR26_1_2_, accrtxn2_.AR_LREMDTE as AR27_1_2_, accrtxn2_.AR_PTREMDTE
as AR28_1_2_, accrtxn2_.AR_STPBY as AR29_1_2_, accrtxn2_.AR_STPDTE as
AR30_1_2_, accrtxn2_.AR_NOPYTRM as AR31_1_2_, accrtxn2_.AR_INTINVB as
AR32_1_2_, accrtxn2_.AR_INTINVS as AR33_1_2_, accrtxn2_.AR_CINTAMT as
AR34_1_2_, accrtxn2_.AR_FLGCDE as AR35_1_2_, accrtxn2_.AR_GPINDTE as
AR36_1_2_, accrtxn2_.AR_TXT as AR37_1_2_, accrtxn2_.AR_DOCLNK as
AR38_1_2_, accrtxn2_.AR_DRTPRD as AR39_1_2_, accrtxn2_.AR_DRPRDTE as
AR40_1_2_, accrtxn2_.AR_PRODGRP as AR41_1_2_, accrtxn2_.AR_ORDNO as
AR42_1_2_, accrtxn2_.AR_TXNDTE as AR43_1_2_, accrtxn2_.AR_FRTTRM as
AR44_1_2_, accrtxn2_.AR_NBR as AR45_1_2_, accrtxn2_.AR_PRINT as
AR46_1_2_, accrtxn2_.AR_CRTDBY as AR47_1_2_, accrtxn2_.AR_MODBY as
AR48_1_2_, accrtxn2_.AR_CRTDDT as AR49_1_2_, accrtxn2_.AR_MODDT as
AR50_1_2_, accrtxn2_.EN_ID as EN51_1_2_, accrtxn2_.STE_ID as
STE52_1_2_, accrtxn2_.AR_AMT as AR53_1_2_, accrtxn2_.AR_OUTAMT as
AR54_1_2_, accrtxn2_.AR_STR1 as AR55_1_2_, accrtxn2_.AR_STR2 as
AR56_1_2_, accrtxn2_.AR_STR3 as AR57_1_2_, accrtxn2_.AR_STR4 as
AR58_1_2_, accrtxn2_.AR_STR5 as AR59_1_2_, accrtxn2_.AR_STR6 as
AR60_1_2_, accrtxn2_.AR_STR7 as AR61_1_2_, accrtxn2_.AR_STR8 as
AR62_1_2_, accrtxn2_.AR_STR9 as AR63_1_2_, accrtxn2_.AR_STR10 as
AR64_1_2_, accrtxn2_.AR_STR11 as AR65_1_2_, accrtxn2_.AR_STR12 as
AR66_1_2_, accrtxn2_.AR_STR13 as AR67_1_2_, accrtxn2_.AR_STR14 as
AR68_1_2_, accrtxn2_.AR_STR15 as AR69_1_2_, accrtxn2_.AR_NUM1 as
AR70_1_2_, accrtxn2_.AR_NUM2 as AR71_1_2_, accrtxn2_.AR_NUM3 as
AR72_1_2_, accrtxn2_.AR_NUM4 as AR73_1_2_, accrtxn2_.AR_NUM5 as
AR74_1_2_, accrtxn2_.AR_NUM6 as AR75_1_2_, accrtxn2_.AR_NUM7 as
AR76_1_2_, accrtxn2_.AR_NUM8 as AR77_1_2_, accrtxn2_.AR_NUM9 as
AR78_1_2_, accrtxn2_.AR_NUM10 as AR79_1_2_, accrtxn2_.AR_DATE1 as
AR80_1_2_, accrtxn2_.AR_DATE2 as AR81_1_2_, accrtxn2_.AR_DATE3 as
AR82_1_2_, accrtxn2_.AR_DATE4 as AR83_1_2_, accrtxn2_.AR_DATE5 as
AR84_1_2_, accrtxn2_.AR_DATE6 as AR85_1_2_, accrtxn2_.AR_DATE7 as
AR86_1_2_, accrtxn2_.AR_DATE8 as AR87_1_2_, accrtxn2_.AR_DATE9 as
AR88_1_2_, accrtxn2_.AR_DATE10 as AR89_1_2_, accrtxn2_.AR_PROMDTE as
AR90_1_2_, accrtxn2_.TID as TID1_2_, accrtxn2_.PY_TERMS as PY92_1_2_,
accrtxn2_.PR_ID as PR93_1_2_ from INV_TXN invtxn0_, CST_HDR csthdr1_,
ACCR_TXN accrtxn2_, CSTPE_HDR cstpehdr4_ where
invtxn0_.CS_ID=csthdr1_.CS_ID(+) and
invtxn0_.IV_ID=accrtxn2_.IV_ID(+) and
invtxn0_.IV_DATE=accrtxn2_.IV_DATE(+) and
csthdr1_.CS_TYPE=cstpehdr4_.CS_TYPE and (invtxn0_.CS_ID in (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 ,
:50)) and invtxn0_.IV_TYPE=cstpehdr4_.CS_NAME and
invtxn0_.IV_CLOSED=0
RATIONALE: The SQL statement with SQL_ID "dmnmycu48ffm0" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID dmnmycu48ffm0
update CST_HDR set CS_STMT=1 where (CS_ID in (select csthdr1_.CS_ID
from CST_HDR csthdr1_, INV_TXN invtxns2_, ADD_HDR addhdrs3_ where
csthdr1_.CS_ID=invtxns2_.CS_ID(+) and
csthdr1_.CS_ID=addhdrs3_.AD_LINK(+) and csthdr1_.CS_ACTIVE=1 and
invtxns2_.IV_TYPE='Account' and trunc(sysdate-invtxns2_.IV_DATE4)=34
and csthdr1_.CS_CRLM3+csthdr1_.CS_OSINAMT+csthdr1_.CS_ODINAMT+csthdr1
_.CS_NUM1+csthdr1_.CS_NUM2+csthdr1_.CS_NUM3+csthdr1_.CS_NUM4+csthdr1_
.CS_NUM15>0 and invtxns2_.IV_TXAMT>0 and
csthdr1_.CS_NUM9>invtxns2_.IV_TXAMT/10 and (csthdr1_.CS_STMT is null
or csthdr1_.CS_STMT<>1 and csthdr1_.CS_STMT<>2) and (addhdrs3_.AD_ID
in (select max(addhdr4_.AD_ID) from ADD_HDR addhdr4_ where
addhdr4_.AD_LINK=csthdr1_.CS_ID and lower(addhdr4_.AD_STR4)='osg'))))
and EN_ID=4160
RATIONALE: The SQL statement with SQL_ID "1jx74utp16rcv" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 1jx74utp16rcv
select invtxn0_.IV_ID as IV1_36_, invtxn0_.IV_DATE as IV2_36_,
invtxn0_.IV_PINVID as IV3_36_, invtxn0_.IV_SBID as IV4_36_,
invtxn0_.IV_CURR as IV5_36_, invtxn0_.IV_CHGS as IV6_36_,
invtxn0_.IV_TLAMT as IV7_36_, invtxn0_.IV_SHPADD as IV8_36_,
invtxn0_.IV_BLNO as IV9_36_, invtxn0_.IV_SHMODE as IV10_36_,
invtxn0_.IV_CARR as IV11_36_, invtxn0_.IV_BILLADD as IV12_36_,
invtxn0_.IV_CRTRMS as IV13_36_, invtxn0_.IV_TERMS as IV14_36_,
invtxn0_.IV_ATTDTLS as IV15_36_, invtxn0_.IV_TEXT as IV16_36_,
invtxn0_.IV_BTCHDTE as IV17_36_, invtxn0_.IV_ADVPAID as IV18_36_,
invtxn0_.IV_ADVADJ as IV19_36_, invtxn0_.IV_PRNTD as IV20_36_,
invtxn0_.IV_MODE as IV21_36_, invtxn0_.IV_DUEDATE as IV22_36_,
invtxn0_.IV_TARIFF as IV23_36_, invtxn0_.IV_TXNDTE as IV24_36_,
invtxn0_.IV_GPINVNO as IV25_36_, invtxn0_.IV_CRTDBY as IV26_36_,
invtxn0_.IV_MODBY as IV27_36_, invtxn0_.IV_CRTDDT as IV28_36_,
invtxn0_.IV_MODDT as IV29_36_, invtxn0_.IV_CUSNO as IV30_36_,
invtxn0_.IV_ASSGTPE as IV31_36_, invtxn0_.IV_ASSGNTO as IV32_36_,
invtxn0_.IV_STR1 as IV33_36_, invtxn0_.IV_STR2 as IV34_36_,
invtxn0_.IV_STR3 as IV35_36_, invtxn0_.IV_STR4 as IV36_36_,
invtxn0_.IV_STR5 as IV37_36_, invtxn0_.IV_STR6 as IV38_36_,
invtxn0_.IV_STR7 as IV39_36_, invtxn0_.IV_STR8 as IV40_36_,
invtxn0_.IV_STR9 as IV41_36_, invtxn0_.IV_STR10 as IV42_36_,
invtxn0_.IV_STR11 as IV43_36_, invtxn0_.IV_STR12 as IV44_36_,
invtxn0_.IV_STR13 as IV45_36_, invtxn0_.IV_STR14 as IV46_36_,
invtxn0_.IV_STR15 as IV47_36_, invtxn0_.IV_STR16 as IV48_36_,
invtxn0_.IV_STR17 as IV49_36_, invtxn0_.IV_STR18 as IV50_36_,
invtxn0_.IV_STR19 as IV51_36_, invtxn0_.IV_STR20 as IV52_36_,
invtxn0_.IV_STR21 as IV53_36_, invtxn0_.IV_STR22 as IV54_36_,
invtxn0_.IV_STR23 as IV55_36_, invtxn0_.IV_STR24 as IV56_36_,
invtxn0_.IV_STR25 as IV57_36_, invtxn0_.IV_NUM1 as IV58_36_,
invtxn0_.IV_NUM2 as IV59_36_, invtxn0_.IV_NUM3 as IV60_36_,
invtxn0_.IV_NUM4 as IV61_36_, invtxn0_.IV_NUM5 as IV62_36_,
invtxn0_.IV_NUM6 as IV63_36_, invtxn0_.IV_NUM7 as IV64_36_,
invtxn0_.IV_NUM8 as IV65_36_, invtxn0_.IV_NUM9 as IV66_36_,
invtxn0_.IV_NUM10 as IV67_36_, invtxn0_.IV_DATE1 as IV68_36_,
invtxn0_.IV_DATE2 as IV69_36_, invtxn0_.IV_DATE3 as IV70_36_,
invtxn0_.IV_DATE4 as IV71_36_, invtxn0_.IV_DATE5 as IV72_36_,
invtxn0_.IV_DATE6 as IV73_36_, invtxn0_.IV_DATE8 as IV74_36_,
invtxn0_.IV_DATE9 as IV75_36_, invtxn0_.IV_DATE10 as IV76_36_,
invtxn0_.IV_ORDNO as IV77_36_, invtxn0_.IV_FOR as IV78_36_,
invtxn0_.IV_REMTO as IV79_36_, invtxn0_.IV_PRODCDE as IV80_36_,
invtxn0_.IV_CNTRLNO as IV81_36_, invtxn0_.IV_IONUM as IV82_36_,
invtxn0_.IV_AUTHNO as IV83_36_, invtxn0_.IV_DEPTNO as IV84_36_,
invtxn0_.IV_LOC as IV85_36_, invtxn0_.IV_CLOSED as IV86_36_,
invtxn0_.TID as TID36_, invtxn0_.AID as AID36_, invtxn0_.IV_SOURCE as
IV89_36_, invtxn0_.IV_RECTYPE as IV90_36_, invtxn0_.IV_SLSORDNO as
IV91_36_, invtxn0_.IV_GAMT as IV92_36_, invtxn0_.IV_TXAMT as
IV93_36_, invtxn0_.IV_PDAMT as IV94_36_, invtxn0_.IV_BU as IV95_36_,
invtxn0_.IV_SHREF as IV96_36_, invtxn0_.IV_SHCLASS as IV97_36_,
invtxn0_.IV_SHDT as IV98_36_, invtxn0_.IV_PODATE as IV99_36_,
invtxn0_.IV_PROREF as IV100_36_, invtxn0_.IV_BLDATEFRO as IV101_36_,
invtxn0_.IV_BLDATETO as IV102_36_, invtxn0_.IV_BATCH as IV103_36_,
invtxn0_.IV_SLDATE as IV104_36_, invtxn0_.IV_GROUP as IV105_36_,
invtxn0_.IV_SHADDDET as IV106_36_, invtxn0_.IV_RESP as IV107_36_,
invtxn0_.IV_REMDT as IV108_36_, invtxn0_.IV_PROB as IV109_36_,
invtxn0_.IV_STID as IV110_36_, invtxn0_.rowid as rowid111_36_,
invtxn0_.EN_ID as EN112_36_, invtxn0_.STE_ID as STE113_36_,
invtxn0_.MTH_SETID as MTH114_36_, invtxn0_.CS_ID as CS115_36_,
invtxn0_.IV_TYPE as IV116_36_ from INV_TXN invtxn0_ where
invtxn0_.EN_ID=:1 and invtxn0_.IV_TYPE=:2 and invtxn0_.IV_SOURCE=:3
and (invtxn0_.IV_GPINVNO in (: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 , :53))
RATIONALE: The SQL statement with SQL_ID "bv25n45d81g64" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID bv25n45d81g64
CALL MODIFIED_ACCOUNT_AGE_CALC(:1, :2)
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(19% impact [32792 seconds])
FINDING 6: 7.3% impact (12514 seconds)
--------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: SQL Tuning, 4.9% benefit (8418 seconds)
ACTION: Investigate the SQL statement with SQL_ID "bv25n45d81g64" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID bv25n45d81g64
CALL MODIFIED_ACCOUNT_AGE_CALC(:1, :2)
RATIONALE: SQL statement with SQL_ID "bv25n45d81g64" was executed 17
times and had an average elapsed time of 495 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 20% of the database time spent in
processing the SQL statement with SQL_ID "bv25n45d81g64".
RATIONALE: Waiting for event "db file sequential read" in wait class
"User I/O" accounted for 14% of the database time spent in processing
the SQL statement with SQL_ID "bv25n45d81g64".
RATIONALE: Average CPU used per execution was 296 seconds.
RECOMMENDATION 2: SQL Tuning, 4.3% benefit (7471 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"csuzvgdc87uf3".
RELEVANT OBJECT: SQL statement with SQL_ID csuzvgdc87uf3 and
PLAN_HASH 2372689924
select * from ( select invtxn0_.IV_ID as col_0_0_, invtxn0_.IV_DATE
as col_1_0_ from INV_TXN invtxn0_ where (invtxn0_.IV_ID in ('3850633'
, '3811172' , '4000347' , '3773138' , '3836740' , '3855730' ,
'4000342' , '3690062' , '3778545' , '3798168' , '3678536' , '4000349'
, '3825281' , '3768649' , '3846775' , '4220931' , '4000344' ,
'3773137' , '3854205' , '3809850' , '3828528' , '3923190' , '4002796'
, '3821958' , '3832272' , '3674546' , '3792206' , '3857424' ,
'4000343' , '3852261' , '3833946' , '4002795' , '3857425' , '4002793'
, '3832271' , '3923189' , '4000348' , '4000346' , '3833945' ,
'3799801' , '3821957' , '3778544' , '3821954' , '4000345' , '3836741'
, '3806555' , '3799800' , '3839855' , '4002794' , '3830239' ,
'3835319' , '3821956' , '3828529' , '3792207' , '3806553' , '3838257'
, '3630814' , '3761719' , '3839854' , '3854204' , '3825280' ,
'3821955')) and (invtxn0_.IV_DATE in ('02-Mar-09' , '28-Jan-09' ,
'19-Jun-09' , '22-Dec-08' , '18-Feb-09' , '05-Mar-09' , '19-Jun-09' ,
'29-Oct-08' , '29-Dec-08' , '16-Jan-09' , '22-Oct-08' , '19-Jun-09' ,
'09-Feb-09' , '18-Dec-08' , '26-Feb-09' , '25-Nov-09' , '19-Jun-09' ,
'22-Dec-08' , '04-Mar-09' , '27-Jan-09' , '11-Feb-09' , '28-Apr-09' ,
'22-Jun-09' , '05-Feb-09' , '13-Feb-09' , '20-Oct-08' , '12-Jan-09' ,
'06-Mar-09' , '19-Jun-09' , '03-Mar-09' , '16-Feb-09' , '22-Jun-09' ,
'06-Mar-09' , '22-Jun-09' , '13-Feb-09' , '28-Apr-09' , '19-Jun-09' ,
'19-Jun-09' , '16-Feb-09' , '19-Jan-09' , '05-Feb-09' , '29-Dec-08' ,
'05-Feb-09' , '19-Jun-09' , '18-Feb-09' , '23-Jan-09' , '19-Jan-09' ,
'20-Feb-09' , '22-Jun-09' , '12-Feb-09' , '17-Feb-09' , '05-Feb-09' ,
'11-Feb-09' , '12-Jan-09' , '23-Jan-09' , '19-Feb-09' , '22-Sep-08' ,
'15-Dec-08' , '20-Feb-09' , '04-Mar-09' , '09-Feb-09' , '05-Feb-09'))
) where rownum <= :1
RATIONALE: SQL statement with SQL_ID "csuzvgdc87uf3" was executed 1
times and had an average elapsed time of 7744 seconds.
RATIONALE: Average CPU used per execution was 7471 seconds.
FINDING 7: 5.9% impact (10091 seconds)
--------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
RECOMMENDATION 1: SQL Tuning, 7.7% benefit (13204 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"dcnp1pu6hd5fs".
RELEVANT OBJECT: SQL statement with SQL_ID dcnp1pu6hd5fs and
PLAN_HASH 3177380161
select invtxn0_.IV_ID as IV1_36_0_, invtxn0_.IV_DATE as IV2_36_0_,
csthdr1_.CS_ID as CS1_22_1_, accrtxn2_.IV_ID as IV1_1_2_,
accrtxn2_.IV_DATE as IV2_1_2_, invtxn0_.IV_PINVID as IV3_36_0_,
invtxn0_.IV_SBID as IV4_36_0_, invtxn0_.IV_CURR as IV5_36_0_,
invtxn0_.IV_CHGS as IV6_36_0_, invtxn0_.IV_TLAMT as IV7_36_0_,
invtxn0_.IV_SHPADD as IV8_36_0_, invtxn0_.IV_BLNO as IV9_36_0_,
invtxn0_.IV_SHMODE as IV10_36_0_, invtxn0_.IV_CARR as IV11_36_0_,
invtxn0_.IV_BILLADD as IV12_36_0_, invtxn0_.IV_CRTRMS as IV13_36_0_,
invtxn0_.IV_TERMS as IV14_36_0_, invtxn0_.IV_ATTDTLS as IV15_36_0_,
invtxn0_.IV_TEXT as IV16_36_0_, invtxn0_.IV_BTCHDTE as IV17_36_0_,
invtxn0_.IV_ADVPAID as IV18_36_0_, invtxn0_.IV_ADVADJ as IV19_36_0_,
invtxn0_.IV_PRNTD as IV20_36_0_, invtxn0_.IV_MODE as IV21_36_0_,
invtxn0_.IV_DUEDATE as IV22_36_0_, invtxn0_.IV_TARIFF as IV23_36_0_,
invtxn0_.IV_TXNDTE as IV24_36_0_, invtxn0_.IV_GPINVNO as IV25_36_0_,
invtxn0_.IV_CRTDBY as IV26_36_0_, invtxn0_.IV_MODBY as IV27_36_0_,
invtxn0_.IV_CRTDDT as IV28_36_0_, invtxn0_.IV_MODDT as IV29_36_0_,
invtxn0_.IV_CUSNO as IV30_36_0_, invtxn0_.IV_ASSGTPE as IV31_36_0_,
invtxn0_.IV_ASSGNTO as IV32_36_0_, invtxn0_.IV_STR1 as IV33_36_0_,
invtxn0_.IV_STR2 as IV34_36_0_, invtxn0_.IV_STR3 as IV35_36_0_,
invtxn0_.IV_STR4 as IV36_36_0_, invtxn0_.IV_STR5 as IV37_36_0_,
invtxn0_.IV_STR6 as IV38_36_0_, invtxn0_.IV_STR7 as IV39_36_0_,
invtxn0_.IV_STR8 as IV40_36_0_, invtxn0_.IV_STR9 as IV41_36_0_,
invtxn0_.IV_STR10 as IV42_36_0_, invtxn0_.IV_STR11 as IV43_36_0_,
invtxn0_.IV_STR12 as IV44_36_0_, invtxn0_.IV_STR13 as IV45_36_0_,
invtxn0_.IV_STR14 as IV46_36_0_, invtxn0_.IV_STR15 as IV47_36_0_,
invtxn0_.IV_STR16 as IV48_36_0_, invtxn0_.IV_STR17 as IV49_36_0_,
invtxn0_.IV_STR18 as IV50_36_0_, invtxn0_.IV_STR19 as IV51_36_0_,
invtxn0_.IV_STR20 as IV52_36_0_, invtxn0_.IV_STR21 as IV53_36_0_,
invtxn0_.IV_STR22 as IV54_36_0_, invtxn0_.IV_STR23 as IV55_36_0_,
invtxn0_.IV_STR24 as IV56_36_0_, invtxn0_.IV_STR25 as IV57_36_0_,
invtxn0_.IV_NUM1 as IV58_36_0_, invtxn0_.IV_NUM2 as IV59_36_0_,
invtxn0_.IV_NUM3 as IV60_36_0_, invtxn0_.IV_NUM4 as IV61_36_0_,
invtxn0_.IV_NUM5 as IV62_36_0_, invtxn0_.IV_NUM6 as IV63_36_0_,
invtxn0_.IV_NUM7 as IV64_36_0_, invtxn0_.IV_NUM8 as IV65_36_0_,
invtxn0_.IV_NUM9 as IV66_36_0_, invtxn0_.IV_NUM10 as IV67_36_0_,
invtxn0_.IV_DATE1 as IV68_36_0_, invtxn0_.IV_DATE2 as IV69_36_0_,
invtxn0_.IV_DATE3 as IV70_36_0_, invtxn0_.IV_DATE4 as IV71_36_0_,
invtxn0_.IV_DATE5 as IV72_36_0_, invtxn0_.IV_DATE6 as IV73_36_0_,
invtxn0_.IV_DATE8 as IV74_36_0_, invtxn0_.IV_DATE9 as IV75_36_0_,
invtxn0_.IV_DATE10 as IV76_36_0_, invtxn0_.IV_ORDNO as IV77_36_0_,
invtxn0_.IV_FOR as IV78_36_0_, invtxn0_.IV_REMTO as IV79_36_0_,
invtxn0_.IV_PRODCDE as IV80_36_0_, invtxn0_.IV_CNTRLNO as IV81_36_0_,
invtxn0_.IV_IONUM as IV82_36_0_, invtxn0_.IV_AUTHNO as IV83_36_0_,
invtxn0_.IV_DEPTNO as IV84_36_0_, invtxn0_.IV_LOC as IV85_36_0_,
invtxn0_.IV_CLOSED as IV86_36_0_, invtxn0_.TID as TID36_0_,
invtxn0_.AID as AID36_0_, invtxn0_.IV_SOURCE as IV89_36_0_,
invtxn0_.IV_RECTYPE as IV90_36_0_, invtxn0_.IV_SLSORDNO as
IV91_36_0_, invtxn0_.IV_GAMT as IV92_36_0_, invtxn0_.IV_TXAMT as
IV93_36_0_, invtxn0_.IV_PDAMT as IV94_36_0_, invtxn0_.IV_BU as
IV95_36_0_, invtxn0_.IV_SHREF as IV96_36_0_, invtxn0_.IV_SHCLASS as
IV97_36_0_, invtxn0_.IV_SHDT as IV98_36_0_, invtxn0_.IV_PODATE as
IV99_36_0_, invtxn0_.IV_PROREF as IV100_36_0_, invtxn0_.IV_BLDATEFRO
as IV101_36_0_, invtxn0_.IV_BLDATETO as IV102_36_0_,
invtxn0_.IV_BATCH as IV103_36_0_, invtxn0_.IV_SLDATE as IV104_36_0_,
invtxn0_.IV_GROUP as IV105_36_0_, invtxn0_.IV_SHADDDET as
IV106_36_0_, invtxn0_.IV_RESP as IV107_36_0_, invtxn0_.IV_REMDT as
IV108_36_0_, invtxn0_.IV_PROB as IV109_36_0_, invtxn0_.IV_STID as
IV110_36_0_, invtxn0_.rowid as rowid111_36_0_, invtxn0_.EN_ID as
EN112_36_0_, invtxn0_.STE_ID as STE113_36_0_, invtxn0_.MTH_SETID as
MTH114_36_0_, invtxn0_.CS_ID as CS115_36_0_, invtxn0_.IV_TYPE as
IV116_36_0_, csthdr1_.CS_PID as CS2_22_1_, csthdr1_.CS_NAME as
CS3_22_1_, csthdr1_.CS_ACTIVE as CS4_22_1_, csthdr1_.CS_ALIAS as
CS5_22_1_, csthdr1_.CS_REF1 as CS6_22_1_, csthdr1_.CS_REF2 as
CS7_22_1_, csthdr1_.CS_OREF as CS8_22_1_, csthdr1_.CS_OSUPP as
CS9_22_1_, csthdr1_.CS_CDSCT as CS10_22_1_, csthdr1_.CS_DELM as
CS11_22_1_, csthdr1_.CS_SLPN as CS12_22_1_, csthdr1_.CS_RESP as
CS13_22_1_, csthdr1_.CS_VATC as CS14_22_1_, csthdr1_.CS_CURR as
CS15_22_1_, csthdr1_.CS_XCHTPE as CS16_22_1_, csthdr1_.CS_DTEFRM as
CS17_22_1_, csthdr1_.CS_PAYR as CS18_22_1_, csthdr1_.CS_STATS as
CS19_22_1_, csthdr1_.CS_CRLM1 as CS20_22_1_, csthdr1_.CS_CRLM2 as
CS21_22_1_, csthdr1_.CS_CRLM3 as CS22_22_1_, csthdr1_.CS_STOP as
CS23_22_1_, csthdr1_.CS_OSINAMT as CS24_22_1_, csthdr1_.CS_ODINAMT as
CS25_22_1_, csthdr1_.CS_LINVDTE as CS26_22_1_, csthdr1_.CS_EDTE as
CS27_22_1_, csthdr1_.CS_SUMINV as CS28_22_1_, csthdr1_.CS_INTINV as
CS29_22_1_, csthdr1_.CS_INTRUL as CS30_22_1_, csthdr1_.CS_CRATING as
CS31_22_1_, csthdr1_.CS_CREVIEW as CS32_22_1_, csthdr1_.CS_CRUPDTE as
CS33_22_1_, csthdr1_.CS_DB as CS34_22_1_, csthdr1_.CS_LANG as
CS35_22_1_, csthdr1_.CS_PSTID as CS36_22_1_, csthdr1_.CS_REGION as
CS37_22_1_, csthdr1_.CS_RMKS as CS38_22_1_, csthdr1_.CS_STMT as
CS39_22_1_, csthdr1_.CS_CRTDBY as CS40_22_1_, csthdr1_.CS_MODBY as
CS41_22_1_, csthdr1_.CS_CRTDDT as CS42_22_1_, csthdr1_.CS_MODDT as
CS43_22_1_, csthdr1_.CS_CSGID as CS44_22_1_, csthdr1_.EN_ID as
EN45_22_1_, csthdr1_.STE_ID as STE46_22_1_, csthdr1_.CS_STR1 as
CS47_22_1_, csthdr1_.CS_STR2 as CS48_22_1_, csthdr1_.CS_STR3 as
CS49_22_1_, csthdr1_.CS_STR4 as CS50_22_1_, csthdr1_.CS_STR5 as
CS51_22_1_, csthdr1_.CS_STR6 as CS52_22_1_, csthdr1_.CS_STR7 as
CS53_22_1_, csthdr1_.CS_STR8 as CS54_22_1_, csthdr1_.CS_STR9 as
CS55_22_1_, csthdr1_.CS_STR10 as CS56_22_1_, csthdr1_.CS_STR11 as
CS57_22_1_, csthdr1_.CS_STR12 as CS58_22_1_, csthdr1_.CS_STR13 as
CS59_22_1_, csthdr1_.CS_STR14 as CS60_22_1_, csthdr1_.CS_STR15 as
CS61_22_1_, csthdr1_.CS_STR16 as CS62_22_1_, csthdr1_.CS_STR17 as
CS63_22_1_, csthdr1_.CS_STR18 as CS64_22_1_, csthdr1_.CS_STR19 as
CS65_22_1_, csthdr1_.CS_STR20 as CS66_22_1_, csthdr1_.CS_STR21 as
CS67_22_1_, csthdr1_.CS_STR22 as CS68_22_1_, csthdr1_.CS_STR23 as
CS69_22_1_, csthdr1_.CS_STR24 as CS70_22_1_, csthdr1_.CS_STR25 as
CS71_22_1_, csthdr1_.CS_NUM1 as CS72_22_1_, csthdr1_.CS_NUM2 as
CS73_22_1_, csthdr1_.CS_NUM3 as CS74_22_1_, csthdr1_.CS_NUM4 as
CS75_22_1_, csthdr1_.CS_NUM5 as CS76_22_1_, csthdr1_.CS_NUM6 as
CS77_22_1_, csthdr1_.CS_NUM7 as CS78_22_1_, csthdr1_.CS_NUM8 as
CS79_22_1_, csthdr1_.CS_NUM9 as CS80_22_1_, csthdr1_.CS_NUM10 as
CS81_22_1_, csthdr1_.CS_NUM11 as CS82_22_1_, csthdr1_.CS_NUM12 as
CS83_22_1_, csthdr1_.CS_NUM13 as CS84_22_1_, csthdr1_.CS_NUM14 as
CS85_22_1_, csthdr1_.CS_NUM15 as CS86_22_1_, csthdr1_.CS_NUM16 as
CS87_22_1_, csthdr1_.CS_NUM17 as CS88_22_1_, csthdr1_.CS_NUM18 as
CS89_22_1_, csthdr1_.CS_NUM19 as CS90_22_1_, csthdr1_.CS_NUM20 as
CS91_22_1_, csthdr1_.CS_DATE1 as CS92_22_1_, csthdr1_.CS_DATE2 as
CS93_22_1_, csthdr1_.CS_DATE3 as CS94_22_1_, csthdr1_.CS_DATE4 as
CS95_22_1_, csthdr1_.CS_DATE5 as CS96_22_1_, csthdr1_.CS_DATE6 as
CS97_22_1_, csthdr1_.CS_DATE7 as CS98_22_1_, csthdr1_.CS_DATE8 as
CS99_22_1_, csthdr1_.CS_DATE9 as CS100_22_1_, csthdr1_.CS_DATE10 as
CS101_22_1_, csthdr1_.CS_TERMS as CS102_22_1_, csthdr1_.CS_KEYMARK as
CS103_22_1_, csthdr1_.AID as AID22_1_, csthdr1_.CS_IND as
CS105_22_1_, csthdr1_.CS_URL as CS106_22_1_, csthdr1_.AC_TYPE as
AC107_22_1_, csthdr1_.CS_AVGDP as CS108_22_1_, csthdr1_.CS_BKT12 as
CS109_22_1_, csthdr1_.CS_ERAM as CS110_22_1_, csthdr1_.rowid as
rowid111_22_1_, csthdr1_.PY_CODE as PY112_22_1_, csthdr1_.PC_ID as
PC113_22_1_, csthdr1_.CY_ID as CY114_22_1_, csthdr1_.PR_ID as
PR115_22_1_, csthdr1_.CS_TYPE as CS116_22_1_, csthdr1_.DT_ID as
DT117_22_1_, accrtxn2_.SB_ID as SB3_1_2_, accrtxn2_.AR_BTHDTE as
AR4_1_2_, accrtxn2_.AR_PAYR as AR5_1_2_, accrtxn2_.AR_INVVNS as
AR6_1_2_, accrtxn2_.AR_INVVNO as AR7_1_2_, accrtxn2_.AR_DESC as
AR8_1_2_, accrtxn2_.AR_SLSPRN as AR9_1_2_, accrtxn2_.AR_CUSGRP as
AR10_1_2_, accrtxn2_.AR_XCHRTPE as AR11_1_2_, accrtxn2_.AR_XCHRTE as
AR12_1_2_, accrtxn2_.AR_FCURAMT as AR13_1_2_, accrtxn2_.AR_VATAMT as
AR14_1_2_, accrtxn2_.AR_CHGTPE as AR15_1_2_, accrtxn2_.AR_MRKUP as
AR16_1_2_, accrtxn2_.AR_FWUDTE as AR17_1_2_, accrtxn2_.AR_ACCDTE as
AR18_1_2_, accrtxn2_.ACC_CODE as ACC19_1_2_, accrtxn2_.AR_BANK as
AR20_1_2_, accrtxn2_.AR_CDSCT as AR21_1_2_, accrtxn2_.AR_PYTMTD as
AR22_1_2_, accrtxn2_.AR_RECCDE as AR23_1_2_, accrtxn2_.AR_RECDTE as
AR24_1_2_, accrtxn2_.AR_ADVCDE as AR25_1_2_, accrtxn2_.AR_PYREM as
AR26_1_2_, accrtxn2_.AR_LREMDTE as AR27_1_2_, accrtxn2_.AR_PTREMDTE
as AR28_1_2_, accrtxn2_.AR_STPBY as AR29_1_2_, accrtxn2_.AR_STPDTE as
AR30_1_2_, accrtxn2_.AR_NOPYTRM as AR31_1_2_, accrtxn2_.AR_INTINVB as
AR32_1_2_, accrtxn2_.AR_INTINVS as AR33_1_2_, accrtxn2_.AR_CINTAMT as
AR34_1_2_, accrtxn2_.AR_FLGCDE as AR35_1_2_, accrtxn2_.AR_GPINDTE as
AR36_1_2_, accrtxn2_.AR_TXT as AR37_1_2_, accrtxn2_.AR_DOCLNK as
AR38_1_2_, accrtxn2_.AR_DRTPRD as AR39_1_2_, accrtxn2_.AR_DRPRDTE as
AR40_1_2_, accrtxn2_.AR_PRODGRP as AR41_1_2_, accrtxn2_.AR_ORDNO as
AR42_1_2_, accrtxn2_.AR_TXNDTE as AR43_1_2_, accrtxn2_.AR_FRTTRM as
AR44_1_2_, accrtxn2_.AR_NBR as AR45_1_2_, accrtxn2_.AR_PRINT as
AR46_1_2_, accrtxn2_.AR_CRTDBY as AR47_1_2_, accrtxn2_.AR_MODBY as
AR48_1_2_, accrtxn2_.AR_CRTDDT as AR49_1_2_, accrtxn2_.AR_MODDT as
AR50_1_2_, accrtxn2_.EN_ID as EN51_1_2_, accrtxn2_.STE_ID as
STE52_1_2_, accrtxn2_.AR_AMT as AR53_1_2_, accrtxn2_.AR_OUTAMT as
AR54_1_2_, accrtxn2_.AR_STR1 as AR55_1_2_, accrtxn2_.AR_STR2 as
AR56_1_2_, accrtxn2_.AR_STR3 as AR57_1_2_, accrtxn2_.AR_STR4 as
AR58_1_2_, accrtxn2_.AR_STR5 as AR59_1_2_, accrtxn2_.AR_STR6 as
AR60_1_2_, accrtxn2_.AR_STR7 as AR61_1_2_, accrtxn2_.AR_STR8 as
AR62_1_2_, accrtxn2_.AR_STR9 as AR63_1_2_, accrtxn2_.AR_STR10 as
AR64_1_2_, accrtxn2_.AR_STR11 as AR65_1_2_, accrtxn2_.AR_STR12 as
AR66_1_2_, accrtxn2_.AR_STR13 as AR67_1_2_, accrtxn2_.AR_STR14 as
AR68_1_2_, accrtxn2_.AR_STR15 as AR69_1_2_, accrtxn2_.AR_NUM1 as
AR70_1_2_, accrtxn2_.AR_NUM2 as AR71_1_2_, accrtxn2_.AR_NUM3 as
AR72_1_2_, accrtxn2_.AR_NUM4 as AR73_1_2_, accrtxn2_.AR_NUM5 as
AR74_1_2_, accrtxn2_.AR_NUM6 as AR75_1_2_, accrtxn2_.AR_NUM7 as
AR76_1_2_, accrtxn2_.AR_NUM8 as AR77_1_2_, accrtxn2_.AR_NUM9 as
AR78_1_2_, accrtxn2_.AR_NUM10 as AR79_1_2_, accrtxn2_.AR_DATE1 as
AR80_1_2_, accrtxn2_.AR_DATE2 as AR81_1_2_, accrtxn2_.AR_DATE3 as
AR82_1_2_, accrtxn2_.AR_DATE4 as AR83_1_2_, accrtxn2_.AR_DATE5 as
AR84_1_2_, accrtxn2_.AR_DATE6 as AR85_1_2_, accrtxn2_.AR_DATE7 as
AR86_1_2_, accrtxn2_.AR_DATE8 as AR87_1_2_, accrtxn2_.AR_DATE9 as
AR88_1_2_, accrtxn2_.AR_DATE10 as AR89_1_2_, accrtxn2_.AR_PROMDTE as
AR90_1_2_, accrtxn2_.TID as TID1_2_, accrtxn2_.PY_TERMS as PY92_1_2_,
accrtxn2_.PR_ID as PR93_1_2_ from INV_TXN invtxn0_, CST_HDR csthdr1_,
ACCR_TXN accrtxn2_, CSTPE_HDR cstpehdr4_ where
invtxn0_.CS_ID=csthdr1_.CS_ID(+) and
invtxn0_.IV_ID=accrtxn2_.IV_ID(+) and
invtxn0_.IV_DATE=accrtxn2_.IV_DATE(+) and
csthdr1_.CS_TYPE=cstpehdr4_.CS_TYPE and (invtxn0_.CS_ID in (: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 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 ,
:50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61
, :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 ,
:73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84
, :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 ,
:96 , :97 , :98 , :99 , :100)) and
invtxn0_.IV_TYPE=cstpehdr4_.CS_NAME and invtxn0_.IV_CLOSED=0
RATIONALE: SQL statement with SQL_ID "dcnp1pu6hd5fs" was executed 129
times and had an average elapsed time of 102 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
78 seconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(19% impact [32792 seconds])
FINDING 8: 4.2% impact (7255 seconds)
-------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 4.2% benefit (7255 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 1.8 M per second for reads and 36 K per second for
writes. The average response time for single block reads was 13
milliseconds.
RECOMMENDATION 2: Host Configuration, 2.4% benefit (4046 seconds)
ACTION: The performance of file +DATA/capgent/capgent_data.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
"+DATA/capgent/capgent_data.dbf"
RATIONALE: The average response time for single block reads for this
file was 12 milliseconds.
RECOMMENDATION 3: Host Configuration, 1.6% benefit (2715 seconds)
ACTION: The performance of file +DATA/capgent/capgent_index.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
"+DATA/capgent/capgent_index.dbf"
RATIONALE: The average response time for single block reads for this
file was 55 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(19% impact [32792 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
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 28% 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.