DETAILED ADDM REPORT FOR TASK 'TASK_162107' WITH ID 162107
----------------------------------------------------------
Analysis Period: 10-JUN-2011 from 06:05:46 to 15:30: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 21461 to 21471
Database Time: 8892 seconds
Average Database Load: .3 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 84% impact (7428 seconds)
------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: Application Analysis, 39% benefit (3487 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please
refer to other findings in this task about parsing for further
details.
RECOMMENDATION 2: SQL Tuning, 38% benefit (3391 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"08qxg52132buh".
RELEVANT OBJECT: SQL statement with SQL_ID 08qxg52132buh and
PLAN_HASH 4100594498
select attmtslog0_.ALOG_ID as col_0_0_, attmtslog0_.AT_DESC as
col_1_0_, attmtslog0_.AT_URL as col_2_0_, attmtslog0_.AT_IVID as
col_3_0_, attmtslog0_.AT_DATE as col_4_0_ from ATTMTS_LOG attmtslog0_
where (attmtslog0_.AT_IVID in ('9653058' , '9650832' , '9652994' ,
'9654502' , '9653158' , '9652750' , '9652734' , '9653802' , '9648158'
, '9648179' , '9648289' , '9653240' , '9650960' , '9654248' ,
'9651160' , '9648408' , '9648704' , '9648723' , '9648718' , '9648685'
, '9649238' , '9647701' , '9650922' , '9647502' , '9652138' ,
'9651580' , '9654130' , '9654232' , '9648021' , '9650250' , '9654233'
, '9650582' , '9647023' , '9649924' , '9654012' , '9654305' ,
'9650456' , '9801292' , '9908912' , '9885474' , '9885292' ,
'10166945' , '9761575' , '9999362' , '9885366' , '9927730' ,
'10181954' , '10198297' , '9999377' , '9927713' , '9885320' ,
'10075720' , '10075769' , '10075643' , '10075610' , '9885427')) and
(attmtslog0_.AT_DATE in ('22-APR-11' , '05-APR-11' , '05-APR-11' ,
'26-APR-11' , '26-APR-11' , '18-FEB-11' , '01-FEB-11' , '15-MAR-11' ,
'26-JAN-11' , '18-FEB-11' , '23-MAR-11' , '01-FEB-11' , '11-APR-11' ,
'16-MAR-11' , '28-APR-11' , '07-APR-11' , '18-FEB-11' , '04-MAR-11' ,
'03-MAR-11' , '02-FEB-11' , '03-MAR-11' , '03-MAR-11' , '30-MAR-11' ,
'21-APR-11' , '26-APR-11' , '19-APR-11' , '26-APR-11' , '01-FEB-11' ,
'20-APR-11' , '22-FEB-11' , '01-FEB-11' , '24-JAN-11' , '16-MAR-11' ,
'05-APR-11' , '11-APR-11' , '03-MAR-11' , '07-APR-11' , '09-MAY-11' ,
'17-MAY-11' , '16-MAY-11' , '16-MAY-11' , '03-JUN-11' , '06-MAY-11' ,
'25-MAY-11' , '16-MAY-11' , '19-MAY-11' , '06-JUN-11' , '08-JUN-11' ,
'25-MAY-11' , '19-MAY-11' , '16-MAY-11' , '31-MAY-11' , '01-JUN-11' ,
'31-MAY-11' , '31-MAY-11' , '16-MAY-11'))
RATIONALE: SQL statement with SQL_ID "08qxg52132buh" was executed 1
times and had an average elapsed time of 3390 seconds.
RATIONALE: Average CPU used per execution was 3390 seconds.
RECOMMENDATION 3: SQL Tuning, 4% benefit (352 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"gzmkn0ysnnkzk".
RELEVANT OBJECT: SQL statement with SQL_ID gzmkn0ysnnkzk and
PLAN_HASH 2239883860
SELECT inv.cs_id ,SUM(CASE WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate)
BETWEEN -999 AND 0) THEN NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 1 AND 30) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 31 AND 60) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 61 AND 90) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 91 AND 99999) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),0,0,0,0,0,0,0 FROM inv_txn inv,
accr_txn accr WHERE inv.IV_ID = accr.IV_ID AND
inv.iv_date=accr.iv_date AND inv.iv_id in (SELECT inv2.iv_id FROM
inv_txn inv2 where inv2.cs_id in (SELECT DISTINCT CS_ID FROM inv_txn
where iv_moddt>=to_date('10/06/11:05:39', 'dd/mm/YY:HH24:MI') AND
en_id=4180 AND iv_type='Invoice') AND inv2.iv_closed=0 AND
inv2.iv_type='Invoice') GROUP BY inv.cs_id
RATIONALE: SQL statement with SQL_ID "gzmkn0ysnnkzk" was executed 1
times and had an average elapsed time of 351 seconds.
RATIONALE: Average CPU used per execution was 326 seconds.
RECOMMENDATION 4: SQL Tuning, 2.5% benefit (225 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"gjyudam42mn52".
RELEVANT OBJECT: SQL statement with SQL_ID gjyudam42mn52 and
PLAN_HASH 3183920376
select * from ( select row_.*, rownum rownum_ from ( select
invtxn0_.IV_RECTYPE as col_0_0_, invtxn0_.IV_STR4 as col_1_0_,
csthdr2_.CS_REF1 as col_2_0_, csthdr2_.CS_NAME as col_3_0_,
invtxn0_.IV_TEXT as col_4_0_, stshdr1_.ST_NAME as col_5_0_,
invtxn0_.IV_STR8 as col_6_0_, invtxn0_.IV_TERMS as col_7_0_,
invtxn0_.IV_SHREF as col_8_0_, invtxn0_.IV_ASSGNTO as col_9_0_,
invtxn0_.IV_RESP as col_10_0_, invtxn0_.IV_GPINVNO as col_11_0_,
invtxn0_.IV_STR11 as col_12_0_, invtxn0_.IV_SLSORDNO as col_13_0_,
invtxn0_.IV_STR5 as col_14_0_, invtxn0_.IV_ORDNO as col_15_0_,
invtxn0_.IV_BLDATEFRO as col_16_0_, invtxn0_.IV_BLDATETO as
col_17_0_, invtxn0_.IV_TLAMT as col_18_0_, accrtxn4_.AR_OUTAMT as
col_19_0_, invtxn0_.IV_DATE as col_20_0_, invtxn0_.IV_DUEDATE as
col_21_0_, trunc(current_date-invtxn0_.IV_DUEDATE) as col_22_0_,
(select count(cmtslogs5_.CM_IVID) from CMTS_LOG cmtslogs5_ where
invtxn0_.IV_ID=cmtslogs5_.CM_IVID and
invtxn0_.IV_DATE=cmtslogs5_.CM_IVDATE and ( cmtslogs5_.cm_iscurr=1) )
as col_23_0_, invtxn0_.IV_MODDT as col_24_0_, invtxn0_.IV_STR7 as
col_25_0_, (select count(*) from TODO_LOG todologs6_ where
invtxn0_.IV_ID=todologs6_.TO_IVID and
invtxn0_.IV_DATE=todologs6_.TO_IVDATE and (todologs6_.TO_CLOSED not
in ('Closed' , 'Completed' , 'Skipped'))) as col_26_0_,
invtxn0_.IV_ID as col_27_0_, invtxn0_.CS_ID as col_28_0_,
invtxn0_.IV_STID as col_29_0_, stshdr1_.ST_ICON as col_30_0_ from
INV_TXN invtxn0_, STS_HDR stshdr1_, CST_HDR csthdr2_, ACCR_TXN
accrtxn4_ where invtxn0_.IV_STID=stshdr1_.ST_ID(+) and
invtxn0_.CS_ID=csthdr2_.CS_ID and invtxn0_.IV_ID=accrtxn4_.IV_ID and
invtxn0_.IV_DATE=accrtxn4_.IV_DATE and (invtxn0_.EN_ID in (4060)) and
invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 order by
invtxn0_.rowid ASC ) row_ where rownum <= :1) where rownum_ > :2
RATIONALE: SQL statement with SQL_ID "gjyudam42mn52" was executed 117
times and had an average elapsed time of 1.9 seconds.
RATIONALE: Average CPU used per execution was 1.9 seconds.
FINDING 2: 61% impact (5397 seconds)
------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 38% benefit (3391 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"08qxg52132buh".
RELEVANT OBJECT: SQL statement with SQL_ID 08qxg52132buh and
PLAN_HASH 4100594498
select attmtslog0_.ALOG_ID as col_0_0_, attmtslog0_.AT_DESC as
col_1_0_, attmtslog0_.AT_URL as col_2_0_, attmtslog0_.AT_IVID as
col_3_0_, attmtslog0_.AT_DATE as col_4_0_ from ATTMTS_LOG attmtslog0_
where (attmtslog0_.AT_IVID in ('9653058' , '9650832' , '9652994' ,
'9654502' , '9653158' , '9652750' , '9652734' , '9653802' , '9648158'
, '9648179' , '9648289' , '9653240' , '9650960' , '9654248' ,
'9651160' , '9648408' , '9648704' , '9648723' , '9648718' , '9648685'
, '9649238' , '9647701' , '9650922' , '9647502' , '9652138' ,
'9651580' , '9654130' , '9654232' , '9648021' , '9650250' , '9654233'
, '9650582' , '9647023' , '9649924' , '9654012' , '9654305' ,
'9650456' , '9801292' , '9908912' , '9885474' , '9885292' ,
'10166945' , '9761575' , '9999362' , '9885366' , '9927730' ,
'10181954' , '10198297' , '9999377' , '9927713' , '9885320' ,
'10075720' , '10075769' , '10075643' , '10075610' , '9885427')) and
(attmtslog0_.AT_DATE in ('22-APR-11' , '05-APR-11' , '05-APR-11' ,
'26-APR-11' , '26-APR-11' , '18-FEB-11' , '01-FEB-11' , '15-MAR-11' ,
'26-JAN-11' , '18-FEB-11' , '23-MAR-11' , '01-FEB-11' , '11-APR-11' ,
'16-MAR-11' , '28-APR-11' , '07-APR-11' , '18-FEB-11' , '04-MAR-11' ,
'03-MAR-11' , '02-FEB-11' , '03-MAR-11' , '03-MAR-11' , '30-MAR-11' ,
'21-APR-11' , '26-APR-11' , '19-APR-11' , '26-APR-11' , '01-FEB-11' ,
'20-APR-11' , '22-FEB-11' , '01-FEB-11' , '24-JAN-11' , '16-MAR-11' ,
'05-APR-11' , '11-APR-11' , '03-MAR-11' , '07-APR-11' , '09-MAY-11' ,
'17-MAY-11' , '16-MAY-11' , '16-MAY-11' , '03-JUN-11' , '06-MAY-11' ,
'25-MAY-11' , '16-MAY-11' , '19-MAY-11' , '06-JUN-11' , '08-JUN-11' ,
'25-MAY-11' , '19-MAY-11' , '16-MAY-11' , '31-MAY-11' , '01-JUN-11' ,
'31-MAY-11' , '31-MAY-11' , '16-MAY-11'))
RATIONALE: SQL statement with SQL_ID "08qxg52132buh" was executed 1
times and had an average elapsed time of 3390 seconds.
RECOMMENDATION 2: SQL Tuning, 8.5% benefit (757 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8ypzr7b9bpsd8".
RELEVANT OBJECT: SQL statement with SQL_ID 8ypzr7b9bpsd8 and
PLAN_HASH 730743856
select accounttov0_.REFID as REFID97_1_, accounttov0_.ACCOUNTTXNREFID
as ACCOUNTT2_97_1_, accounttov0_.ACCOUNTTXNREFDATE as
ACCOUNTT3_97_1_, accounttov0_.ACCOUNTID as ACCOUNTID97_1_,
accounttov0_.TXNREFID as TXNREFID97_1_, accounttov0_.PARENTACCOUNT as
PARENTAC6_97_1_, accounttov0_.ENTITY as ENTITY97_1_,
accounttov0_.SITEID as SITEID97_1_, accounttov0_.ASSIGNEDTO as
ASSIGNEDTO97_1_, accounttov0_.TXNASSTPE as TXNASSTPE97_1_,
accounttov0_.ACCOUNTNAME as ACCOUNT11_97_1_, accounttov0_.LANGUAGE as
LANGUAGE97_1_, accounttov0_.ACCOUNTCURRENCY as ACCOUNT13_97_1_,
accounttov0_.ACCOUNTGROUPID as ACCOUNT14_97_1_,
accounttov0_.ACCOUNTRESP as ACCOUNT15_97_1_,
accounttov0_.ACCOUNTSALEPERSON as ACCOUNT16_97_1_,
accounttov0_.ACCOUNTWEBSITE as ACCOUNT17_97_1_,
accounttov0_.ACCOUNTVAT as ACCOUNTVAT97_1_, accounttov0_.TERMS as
TERMS97_1_, accounttov0_.SOURCE as SOURCE97_1_, accounttov0_.ACTIVE
as ACTIVE97_1_, accounttov0_.CLOSED as CLOSED97_1_,
accounttov0_.ACCOUNTCRTDBY as ACCOUNT23_97_1_,
accounttov0_.ACCOUNTCRTDATE as ACCOUNT24_97_1_,
accounttov0_.ACCOUNTMODBY as ACCOUNT25_97_1_,
accounttov0_.ACCOUNTMODDATE as ACCOUNT26_97_1_,
accounttov0_.TXNCRTDBY as TXNCRTDBY97_1_, accounttov0_.TXNCRTDATE as
TXNCRTDATE97_1_, accounttov0_.TXNMODBY as TXNMODBY97_1_,
accounttov0_.TXNMODDATE as TXNMODDATE97_1_,
accounttov0_.LASTACTIONDATE as LASTACT31_97_1_,
accounttov0_.LASTACTION as LASTACTION97_1_, accounttov0_.LASTACTIONBY
as LASTACT33_97_1_, accounttov0_.ACCOUNTTYPE as ACCOUNT34_97_1_,
accounttov0_.TXNTYPE as TXNTYPE97_1_, accounttov0_.STATUSID as
STATUSID97_1_, accounttov0_.TXNPARENTID as TXNPARE37_97_1_,
accounttov0_.TXNCURRENCY as TXNCURR38_97_1_, accounttov0_.TXNSHIPMODE
as TXNSHIP39_97_1_, accounttov0_.TXNTERMS as TXNTERMS97_1_,
accounttov0_.TXNFOR as TXNFOR97_1_, accounttov0_.TXNREMITTO as
TXNREMITTO97_1_, accounttov0_.TXNPRODCDE as TXNPRODCDE97_1_,
accounttov0_.TXNLOC as TXNLOC97_1_, accounttov0_.TXNRECTYPE as
TXNRECTYPE97_1_, accounttov0_.TXNBU as TXNBU97_1_,
accounttov0_.TXNGROUP as TXNGROUP97_1_, accounttov0_.TXNBATCH as
TXNBATCH97_1_, accounttov0_.AID as AID97_1_, accounttov0_.TXNSHIPADD
as TXNSHIPADD97_1_, accounttov0_.TXNBILLADD as TXNBILLADD97_1_,
accounttov0_.TXNCRTTRMS as TXNCRTTRMS97_1_, accounttov0_.TID as
TID97_1_, accounttov0_.TAID as TAID97_1_, accounttov0_.TXNBATCHDATE
as TXNBATC55_97_1_, accounttov0_.ACCOUNTREGION as ACCOUNT56_97_1_,
accounttov0_.STRING1 as STRING57_97_1_, accounttov0_.STRING2 as
STRING58_97_1_, accounttov0_.STRING3 as STRING59_97_1_,
accounttov0_.STRING4 as STRING60_97_1_, accounttov0_.STRING5 as
STRING61_97_1_, accounttov0_.STRING6 as STRING62_97_1_,
accounttov0_.STRING7 as STRING63_97_1_, accounttov0_.STRING8 as
STRING64_97_1_, accounttov0_.STRING9 as STRING65_97_1_,
accounttov0_.STRING10 as STRING66_97_1_, accounttov0_.STRING11 as
STRING67_97_1_, accounttov0_.STRING12 as STRING68_97_1_,
accounttov0_.STRING13 as STRING69_97_1_, accounttov0_.STRING14 as
STRING70_97_1_, accounttov0_.STRING15 as STRING71_97_1_,
accounttov0_.STRING16 as STRING72_97_1_, accounttov0_.STRING17 as
STRING73_97_1_, accounttov0_.STRING18 as STRING74_97_1_,
accounttov0_.STRING19 as STRING75_97_1_, accounttov0_.STRING20 as
STRING76_97_1_, accounttov0_.STRING21 as STRING77_97_1_,
accounttov0_.STRING22 as STRING78_97_1_, accounttov0_.STRING23 as
STRING79_97_1_, accounttov0_.STRING24 as STRING80_97_1_,
accounttov0_.STRING25 as STRING81_97_1_, accounttov0_.STRING26 as
STRING82_97_1_, accounttov0_.STRING27 as STRING83_97_1_,
accounttov0_.STRING28 as STRING84_97_1_, accounttov0_.STRING29 as
STRING85_97_1_, accounttov0_.STRING30 as STRING86_97_1_,
accounttov0_.STRING31 as STRING87_97_1_, accounttov0_.STRING32 as
STRING88_97_1_, accounttov0_.STRING33 as STRING89_97_1_,
accounttov0_.STRING34 as STRING90_97_1_, accounttov0_.STRING35 as
STRING91_97_1_, accounttov0_.STRING36 as STRING92_97_1_,
accounttov0_.STRING37 as STRING93_97_1_, accounttov0_.STRING38 as
STRING94_97_1_, accounttov0_.STRING39 as STRING95_97_1_,
accounttov0_.STRING40 as STRING96_97_1_, accounttov0_.STRING41 as
STRING97_97_1_, accounttov0_.STRING42 as STRING98_97_1_,
accounttov0_.STRING43 as STRING99_97_1_, accounttov0_.STRING44 as
STRING100_97_1_, accounttov0_.STRING45 as STRING101_97_1_,
accounttov0_.STRING46 as STRING102_97_1_, accounttov0_.STRING47 as
STRING103_97_1_, accounttov0_.STRING48 as STRING104_97_1_,
accounttov0_.STRING49 as STRING105_97_1_, accounttov0_.STRING50 as
STRING106_97_1_, accounttov0_.STRING51 as STRING107_97_1_,
accounttov0_.STRING52 as STRING108_97_1_, accounttov0_.STRING53 as
STRING109_97_1_, accounttov0_.STRING54 as STRING110_97_1_,
accounttov0_.STRING55 as STRING111_97_1_, accounttov0_.STRING56 as
STRING112_97_1_, accounttov0_.STRING57 as STRING113_97_1_,
accounttov0_.STRING58 as STRING114_97_1_, accounttov0_.STRING59 as
STRING115_97_1_, accounttov0_.STRING60 as STRING116_97_1_,
accounttov0_.STRING61 as STRING117_97_1_, accounttov0_.STRING62 as
STRING118_97_1_, accounttov0_.STRING63 as STRING119_97_1_,
accounttov0_.STRING64 as STRING120_97_1_, accounttov0_.STRING65 as
STRING121_97_1_, accounttov0_.STRING66 as STRING122_97_1_,
accounttov0_.STRING67 as STRING123_97_1_, accounttov0_.STRING68 as
STRING124_97_1_, accounttov0_.STRING69 as STRING125_97_1_,
accounttov0_.STRING70 as STRING126_97_1_, accounttov0_.STRING71 as
STRING127_97_1_, accounttov0_.STRING72 as STRING128_97_1_,
accounttov0_.STRING73 as STRING129_97_1_, accounttov0_.STRING74 as
STRING130_97_1_, accounttov0_.STRING75 as STRING131_97_1_,
accounttov0_.TEXT1 as TEXT132_97_1_, accounttov0_.TEXT2 as
TEXT133_97_1_, accounttov0_.TEXT3 as TEXT134_97_1_,
accounttov0_.TEXT4 as TEXT135_97_1_, accounttov0_.TEXT5 as
TEXT136_97_1_, accounttov0_.TEXT6 as TEXT137_97_1_,
accounttov0_.TEXT7 as TEXT138_97_1_, accounttov0_.TEXT8 as
TEXT139_97_1_, accounttov0_.TEXT9 as TEXT140_97_1_,
accounttov0_.TEXT10 as TEXT141_97_1_, accounttov0_.NUM1 as
NUM142_97_1_, accounttov0_.NUM2 as NUM143_97_1_, accounttov0_.NUM3 as
NUM144_97_1_, accounttov0_.NUM4 as NUM145_97_1_, accounttov0_.NUM5 as
NUM146_97_1_, accounttov0_.NUM6 as NUM147_97_1_, accounttov0_.NUM7 as
NUM148_97_1_, accounttov0_.NUM8 as NUM149_97_1_, accounttov0_.NUM9 as
NUM150_97_1_, accounttov0_.NUM10 as NUM151_97_1_, accounttov0_.NUM11
as NUM152_97_1_, accounttov0_.NUM12 as NUM153_97_1_,
accounttov0_.NUM13 as NUM154_97_1_, accounttov0_.NUM14 as
NUM155_97_1_, accounttov0_.NUM15 as NUM156_97_1_, accounttov0_.NUM16
as NUM157_97_1_, accounttov0_.NUM17 as NUM158_97_1_,
accounttov0_.NUM18 as NUM159_97_1_, accounttov0_.NUM19 as
NUM160_97_1_, accounttov0_.NUM20 as NUM161_97_1_, accounttov0_.NUM21
as NUM162_97_1_, accounttov0_.NUM22 as NUM163_97_1_,
accounttov0_.NUM23 as NUM164_97_1_, accounttov0_.NUM24 as
NUM165_97_1_, accounttov0_.NUM25 as NUM166_97_1_, accounttov0_.NUM26
as NUM167_97_1_, accounttov0_.NUM27 as NUM168_97_1_,
accounttov0_.NUM28 as NUM169_97_1_, accounttov0_.NUM29 as
NUM170_97_1_, accounttov0_.NUM30 as NUM171_97_1_, accounttov0_.NUM31
as NUM172_97_1_, accounttov0_.NUM32 as NUM173_97_1_,
accounttov0_.NUM33 as NUM174_97_1_, accounttov0_.NUM34 as
NUM175_97_1_, accounttov0_.NUM35 as NUM176_97_1_, accounttov0_.NUM36
as NUM177_97_1_, accounttov0_.NUM37 as NUM178_97_1_,
accounttov0_.NUM38 as NUM179_97_1_, accounttov0_.NUM39 as
NUM180_97_1_, accounttov0_.NUM40 as NUM181_97_1_, accounttov0_.NUM41
as NUM182_97_1_, accounttov0_.NUM42 as NUM183_97_1_,
accounttov0_.NUM43 as NUM184_97_1_, accounttov0_.NUM44 as
NUM185_97_1_, accounttov0_.NUM45 as NUM186_97_1_, accounttov0_.NUM46
as NUM187_97_1_, accounttov0_.NUM47 as NUM188_97_1_,
accounttov0_.DATE1 as DATE189_97_1_, accounttov0_.DATE2 as
DATE190_97_1_, accounttov0_.DATE3 as DATE191_97_1_,
accounttov0_.DATE4 as DATE192_97_1_, accounttov0_.DATE5 as
DATE193_97_1_, accounttov0_.DATE6 as DATE194_97_1_,
accounttov0_.DATE7 as DATE195_97_1_, accounttov0_.DATE8 as
DATE196_97_1_, accounttov0_.DATE9 as DATE197_97_1_,
accounttov0_.DATE10 as DATE198_97_1_, accounttov0_.DATE11 as
DATE199_97_1_, accounttov0_.DATE12 as DATE200_97_1_,
accounttov0_.DATE13 as DATE201_97_1_, accounttov0_.DATE14 as
DATE202_97_1_, accounttov0_.DATE15 as DATE203_97_1_,
accounttov0_.DATE16 as DATE204_97_1_, accounttov0_.DATE17 as
DATE205_97_1_, accounttov0_.DATE18 as DATE206_97_1_,
accounttov0_.DATE19 as DATE207_97_1_, accounttov0_.DATE20 as
DATE208_97_1_, accounttov0_.DATE21 as DATE209_97_1_,
accounttov0_.DATE22 as DATE210_97_1_, accounttov0_.DATE23 as
DATE211_97_1_, accounttov0_.DATE24 as DATE212_97_1_,
accounttov0_.DATE25 as DATE213_97_1_, accounttov0_.DATE26 as
DATE214_97_1_, accounttov0_.DATE27 as DATE215_97_1_,
accounttov0_.DATE28 as DATE216_97_1_, accounttov0_.DATE29 as
DATE217_97_1_, accounttov0_.DATE30 as DATE218_97_1_,
accounttov0_.DATE31 as DATE219_97_1_, cstpehdr1_.CS_TYPE as
CS1_23_0_, cstpehdr1_.CS_NAME as CS2_23_0_, cstpehdr1_.CS_DESC as
CS3_23_0_, cstpehdr1_.CS_CRTDBY as CS4_23_0_, cstpehdr1_.CS_MODBY as
CS5_23_0_, cstpehdr1_.CS_CRTDDT as CS6_23_0_, cstpehdr1_.CS_MODDT as
CS7_23_0_ from ACCOUNT accounttov0_, CSTPE_HDR cstpehdr1_ where
accounttov0_.ACCOUNTTYPE=cstpehdr1_.CS_TYPE(+) and
accounttov0_.REFID=:1
RATIONALE: SQL statement with SQL_ID "8ypzr7b9bpsd8" was executed 28102
times and had an average elapsed time of 0.026 seconds.
RECOMMENDATION 3: SQL Tuning, 6.8% benefit (600 seconds)
ACTION: Investigate the SQL statement with SQL_ID "9gpw9dypy93vg" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 9gpw9dypy93vg
call PERIODIC_AGING_CALCULATION ( )
RATIONALE: SQL statement with SQL_ID "9gpw9dypy93vg" was executed 2
times and had an average elapsed time of 300 seconds.
RECOMMENDATION 4: SQL Tuning, 4% benefit (352 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"gzmkn0ysnnkzk".
RELEVANT OBJECT: SQL statement with SQL_ID gzmkn0ysnnkzk and
PLAN_HASH 2239883860
SELECT inv.cs_id ,SUM(CASE WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate)
BETWEEN -999 AND 0) THEN NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 1 AND 30) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 31 AND 60) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 61 AND 90) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 91 AND 99999) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),0,0,0,0,0,0,0 FROM inv_txn inv,
accr_txn accr WHERE inv.IV_ID = accr.IV_ID AND
inv.iv_date=accr.iv_date AND inv.iv_id in (SELECT inv2.iv_id FROM
inv_txn inv2 where inv2.cs_id in (SELECT DISTINCT CS_ID FROM inv_txn
where iv_moddt>=to_date('10/06/11:05:39', 'dd/mm/YY:HH24:MI') AND
en_id=4180 AND iv_type='Invoice') AND inv2.iv_closed=0 AND
inv2.iv_type='Invoice') GROUP BY inv.cs_id
RATIONALE: SQL statement with SQL_ID "gzmkn0ysnnkzk" was executed 1
times and had an average elapsed time of 351 seconds.
RECOMMENDATION 5: SQL Tuning, 3.4% benefit (305 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"13nnn19dcfq7g".
RELEVANT OBJECT: SQL statement with SQL_ID 13nnn19dcfq7g and
PLAN_HASH 544202911
select count(*) as col_0_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_.IV_CLOSED=0 and (upper(cmtslog0_.CM_TEXT) like '%N%')
RATIONALE: SQL statement with SQL_ID "13nnn19dcfq7g" was executed 1
times and had an average elapsed time of 303 seconds.
FINDING 3: 38% impact (3410 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, 38% benefit (3410 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 26 SQL statements with PLAN_HASH_VALUE 1928032458
were found to be using literals. An example is SQL statement with
SQL_ID "c0qpz5paz0245" .
RELEVANT OBJECT: SQL statement with SQL_ID c0qpz5paz0245 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='8517524' order by cmtslog0_.CM_CRTDT DESC
RATIONALE: At least 26 SQL statements with PLAN_HASH_VALUE 1928032458
were found to be using literals. An example is SQL statement with
SQL_ID "25bjnc2h1a4mx" .
RELEVANT OBJECT: SQL statement with SQL_ID 25bjnc2h1a4mx 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='7142594' order by cmtslog0_.CM_CRTDT DESC
RATIONALE: At least 11 SQL statements with PLAN_HASH_VALUE 2239883860
were found to be using literals. An example is SQL statement with
SQL_ID "4yz6ad0q64074" .
RELEVANT OBJECT: SQL statement with SQL_ID 4yz6ad0q64074 and
PLAN_HASH 2239883860
SELECT inv.cs_id ,SUM(CASE WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate)
BETWEEN -999 AND 0) THEN NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 1 AND 30) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 31 AND 60) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 61 AND 90) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 91 AND 120) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 121 AND 150) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 151 AND 180) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 181 AND 99999) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),0,0,0,0 FROM inv_txn inv, accr_txn
accr WHERE inv.IV_ID = accr.IV_ID AND inv.iv_date=accr.iv_date AND
inv.iv_id in (SELECT inv2.iv_id FROM inv_txn inv2 where inv2.cs_id in
(SELECT DISTINCT CS_ID FROM inv_txn where
iv_moddt>=to_date('10/06/11:05:39', 'dd/mm/YY:HH24:MI') AND
en_id=2010 AND iv_type='Invoice') AND inv2.iv_closed=0 AND
inv2.iv_type='Invoice') GROUP BY inv.cs_id
RATIONALE: At least 11 SQL statements with PLAN_HASH_VALUE 2239883860
were found to be using literals. An example is SQL statement with
SQL_ID "bw6fr2pw7d24w" .
RELEVANT OBJECT: SQL statement with SQL_ID bw6fr2pw7d24w and
PLAN_HASH 2239883860
SELECT inv.cs_id ,SUM(CASE WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate)
BETWEEN -999 AND 0) THEN NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 1 AND 30) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 31 AND 60) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 61 AND 90) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 91 AND 120) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 121 AND 150) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 151 AND 180) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 181 AND 99999) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),0,0,0,0 FROM inv_txn inv, accr_txn
accr WHERE inv.IV_ID = accr.IV_ID AND inv.iv_date=accr.iv_date AND
inv.iv_id in (SELECT inv2.iv_id FROM inv_txn inv2 where inv2.cs_id in
(SELECT DISTINCT CS_ID FROM inv_txn where
iv_moddt>=to_date('10/06/11:05:39', 'dd/mm/YY:HH24:MI') AND
en_id=3910 AND iv_type='Invoice') AND inv2.iv_closed=0 AND
inv2.iv_type='Invoice') GROUP BY inv.cs_id
RATIONALE: At least 10 SQL statements with PLAN_HASH_VALUE 1989452701
were found to be using literals. An example is SQL statement with
SQL_ID "4z4d0z2ss80pz" .
RELEVANT OBJECT: SQL statement with SQL_ID 4z4d0z2ss80pz and
PLAN_HASH 1989452701
select count(*) as col_0_0_ from ADD_HDR addhdr0_, CST_HDR csthdr1_
where addhdr0_.AD_LINK=csthdr1_.CS_ID(+) and (addhdr0_.EN_ID in
(4060)) and (upper(csthdr1_.CS_REF1) like '%220354%')
RATIONALE: At least 10 SQL statements with PLAN_HASH_VALUE 1989452701
were found to be using literals. An example is SQL statement with
SQL_ID "b72fgmbh4h1h0" .
RELEVANT OBJECT: SQL statement with SQL_ID b72fgmbh4h1h0 and
PLAN_HASH 1989452701
select count(*) as col_0_0_ from ADD_HDR addhdr0_, CST_HDR csthdr1_
where addhdr0_.AD_LINK=csthdr1_.CS_ID(+) and (addhdr0_.EN_ID in
(4060)) and (upper(csthdr1_.CS_REF1) like '%220791%')
RATIONALE: At least 10 SQL statements with PLAN_HASH_VALUE 2919750023
were found to be using literals. An example is SQL statement with
SQL_ID "cgarz64p9x6ax" .
RELEVANT OBJECT: SQL statement with SQL_ID cgarz64p9x6ax 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='1405804' and invtxn1_.IV_TYPE='Invoice' order by
cmtslog0_.CM_CRTDT DESC ) where rownum <= :1
RATIONALE: At least 10 SQL statements with PLAN_HASH_VALUE 2919750023
were found to be using literals. An example is SQL statement with
SQL_ID "80jhb5zh1qapk" .
RELEVANT OBJECT: SQL statement with SQL_ID 80jhb5zh1qapk 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='231946' and invtxn1_.IV_TYPE='Invoice' order by
cmtslog0_.CM_CRTDT DESC ) where rownum <= :1
RATIONALE: At least 8 SQL statements with PLAN_HASH_VALUE 87733662 were
found to be using literals. An example is SQL statement with SQL_ID
"f0g37cg85u4t8" .
RELEVANT OBJECT: SQL statement with SQL_ID f0g37cg85u4t8 and
PLAN_HASH 87733662
select count(*) as col_0_0_ from INV_TXN invtxn0_, CST_HDR csthdr1_
where invtxn0_.CS_ID=csthdr1_.CS_ID and (invtxn0_.EN_ID in (4160))
and invtxn0_.IV_TYPE='Account' and invtxn0_.IV_CLOSED=0 and
csthdr1_.CS_NUM9<>0 and (invtxn0_.IV_STR6 is null or invtxn0_.IV_STR6
not in ('Recovery' , 'Treasury'))
RATIONALE: At least 8 SQL statements with PLAN_HASH_VALUE 87733662 were
found to be using literals. An example is SQL statement with SQL_ID
"gbppjnghvk73f" .
RELEVANT OBJECT: SQL statement with SQL_ID gbppjnghvk73f and
PLAN_HASH 87733662
select count(*) as col_0_0_ from INV_TXN invtxn0_, CST_HDR csthdr1_
where invtxn0_.CS_ID=csthdr1_.CS_ID and (invtxn0_.EN_ID in (4060))
and invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 and
(upper(csthdr1_.CS_REF1) like '%155408%')
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (38% impact [3412 seconds])
FINDING 4: 19% impact (1697 seconds)
------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 11% benefit (941 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: 3 full object
scans, 112167 physical reads, 7258 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "8ypzr7b9bpsd8" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8ypzr7b9bpsd8
select accounttov0_.REFID as REFID97_1_, accounttov0_.ACCOUNTTXNREFID
as ACCOUNTT2_97_1_, accounttov0_.ACCOUNTTXNREFDATE as
ACCOUNTT3_97_1_, accounttov0_.ACCOUNTID as ACCOUNTID97_1_,
accounttov0_.TXNREFID as TXNREFID97_1_, accounttov0_.PARENTACCOUNT as
PARENTAC6_97_1_, accounttov0_.ENTITY as ENTITY97_1_,
accounttov0_.SITEID as SITEID97_1_, accounttov0_.ASSIGNEDTO as
ASSIGNEDTO97_1_, accounttov0_.TXNASSTPE as TXNASSTPE97_1_,
accounttov0_.ACCOUNTNAME as ACCOUNT11_97_1_, accounttov0_.LANGUAGE as
LANGUAGE97_1_, accounttov0_.ACCOUNTCURRENCY as ACCOUNT13_97_1_,
accounttov0_.ACCOUNTGROUPID as ACCOUNT14_97_1_,
accounttov0_.ACCOUNTRESP as ACCOUNT15_97_1_,
accounttov0_.ACCOUNTSALEPERSON as ACCOUNT16_97_1_,
accounttov0_.ACCOUNTWEBSITE as ACCOUNT17_97_1_,
accounttov0_.ACCOUNTVAT as ACCOUNTVAT97_1_, accounttov0_.TERMS as
TERMS97_1_, accounttov0_.SOURCE as SOURCE97_1_, accounttov0_.ACTIVE
as ACTIVE97_1_, accounttov0_.CLOSED as CLOSED97_1_,
accounttov0_.ACCOUNTCRTDBY as ACCOUNT23_97_1_,
accounttov0_.ACCOUNTCRTDATE as ACCOUNT24_97_1_,
accounttov0_.ACCOUNTMODBY as ACCOUNT25_97_1_,
accounttov0_.ACCOUNTMODDATE as ACCOUNT26_97_1_,
accounttov0_.TXNCRTDBY as TXNCRTDBY97_1_, accounttov0_.TXNCRTDATE as
TXNCRTDATE97_1_, accounttov0_.TXNMODBY as TXNMODBY97_1_,
accounttov0_.TXNMODDATE as TXNMODDATE97_1_,
accounttov0_.LASTACTIONDATE as LASTACT31_97_1_,
accounttov0_.LASTACTION as LASTACTION97_1_, accounttov0_.LASTACTIONBY
as LASTACT33_97_1_, accounttov0_.ACCOUNTTYPE as ACCOUNT34_97_1_,
accounttov0_.TXNTYPE as TXNTYPE97_1_, accounttov0_.STATUSID as
STATUSID97_1_, accounttov0_.TXNPARENTID as TXNPARE37_97_1_,
accounttov0_.TXNCURRENCY as TXNCURR38_97_1_, accounttov0_.TXNSHIPMODE
as TXNSHIP39_97_1_, accounttov0_.TXNTERMS as TXNTERMS97_1_,
accounttov0_.TXNFOR as TXNFOR97_1_, accounttov0_.TXNREMITTO as
TXNREMITTO97_1_, accounttov0_.TXNPRODCDE as TXNPRODCDE97_1_,
accounttov0_.TXNLOC as TXNLOC97_1_, accounttov0_.TXNRECTYPE as
TXNRECTYPE97_1_, accounttov0_.TXNBU as TXNBU97_1_,
accounttov0_.TXNGROUP as TXNGROUP97_1_, accounttov0_.TXNBATCH as
TXNBATCH97_1_, accounttov0_.AID as AID97_1_, accounttov0_.TXNSHIPADD
as TXNSHIPADD97_1_, accounttov0_.TXNBILLADD as TXNBILLADD97_1_,
accounttov0_.TXNCRTTRMS as TXNCRTTRMS97_1_, accounttov0_.TID as
TID97_1_, accounttov0_.TAID as TAID97_1_, accounttov0_.TXNBATCHDATE
as TXNBATC55_97_1_, accounttov0_.ACCOUNTREGION as ACCOUNT56_97_1_,
accounttov0_.STRING1 as STRING57_97_1_, accounttov0_.STRING2 as
STRING58_97_1_, accounttov0_.STRING3 as STRING59_97_1_,
accounttov0_.STRING4 as STRING60_97_1_, accounttov0_.STRING5 as
STRING61_97_1_, accounttov0_.STRING6 as STRING62_97_1_,
accounttov0_.STRING7 as STRING63_97_1_, accounttov0_.STRING8 as
STRING64_97_1_, accounttov0_.STRING9 as STRING65_97_1_,
accounttov0_.STRING10 as STRING66_97_1_, accounttov0_.STRING11 as
STRING67_97_1_, accounttov0_.STRING12 as STRING68_97_1_,
accounttov0_.STRING13 as STRING69_97_1_, accounttov0_.STRING14 as
STRING70_97_1_, accounttov0_.STRING15 as STRING71_97_1_,
accounttov0_.STRING16 as STRING72_97_1_, accounttov0_.STRING17 as
STRING73_97_1_, accounttov0_.STRING18 as STRING74_97_1_,
accounttov0_.STRING19 as STRING75_97_1_, accounttov0_.STRING20 as
STRING76_97_1_, accounttov0_.STRING21 as STRING77_97_1_,
accounttov0_.STRING22 as STRING78_97_1_, accounttov0_.STRING23 as
STRING79_97_1_, accounttov0_.STRING24 as STRING80_97_1_,
accounttov0_.STRING25 as STRING81_97_1_, accounttov0_.STRING26 as
STRING82_97_1_, accounttov0_.STRING27 as STRING83_97_1_,
accounttov0_.STRING28 as STRING84_97_1_, accounttov0_.STRING29 as
STRING85_97_1_, accounttov0_.STRING30 as STRING86_97_1_,
accounttov0_.STRING31 as STRING87_97_1_, accounttov0_.STRING32 as
STRING88_97_1_, accounttov0_.STRING33 as STRING89_97_1_,
accounttov0_.STRING34 as STRING90_97_1_, accounttov0_.STRING35 as
STRING91_97_1_, accounttov0_.STRING36 as STRING92_97_1_,
accounttov0_.STRING37 as STRING93_97_1_, accounttov0_.STRING38 as
STRING94_97_1_, accounttov0_.STRING39 as STRING95_97_1_,
accounttov0_.STRING40 as STRING96_97_1_, accounttov0_.STRING41 as
STRING97_97_1_, accounttov0_.STRING42 as STRING98_97_1_,
accounttov0_.STRING43 as STRING99_97_1_, accounttov0_.STRING44 as
STRING100_97_1_, accounttov0_.STRING45 as STRING101_97_1_,
accounttov0_.STRING46 as STRING102_97_1_, accounttov0_.STRING47 as
STRING103_97_1_, accounttov0_.STRING48 as STRING104_97_1_,
accounttov0_.STRING49 as STRING105_97_1_, accounttov0_.STRING50 as
STRING106_97_1_, accounttov0_.STRING51 as STRING107_97_1_,
accounttov0_.STRING52 as STRING108_97_1_, accounttov0_.STRING53 as
STRING109_97_1_, accounttov0_.STRING54 as STRING110_97_1_,
accounttov0_.STRING55 as STRING111_97_1_, accounttov0_.STRING56 as
STRING112_97_1_, accounttov0_.STRING57 as STRING113_97_1_,
accounttov0_.STRING58 as STRING114_97_1_, accounttov0_.STRING59 as
STRING115_97_1_, accounttov0_.STRING60 as STRING116_97_1_,
accounttov0_.STRING61 as STRING117_97_1_, accounttov0_.STRING62 as
STRING118_97_1_, accounttov0_.STRING63 as STRING119_97_1_,
accounttov0_.STRING64 as STRING120_97_1_, accounttov0_.STRING65 as
STRING121_97_1_, accounttov0_.STRING66 as STRING122_97_1_,
accounttov0_.STRING67 as STRING123_97_1_, accounttov0_.STRING68 as
STRING124_97_1_, accounttov0_.STRING69 as STRING125_97_1_,
accounttov0_.STRING70 as STRING126_97_1_, accounttov0_.STRING71 as
STRING127_97_1_, accounttov0_.STRING72 as STRING128_97_1_,
accounttov0_.STRING73 as STRING129_97_1_, accounttov0_.STRING74 as
STRING130_97_1_, accounttov0_.STRING75 as STRING131_97_1_,
accounttov0_.TEXT1 as TEXT132_97_1_, accounttov0_.TEXT2 as
TEXT133_97_1_, accounttov0_.TEXT3 as TEXT134_97_1_,
accounttov0_.TEXT4 as TEXT135_97_1_, accounttov0_.TEXT5 as
TEXT136_97_1_, accounttov0_.TEXT6 as TEXT137_97_1_,
accounttov0_.TEXT7 as TEXT138_97_1_, accounttov0_.TEXT8 as
TEXT139_97_1_, accounttov0_.TEXT9 as TEXT140_97_1_,
accounttov0_.TEXT10 as TEXT141_97_1_, accounttov0_.NUM1 as
NUM142_97_1_, accounttov0_.NUM2 as NUM143_97_1_, accounttov0_.NUM3 as
NUM144_97_1_, accounttov0_.NUM4 as NUM145_97_1_, accounttov0_.NUM5 as
NUM146_97_1_, accounttov0_.NUM6 as NUM147_97_1_, accounttov0_.NUM7 as
NUM148_97_1_, accounttov0_.NUM8 as NUM149_97_1_, accounttov0_.NUM9 as
NUM150_97_1_, accounttov0_.NUM10 as NUM151_97_1_, accounttov0_.NUM11
as NUM152_97_1_, accounttov0_.NUM12 as NUM153_97_1_,
accounttov0_.NUM13 as NUM154_97_1_, accounttov0_.NUM14 as
NUM155_97_1_, accounttov0_.NUM15 as NUM156_97_1_, accounttov0_.NUM16
as NUM157_97_1_, accounttov0_.NUM17 as NUM158_97_1_,
accounttov0_.NUM18 as NUM159_97_1_, accounttov0_.NUM19 as
NUM160_97_1_, accounttov0_.NUM20 as NUM161_97_1_, accounttov0_.NUM21
as NUM162_97_1_, accounttov0_.NUM22 as NUM163_97_1_,
accounttov0_.NUM23 as NUM164_97_1_, accounttov0_.NUM24 as
NUM165_97_1_, accounttov0_.NUM25 as NUM166_97_1_, accounttov0_.NUM26
as NUM167_97_1_, accounttov0_.NUM27 as NUM168_97_1_,
accounttov0_.NUM28 as NUM169_97_1_, accounttov0_.NUM29 as
NUM170_97_1_, accounttov0_.NUM30 as NUM171_97_1_, accounttov0_.NUM31
as NUM172_97_1_, accounttov0_.NUM32 as NUM173_97_1_,
accounttov0_.NUM33 as NUM174_97_1_, accounttov0_.NUM34 as
NUM175_97_1_, accounttov0_.NUM35 as NUM176_97_1_, accounttov0_.NUM36
as NUM177_97_1_, accounttov0_.NUM37 as NUM178_97_1_,
accounttov0_.NUM38 as NUM179_97_1_, accounttov0_.NUM39 as
NUM180_97_1_, accounttov0_.NUM40 as NUM181_97_1_, accounttov0_.NUM41
as NUM182_97_1_, accounttov0_.NUM42 as NUM183_97_1_,
accounttov0_.NUM43 as NUM184_97_1_, accounttov0_.NUM44 as
NUM185_97_1_, accounttov0_.NUM45 as NUM186_97_1_, accounttov0_.NUM46
as NUM187_97_1_, accounttov0_.NUM47 as NUM188_97_1_,
accounttov0_.DATE1 as DATE189_97_1_, accounttov0_.DATE2 as
DATE190_97_1_, accounttov0_.DATE3 as DATE191_97_1_,
accounttov0_.DATE4 as DATE192_97_1_, accounttov0_.DATE5 as
DATE193_97_1_, accounttov0_.DATE6 as DATE194_97_1_,
accounttov0_.DATE7 as DATE195_97_1_, accounttov0_.DATE8 as
DATE196_97_1_, accounttov0_.DATE9 as DATE197_97_1_,
accounttov0_.DATE10 as DATE198_97_1_, accounttov0_.DATE11 as
DATE199_97_1_, accounttov0_.DATE12 as DATE200_97_1_,
accounttov0_.DATE13 as DATE201_97_1_, accounttov0_.DATE14 as
DATE202_97_1_, accounttov0_.DATE15 as DATE203_97_1_,
accounttov0_.DATE16 as DATE204_97_1_, accounttov0_.DATE17 as
DATE205_97_1_, accounttov0_.DATE18 as DATE206_97_1_,
accounttov0_.DATE19 as DATE207_97_1_, accounttov0_.DATE20 as
DATE208_97_1_, accounttov0_.DATE21 as DATE209_97_1_,
accounttov0_.DATE22 as DATE210_97_1_, accounttov0_.DATE23 as
DATE211_97_1_, accounttov0_.DATE24 as DATE212_97_1_,
accounttov0_.DATE25 as DATE213_97_1_, accounttov0_.DATE26 as
DATE214_97_1_, accounttov0_.DATE27 as DATE215_97_1_,
accounttov0_.DATE28 as DATE216_97_1_, accounttov0_.DATE29 as
DATE217_97_1_, accounttov0_.DATE30 as DATE218_97_1_,
accounttov0_.DATE31 as DATE219_97_1_, cstpehdr1_.CS_TYPE as
CS1_23_0_, cstpehdr1_.CS_NAME as CS2_23_0_, cstpehdr1_.CS_DESC as
CS3_23_0_, cstpehdr1_.CS_CRTDBY as CS4_23_0_, cstpehdr1_.CS_MODBY as
CS5_23_0_, cstpehdr1_.CS_CRTDDT as CS6_23_0_, cstpehdr1_.CS_MODDT as
CS7_23_0_ from ACCOUNT accounttov0_, CSTPE_HDR cstpehdr1_ where
accounttov0_.ACCOUNTTYPE=cstpehdr1_.CS_TYPE(+) and
accounttov0_.REFID=:1
RATIONALE: The SQL statement with SQL_ID "fxm2yf958b9vf" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID fxm2yf958b9vf
/* named native SQL query search.transaction.assignment */ SELECT /*+
INDEX(INV_TXN INV_TXN_IVCLOSED_ENID_IDX)*/ IV_ID, IV_DATE FROM
INV_TXN WHERE (IV_CLOSED = 0 AND EN_ID = :1 AND IV_TYPE = :2 AND
IV_ASSGNTO IS NULL)
RATIONALE: The SQL statement with SQL_ID "6gatdvtpkct0x" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 6gatdvtpkct0x
select invtxn0_.IV_ID as col_0_0_, invtxn0_.IV_DATE as col_0_1_,
invtxn0_.IV_SLSORDNO as col_1_0_, invtxn0_.IV_RECTYPE as col_2_0_,
csthdr1_.CS_REF1 as col_3_0_, invtxn0_.IV_GPINVNO as col_4_0_ from
INV_TXN invtxn0_, CST_HDR csthdr1_ where
invtxn0_.CS_ID=csthdr1_.CS_ID and invtxn0_.IV_CLOSED=0 and
invtxn0_.IV_TYPE=:1 and invtxn0_.EN_ID=: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 , :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 , :101 , :102 ,
:103 , :104 , :105 , :106 , :107 , :108 , :109 , :110 , :111 , :112 ,
:113 , :114 , :115 , :116 , :117 , :118 , :119 , :120 , :121 , :122 ,
:123 , :124 , :125 , :126 , :127 , :128 , :129 , :130 , :131 , :132 ,
:133 , :134 , :135 , :136 , :137 , :138 , :139 , :140 , :141 , :142 ,
:143 , :144 , :145 , :146 , :147 , :148 , :149 , :150 , :151 , :152 ,
:153 , :154 , :155 , :156 , :157 , :158 , :159 , :160 , :161 , :162 ,
:163 , :164 , :165 , :166 , :167 , :168 , :169 , :170 , :171 , :172 ,
:173 , :174 , :175 , :176 , :177 , :178 , :179 , :180 , :181 , :182 ,
:183 , :184 , :185 , :186 , :187 , :188 , :189 , :190 , :191 , :192 ,
:193 , :194 , :195 , :196 , :197 , :198 , :199 , :200 , :201 , :202 ,
:203 , :204 , :205 , :206 , :207 , :208 , :209 , :210 , :211 , :212 ,
:213 , :214 , :215 , :216 , :217 , :218 , :219 , :220 , :221 , :222 ,
:223 , :224 , :225 , :226 , :227 , :228 , :229 , :230 , :231 , :232 ,
:233 , :234 , :235 , :236 , :237 , :238 , :239 , :240 , :241 , :242 ,
:243 , :244 , :245 , :246 , :247 , :248 , :249 , :250 , :251 , :252 ,
:253 , :254 , :255 , :256 , :257 , :258 , :259 , :260 , :261 , :262 ,
:263 , :264 , :265 , :266 , :267 , :268 , :269 , :270 , :271 , :272 ,
:273 , :274 , :275 , :276 , :277 , :278 , :279 , :280 , :281 , :282 ,
:283 , :284 , :285 , :286 , :287 , :288 , :289 , :290 , :291 , :292 ,
:293 , :294 , :295 , :296 , :297 , :298 , :299 , :300 , :301 , :302 ,
:303 , :304 , :305 , :306 , :307 , :308 , :309 , :310 , :311 , :312 ,
:313 , :314 , :315 , :316 , :317 , :318 , :319 , :320 , :321 , :322 ,
:323 , :324 , :325 , :326 , :327 , :328 , :329 , :330 , :331 , :332 ,
:333 , :334 , :335 , :336 , :337 , :338 , :339 , :340 , :341 , :342 ,
:343 , :344 , :345 , :346 , :347 , :348 , :349 , :350 , :351 , :352 ,
:353 , :354 , :355 , :356 , :357 , :358 , :359 , :360 , :361 , :362 ,
:363 , :364 , :365 , :366 , :367 , :368 , :369 , :370 , :371 , :372 ,
:373 , :374 , :375 , :376 , :377 , :378 , :379 , :380 , :381 , :382 ,
:383 , :384 , :385 , :386 , :387 , :388 , :389 , :390 , :391 , :392 ,
:393 , :394 , :395 , :396 , :397 , :398 , :399 , :400 , :401 , :402 ,
:403 , :404 , :405 , :406 , :407 , :408 , :409 , :410 , :411 , :412 ,
:413 , :414 , :415 , :416 , :417 , :418 , :419 , :420 , :421 , :422 ,
:423 , :424 , :425 , :426 , :427 , :428 , :429 , :430 , :431 , :432 ,
:433 , :434 , :435 , :436 , :437 , :438 , :439 , :440 , :441 , :442 ,
:443 , :444 , :445 , :446 , :447 , :448 , :449 , :450 , :451 , :452 ,
:453 , :454 , :455 , :456 , :457 , :458 , :459 , :460 , :461 , :462 ,
:463 , :464 , :465 , :466 , :467 , :468 , :469 , :470 , :471 , :472 ,
:473 , :474 , :475 , :476 , :477 , :478 , :479 , :480 , :481 , :482 ,
:483 , :484 , :485 , :486 , :487 , :488 , :489 , :490 , :491 , :492 ,
:493 , :494 , :495 , :496 , :497 , :498 , :499 , :500 , :501 , :502 ,
:503))
RATIONALE: The SQL statement with SQL_ID "1jxzrx64ykzyq" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 1jxzrx64ykzyq
select count(*) as col_0_0_ from INV_TXN invtxn0_, CMTS_LOG
cmtslogs1_, ATTMTS_LOG attmtslogs2_ 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_.EN_ID in (4060)) and
invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=1
RECOMMENDATION 2: Segment Tuning, 4.8% benefit (429 seconds)
ACTION: Run "Segment Advisor" on TABLE "DNBI.CMTS_LOG" with object id
56446.
RELEVANT OBJECT: database object with id 56446
ACTION: Investigate application logic involving I/O on TABLE
"DNBI.CMTS_LOG" with object id 56446.
RELEVANT OBJECT: database object with id 56446
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 42140 physical reads, 1016 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "13nnn19dcfq7g" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 13nnn19dcfq7g
select count(*) as col_0_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_.IV_CLOSED=0 and (upper(cmtslog0_.CM_TEXT) like '%N%')
RECOMMENDATION 3: Segment Tuning, 3.7% benefit (327 seconds)
ACTION: Run "Segment Advisor" on UNDEFINED "** UNAVAILABLE **.**
UNAVAILABLE **" with object id 56429.
RELEVANT OBJECT: database object with id 56429
ACTION: Investigate application logic involving I/O on UNDEFINED "**
UNAVAILABLE **.** UNAVAILABLE **" with object id 56429.
RELEVANT OBJECT: database object with id 56429
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 35963 physical reads, 919 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "awzp6jh6gbshp" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID awzp6jh6gbshp
select transactio0_.REFID as REFID99_2_, transactio0_.TXNDATE as
TXNDATE99_2_, transactio0_.RECID as RECID99_2_, transactio0_.RECDATE
as RECDATE99_2_, transactio0_.TXNREFID as TXNREFID99_2_,
transactio0_.LINKEDTRANSACTIONID as LINKEDTR6_99_2_,
transactio0_.ACCOUNTREFID as ACCOUNTR7_99_2_, transactio0_.STATUSID
as STATUSID99_2_, transactio0_.ENTITY as ENTITY99_2_,
transactio0_.SITEID as SITEID99_2_, transactio0_.RECENTITY as
RECENTITY99_2_, transactio0_.RECSITEID as RECSITEID99_2_,
transactio0_.MATCHID as MATCHID99_2_, transactio0_.ASSIGNTO as
ASSIGNTO99_2_, transactio0_.ASSIGNTYPE as ASSIGNTYPE99_2_,
transactio0_.DUEDATE as DUEDATE99_2_, transactio0_.CURRENCY as
CURRENCY99_2_, transactio0_.TERMS as TERMS99_2_,
transactio0_.LOCATION as LOCATION99_2_, transactio0_.BUSINESSUNIT as
BUSINES20_99_2_, transactio0_.TRANSACTIONGROUP as TRANSAC21_99_2_,
transactio0_.ADDRESS1 as ADDRESS22_99_2_, transactio0_.ADDRESS2 as
ADDRESS23_99_2_, transactio0_.ADDRESS3 as ADDRESS24_99_2_,
transactio0_.ADDRESS4 as ADDRESS25_99_2_, transactio0_.SOURCE as
SOURCE99_2_, transactio0_.RECTYPE as RECTYPE99_2_, transactio0_.TYPE
as TYPE99_2_, transactio0_.CLOSED as CLOSED99_2_,
transactio0_.TXNCRTDATE as TXNCRTDATE99_2_, transactio0_.TXNCRTDBY as
TXNCRTDBY99_2_, transactio0_.TXNMODDATE as TXNMODDATE99_2_,
transactio0_.TXNMODBY as TXNMODBY99_2_, transactio0_.RECCRTDBY as
RECCRTDBY99_2_, transactio0_.RECCRTDATE as RECCRTDATE99_2_,
transactio0_.RECMODBY as RECMODBY99_2_, transactio0_.RECMODDATE as
RECMODDATE99_2_, transactio0_.LASTACTIONDATE as LASTACT38_99_2_,
transactio0_.LASTACTION as LASTACTION99_2_, transactio0_.LASTACTIONBY
as LASTACT40_99_2_, transactio0_.PRODCODE as PRODCODE99_2_,
transactio0_.PRICELIST as PRICELIST99_2_, transactio0_.STRING1 as
STRING43_99_2_, transactio0_.STRING2 as STRING44_99_2_,
transactio0_.STRING3 as STRING45_99_2_, transactio0_.STRING4 as
STRING46_99_2_, transactio0_.STRING5 as STRING47_99_2_,
transactio0_.STRING6 as STRING48_99_2_, transactio0_.STRING7 as
STRING49_99_2_, transactio0_.STRING8 as STRING50_99_2_,
transactio0_.STRING9 as STRING51_99_2_, transactio0_.STRING10 as
STRING52_99_2_, transactio0_.STRING11 as STRING53_99_2_,
transactio0_.STRING12 as STRING54_99_2_, transactio0_.STRING13 as
STRING55_99_2_, transactio0_.STRING14 as STRING56_99_2_,
transactio0_.STRING15 as STRING57_99_2_, transactio0_.STRING16 as
STRING58_99_2_, transactio0_.STRING17 as STRING59_99_2_,
transactio0_.STRING18 as STRING60_99_2_, transactio0_.STRING19 as
STRING61_99_2_, transactio0_.STRING20 as STRING62_99_2_,
transactio0_.STRING21 as STRING63_99_2_, transactio0_.STRING22 as
STRING64_99_2_, transactio0_.STRING23 as STRING65_99_2_,
transactio0_.STRING24 as STRING66_99_2_, transactio0_.STRING25 as
STRING67_99_2_, transactio0_.STRING26 as STRING68_99_2_,
transactio0_.STRING27 as STRING69_99_2_, transactio0_.STRING28 as
STRING70_99_2_, transactio0_.STRING29 as STRING71_99_2_,
transactio0_.STRING30 as STRING72_99_2_, transactio0_.STRING31 as
STRING73_99_2_, transactio0_.STRING32 as STRING74_99_2_,
transactio0_.STRING33 as STRING75_99_2_, transactio0_.STRING34 as
STRING76_99_2_, transactio0_.STRING35 as STRING77_99_2_,
transactio0_.STRING36 as STRING78_99_2_, transactio0_.STRING37 as
STRING79_99_2_, transactio0_.STRING38 as STRING80_99_2_,
transactio0_.STRING39 as STRING81_99_2_, transactio0_.STRING40 as
STRING82_99_2_, transactio0_.STRING41 as STRING83_99_2_,
transactio0_.STRING42 as STRING84_99_2_, transactio0_.STRING43 as
STRING85_99_2_, transactio0_.STRING44 as STRING86_99_2_,
transactio0_.STRING45 as STRING87_99_2_, transactio0_.STRING46 as
STRING88_99_2_, transactio0_.STRING47 as STRING89_99_2_,
transactio0_.STRING48 as STRING90_99_2_, transactio0_.STRING49 as
STRING91_99_2_, transactio0_.STRING50 as STRING92_99_2_,
transactio0_.STRING51 as STRING93_99_2_, transactio0_.STRING52 as
STRING94_99_2_, transactio0_.STRING53 as STRING95_99_2_,
transactio0_.STRING54 as STRING96_99_2_, transactio0_.STRING55 as
STRING97_99_2_, transactio0_.STRING56 as STRING98_99_2_,
transactio0_.STRING57 as STRING99_99_2_, transactio0_.STRING58 as
STRING100_99_2_, transactio0_.STRING59 as STRING101_99_2_,
transactio0_.STRING60 as STRING102_99_2_, transactio0_.STRING61 as
STRING103_99_2_, transactio0_.STRING62 as STRING104_99_2_,
transactio0_.STRING63 as STRING105_99_2_, transactio0_.STRING64 as
STRING106_99_2_, transactio0_.STRING65 as STRING107_99_2_,
transactio0_.STRING66 as STRING108_99_2_, transactio0_.STRING67 as
STRING109_99_2_, transactio0_.STRING68 as STRING110_99_2_,
transactio0_.STRING69 as STRING111_99_2_, transactio0_.STRING70 as
STRING112_99_2_, transactio0_.STRING71 as STRING113_99_2_,
transactio0_.TEXT1 as TEXT114_99_2_, transactio0_.TEXT2 as
TEXT115_99_2_, transactio0_.TEXT3 as TEXT116_99_2_,
transactio0_.TEXT4 as TEXT117_99_2_, transactio0_.TEXT5 as
TEXT118_99_2_, transactio0_.TEXT6 as TEXT119_99_2_,
transactio0_.TEXT7 as TEXT120_99_2_, transactio0_.NUM1 as
NUM121_99_2_, transactio0_.NUM2 as NUM122_99_2_, transactio0_.NUM3 as
NUM123_99_2_, transactio0_.NUM4 as NUM124_99_2_, transactio0_.NUM5 as
NUM125_99_2_, transactio0_.NUM6 as NUM126_99_2_, transactio0_.NUM7 as
NUM127_99_2_, transactio0_.NUM8 as NUM128_99_2_, transactio0_.NUM9 as
NUM129_99_2_, transactio0_.NUM10 as NUM130_99_2_, transactio0_.NUM11
as NUM131_99_2_, transactio0_.NUM12 as NUM132_99_2_,
transactio0_.NUM13 as NUM133_99_2_, transactio0_.NUM14 as
NUM134_99_2_, transactio0_.NUM15 as NUM135_99_2_, transactio0_.NUM16
as NUM136_99_2_, transactio0_.NUM17 as NUM137_99_2_,
transactio0_.NUM18 as NUM138_99_2_, transactio0_.NUM19 as
NUM139_99_2_, transactio0_.NUM20 as NUM140_99_2_, transactio0_.NUM21
as NUM141_99_2_, transactio0_.NUM22 as NUM142_99_2_,
transactio0_.NUM23 as NUM143_99_2_, transactio0_.NUM24 as
NUM144_99_2_, transactio0_.NUM25 as NUM145_99_2_, transactio0_.NUM26
as NUM146_99_2_, transactio0_.NUM27 as NUM147_99_2_,
transactio0_.NUM28 as NUM148_99_2_, transactio0_.NUM29 as
NUM149_99_2_, transactio0_.NUM30 as NUM150_99_2_, transactio0_.NUM31
as NUM151_99_2_, transactio0_.NUM32 as NUM152_99_2_,
transactio0_.NUM33 as NUM153_99_2_, transactio0_.NUM34 as
NUM154_99_2_, transactio0_.NUM35 as NUM155_99_2_, transactio0_.NUM36
as NUM156_99_2_, transactio0_.NUM37 as NUM157_99_2_,
transactio0_.NUM38 as NUM158_99_2_, transactio0_.NUM39 as
NUM159_99_2_, transactio0_.NUM40 as NUM160_99_2_, transactio0_.NUM41
as NUM161_99_2_, transactio0_.NUM42 as NUM162_99_2_,
transactio0_.NUM43 as NUM163_99_2_, transactio0_.NUM44 as
NUM164_99_2_, transactio0_.NUM45 as NUM165_99_2_, transactio0_.NUM46
as NUM166_99_2_, transactio0_.NUM47 as NUM167_99_2_,
transactio0_.NUM48 as NUM168_99_2_, transactio0_.NUM49 as
NUM169_99_2_, transactio0_.NUM50 as NUM170_99_2_, transactio0_.NUM51
as NUM171_99_2_, transactio0_.DATE1 as DATE172_99_2_,
transactio0_.DATE2 as DATE173_99_2_, transactio0_.DATE3 as
DATE174_99_2_, transactio0_.DATE4 as DATE175_99_2_,
transactio0_.DATE5 as DATE176_99_2_, transactio0_.DATE6 as
DATE177_99_2_, transactio0_.DATE7 as DATE178_99_2_,
transactio0_.DATE8 as DATE179_99_2_, transactio0_.DATE9 as
DATE180_99_2_, transactio0_.DATE10 as DATE181_99_2_,
transactio0_.DATE11 as DATE182_99_2_, transactio0_.DATE12 as
DATE183_99_2_, transactio0_.DATE13 as DATE184_99_2_,
transactio0_.DATE14 as DATE185_99_2_, transactio0_.DATE15 as
DATE186_99_2_, transactio0_.DATE16 as DATE187_99_2_,
transactio0_.DATE17 as DATE188_99_2_, transactio0_.DATE18 as
DATE189_99_2_, transactio0_.DATE19 as DATE190_99_2_,
transactio0_.DATE20 as DATE191_99_2_, transactio0_.DATE21 as
DATE192_99_2_, transactio0_.DATE22 as DATE193_99_2_,
transactio0_.DATE23 as DATE194_99_2_, transactio0_.DATE24 as
DATE195_99_2_, transactio0_.DATE25 as DATE196_99_2_,
transactio0_.DATE26 as DATE197_99_2_, transactio0_.DATE27 as
DATE198_99_2_, transactio0_.DATE28 as DATE199_99_2_,
transactio0_.DATE29 as DATE200_99_2_, transactio0_.DATE30 as
DATE201_99_2_, transactio0_.DATE31 as DATE202_99_2_,
transactio0_.DATE32 as DATE203_99_2_, transactio0_.DATE33 as
DATE204_99_2_, transactio0_.DATE34 as DATE205_99_2_,
transactio0_.DATE35 as DATE206_99_2_, transactio0_.DATE36 as
DATE207_99_2_, transactio0_.DATE37 as DATE208_99_2_,
transactio0_.DATE38 as DATE209_99_2_, stshdr1_.ST_ID as ST1_72_0_,
stshdr1_.ST_NAME as ST2_72_0_, stshdr1_.ST_SYS as ST3_72_0_,
stshdr1_.ST_TYPE as ST4_72_0_, stshdr1_.ST_DESC as ST5_72_0_,
stshdr1_.ST_RLID as ST6_72_0_, stshdr1_.ST_SDT as ST7_72_0_,
stshdr1_.ST_EDT as ST8_72_0_, stshdr1_.ST_ICON as ST9_72_0_,
stshdr1_.ST_CRTDBY as ST10_72_0_, stshdr1_.ST_MODBY as ST11_72_0_,
stshdr1_.ST_CRTDDT as ST12_72_0_, stshdr1_.ST_MODDT as ST13_72_0_,
stshdr1_.EN_ID as EN14_72_0_, stshdr1_.STE_ID as STE15_72_0_,
stshdr1_.ST_SHOW as ST16_72_0_, invtpehdr2_.IV_TYPE as IV1_114_1_,
invtpehdr2_.IV_CRTDBY as IV2_114_1_, invtpehdr2_.IV_CRTDDT as
IV3_114_1_, invtpehdr2_.IV_DESC as IV4_114_1_, invtpehdr2_.IV_MODBY
as IV5_114_1_, invtpehdr2_.IV_MODDT as IV6_114_1_,
invtpehdr2_.IV_NAME as IV7_114_1_ from TRANSACTION transactio0_,
STS_HDR stshdr1_, invtpe_hdr invtpehdr2_ where
transactio0_.STATUSID=stshdr1_.ST_ID(+) and
transactio0_.TYPE=invtpehdr2_.IV_TYPE(+) and transactio0_.REFID=:1
and transactio0_.TXNDATE=:2
RATIONALE: The SQL statement with SQL_ID "7md60axwywfp7" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 7md60axwywfp7
SELECT inv.cs_id ,SUM(CASE WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate)
BETWEEN -999 AND 0) THEN NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 1 AND 30) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 31 AND 60) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 61 AND 90) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 91 AND 120) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 121 AND 150) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 151 AND 180) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),SUM(CASE
WHEN(TRUNC(CURRENT_DATE-inv.iv_duedate) BETWEEN 181 AND 99999) THEN
NVL(accr.AR_OUTAMT, 0) ELSE 0 END),0,0,0,0 FROM inv_txn inv, accr_txn
accr WHERE inv.IV_ID = accr.IV_ID AND inv.iv_date=accr.iv_date AND
inv.iv_id in (SELECT inv2.iv_id FROM inv_txn inv2 where inv2.cs_id in
(SELECT DISTINCT CS_ID FROM inv_txn where
iv_moddt>=to_date('10/06/11:05:39', 'dd/mm/YY:HH24:MI') AND
en_id=3220 AND iv_type='Invoice') AND inv2.iv_closed=0 AND
inv2.iv_type='Invoice') GROUP BY inv.cs_id
RATIONALE: The SQL statement with SQL_ID "800tka2nr78k4" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 800tka2nr78k4
select accrtxn0_.IV_ID as IV1_1_0_, accrtxn0_.IV_DATE as IV2_1_0_,
accrtxn0_.SB_ID as SB3_1_0_, accrtxn0_.AR_BTHDTE as AR4_1_0_,
accrtxn0_.AR_PAYR as AR5_1_0_, accrtxn0_.AR_INVVNS as AR6_1_0_,
accrtxn0_.AR_INVVNO as AR7_1_0_, accrtxn0_.AR_DESC as AR8_1_0_,
accrtxn0_.AR_SLSPRN as AR9_1_0_, accrtxn0_.AR_CUSGRP as AR10_1_0_,
accrtxn0_.AR_XCHRTPE as AR11_1_0_, accrtxn0_.AR_XCHRTE as AR12_1_0_,
accrtxn0_.AR_FCURAMT as AR13_1_0_, accrtxn0_.AR_VATAMT as AR14_1_0_,
accrtxn0_.AR_CHGTPE as AR15_1_0_, accrtxn0_.AR_MRKUP as AR16_1_0_,
accrtxn0_.AR_FWUDTE as AR17_1_0_, accrtxn0_.AR_ACCDTE as AR18_1_0_,
accrtxn0_.ACC_CODE as ACC19_1_0_, accrtxn0_.AR_BANK as AR20_1_0_,
accrtxn0_.AR_CDSCT as AR21_1_0_, accrtxn0_.AR_PYTMTD as AR22_1_0_,
accrtxn0_.AR_RECCDE as AR23_1_0_, accrtxn0_.AR_RECDTE as AR24_1_0_,
accrtxn0_.AR_ADVCDE as AR25_1_0_, accrtxn0_.AR_PYREM as AR26_1_0_,
accrtxn0_.AR_LREMDTE as AR27_1_0_, accrtxn0_.AR_PTREMDTE as
AR28_1_0_, accrtxn0_.AR_STPBY as AR29_1_0_, accrtxn0_.AR_STPDTE as
AR30_1_0_, accrtxn0_.AR_NOPYTRM as AR31_1_0_, accrtxn0_.AR_INTINVB as
AR32_1_0_, accrtxn0_.AR_INTINVS as AR33_1_0_, accrtxn0_.AR_CINTAMT as
AR34_1_0_, accrtxn0_.AR_FLGCDE as AR35_1_0_, accrtxn0_.AR_GPINDTE as
AR36_1_0_, accrtxn0_.AR_TXT as AR37_1_0_, accrtxn0_.AR_DOCLNK as
AR38_1_0_, accrtxn0_.AR_DRTPRD as AR39_1_0_, accrtxn0_.AR_DRPRDTE as
AR40_1_0_, accrtxn0_.AR_PRODGRP as AR41_1_0_, accrtxn0_.AR_ORDNO as
AR42_1_0_, accrtxn0_.AR_TXNDTE as AR43_1_0_, accrtxn0_.AR_FRTTRM as
AR44_1_0_, accrtxn0_.AR_NBR as AR45_1_0_, accrtxn0_.AR_PRINT as
AR46_1_0_, accrtxn0_.AR_CRTDBY as AR47_1_0_, accrtxn0_.AR_MODBY as
AR48_1_0_, accrtxn0_.AR_CRTDDT as AR49_1_0_, accrtxn0_.AR_MODDT as
AR50_1_0_, accrtxn0_.EN_ID as EN51_1_0_, accrtxn0_.STE_ID as
STE52_1_0_, accrtxn0_.AR_AMT as AR53_1_0_, accrtxn0_.AR_OUTAMT as
AR54_1_0_, accrtxn0_.AR_STR1 as AR55_1_0_, accrtxn0_.AR_STR2 as
AR56_1_0_, accrtxn0_.AR_STR3 as AR57_1_0_, accrtxn0_.AR_STR4 as
AR58_1_0_, accrtxn0_.AR_STR5 as AR59_1_0_, accrtxn0_.AR_STR6 as
AR60_1_0_, accrtxn0_.AR_STR7 as AR61_1_0_, accrtxn0_.AR_STR8 as
AR62_1_0_, accrtxn0_.AR_STR9 as AR63_1_0_, accrtxn0_.AR_STR10 as
AR64_1_0_, accrtxn0_.AR_STR11 as AR65_1_0_, accrtxn0_.AR_STR12 as
AR66_1_0_, accrtxn0_.AR_STR13 as AR67_1_0_, accrtxn0_.AR_STR14 as
AR68_1_0_, accrtxn0_.AR_STR15 as AR69_1_0_, accrtxn0_.AR_NUM1 as
AR70_1_0_, accrtxn0_.AR_NUM2 as AR71_1_0_, accrtxn0_.AR_NUM3 as
AR72_1_0_, accrtxn0_.AR_NUM4 as AR73_1_0_, accrtxn0_.AR_NUM5 as
AR74_1_0_, accrtxn0_.AR_NUM6 as AR75_1_0_, accrtxn0_.AR_NUM7 as
AR76_1_0_, accrtxn0_.AR_NUM8 as AR77_1_0_, accrtxn0_.AR_NUM9 as
AR78_1_0_, accrtxn0_.AR_NUM10 as AR79_1_0_, accrtxn0_.AR_DATE1 as
AR80_1_0_, accrtxn0_.AR_DATE2 as AR81_1_0_, accrtxn0_.AR_DATE3 as
AR82_1_0_, accrtxn0_.AR_DATE4 as AR83_1_0_, accrtxn0_.AR_DATE5 as
AR84_1_0_, accrtxn0_.AR_DATE6 as AR85_1_0_, accrtxn0_.AR_DATE7 as
AR86_1_0_, accrtxn0_.AR_DATE8 as AR87_1_0_, accrtxn0_.AR_DATE9 as
AR88_1_0_, accrtxn0_.AR_DATE10 as AR89_1_0_, accrtxn0_.AR_PROMDTE as
AR90_1_0_, accrtxn0_.TID as TID1_0_, accrtxn0_.PY_TERMS as PY92_1_0_,
accrtxn0_.PR_ID as PR93_1_0_ from ACCR_TXN accrtxn0_ where
accrtxn0_.IV_ID=:1 and accrtxn0_.IV_DATE=:2
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(34% impact [2996 seconds])
FINDING 5: 18% impact (1605 seconds)
------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 18% benefit (1605 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 75 K per second for reads and 16 K per second for
writes. The average response time for single block reads was 24
milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(34% impact [2996 seconds])
FINDING 6: 12% impact (1028 seconds)
------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
RECOMMENDATION 1: SQL Tuning, 8.5% benefit (757 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8ypzr7b9bpsd8".
RELEVANT OBJECT: SQL statement with SQL_ID 8ypzr7b9bpsd8 and
PLAN_HASH 730743856
select accounttov0_.REFID as REFID97_1_, accounttov0_.ACCOUNTTXNREFID
as ACCOUNTT2_97_1_, accounttov0_.ACCOUNTTXNREFDATE as
ACCOUNTT3_97_1_, accounttov0_.ACCOUNTID as ACCOUNTID97_1_,
accounttov0_.TXNREFID as TXNREFID97_1_, accounttov0_.PARENTACCOUNT as
PARENTAC6_97_1_, accounttov0_.ENTITY as ENTITY97_1_,
accounttov0_.SITEID as SITEID97_1_, accounttov0_.ASSIGNEDTO as
ASSIGNEDTO97_1_, accounttov0_.TXNASSTPE as TXNASSTPE97_1_,
accounttov0_.ACCOUNTNAME as ACCOUNT11_97_1_, accounttov0_.LANGUAGE as
LANGUAGE97_1_, accounttov0_.ACCOUNTCURRENCY as ACCOUNT13_97_1_,
accounttov0_.ACCOUNTGROUPID as ACCOUNT14_97_1_,
accounttov0_.ACCOUNTRESP as ACCOUNT15_97_1_,
accounttov0_.ACCOUNTSALEPERSON as ACCOUNT16_97_1_,
accounttov0_.ACCOUNTWEBSITE as ACCOUNT17_97_1_,
accounttov0_.ACCOUNTVAT as ACCOUNTVAT97_1_, accounttov0_.TERMS as
TERMS97_1_, accounttov0_.SOURCE as SOURCE97_1_, accounttov0_.ACTIVE
as ACTIVE97_1_, accounttov0_.CLOSED as CLOSED97_1_,
accounttov0_.ACCOUNTCRTDBY as ACCOUNT23_97_1_,
accounttov0_.ACCOUNTCRTDATE as ACCOUNT24_97_1_,
accounttov0_.ACCOUNTMODBY as ACCOUNT25_97_1_,
accounttov0_.ACCOUNTMODDATE as ACCOUNT26_97_1_,
accounttov0_.TXNCRTDBY as TXNCRTDBY97_1_, accounttov0_.TXNCRTDATE as
TXNCRTDATE97_1_, accounttov0_.TXNMODBY as TXNMODBY97_1_,
accounttov0_.TXNMODDATE as TXNMODDATE97_1_,
accounttov0_.LASTACTIONDATE as LASTACT31_97_1_,
accounttov0_.LASTACTION as LASTACTION97_1_, accounttov0_.LASTACTIONBY
as LASTACT33_97_1_, accounttov0_.ACCOUNTTYPE as ACCOUNT34_97_1_,
accounttov0_.TXNTYPE as TXNTYPE97_1_, accounttov0_.STATUSID as
STATUSID97_1_, accounttov0_.TXNPARENTID as TXNPARE37_97_1_,
accounttov0_.TXNCURRENCY as TXNCURR38_97_1_, accounttov0_.TXNSHIPMODE
as TXNSHIP39_97_1_, accounttov0_.TXNTERMS as TXNTERMS97_1_,
accounttov0_.TXNFOR as TXNFOR97_1_, accounttov0_.TXNREMITTO as
TXNREMITTO97_1_, accounttov0_.TXNPRODCDE as TXNPRODCDE97_1_,
accounttov0_.TXNLOC as TXNLOC97_1_, accounttov0_.TXNRECTYPE as
TXNRECTYPE97_1_, accounttov0_.TXNBU as TXNBU97_1_,
accounttov0_.TXNGROUP as TXNGROUP97_1_, accounttov0_.TXNBATCH as
TXNBATCH97_1_, accounttov0_.AID as AID97_1_, accounttov0_.TXNSHIPADD
as TXNSHIPADD97_1_, accounttov0_.TXNBILLADD as TXNBILLADD97_1_,
accounttov0_.TXNCRTTRMS as TXNCRTTRMS97_1_, accounttov0_.TID as
TID97_1_, accounttov0_.TAID as TAID97_1_, accounttov0_.TXNBATCHDATE
as TXNBATC55_97_1_, accounttov0_.ACCOUNTREGION as ACCOUNT56_97_1_,
accounttov0_.STRING1 as STRING57_97_1_, accounttov0_.STRING2 as
STRING58_97_1_, accounttov0_.STRING3 as STRING59_97_1_,
accounttov0_.STRING4 as STRING60_97_1_, accounttov0_.STRING5 as
STRING61_97_1_, accounttov0_.STRING6 as STRING62_97_1_,
accounttov0_.STRING7 as STRING63_97_1_, accounttov0_.STRING8 as
STRING64_97_1_, accounttov0_.STRING9 as STRING65_97_1_,
accounttov0_.STRING10 as STRING66_97_1_, accounttov0_.STRING11 as
STRING67_97_1_, accounttov0_.STRING12 as STRING68_97_1_,
accounttov0_.STRING13 as STRING69_97_1_, accounttov0_.STRING14 as
STRING70_97_1_, accounttov0_.STRING15 as STRING71_97_1_,
accounttov0_.STRING16 as STRING72_97_1_, accounttov0_.STRING17 as
STRING73_97_1_, accounttov0_.STRING18 as STRING74_97_1_,
accounttov0_.STRING19 as STRING75_97_1_, accounttov0_.STRING20 as
STRING76_97_1_, accounttov0_.STRING21 as STRING77_97_1_,
accounttov0_.STRING22 as STRING78_97_1_, accounttov0_.STRING23 as
STRING79_97_1_, accounttov0_.STRING24 as STRING80_97_1_,
accounttov0_.STRING25 as STRING81_97_1_, accounttov0_.STRING26 as
STRING82_97_1_, accounttov0_.STRING27 as STRING83_97_1_,
accounttov0_.STRING28 as STRING84_97_1_, accounttov0_.STRING29 as
STRING85_97_1_, accounttov0_.STRING30 as STRING86_97_1_,
accounttov0_.STRING31 as STRING87_97_1_, accounttov0_.STRING32 as
STRING88_97_1_, accounttov0_.STRING33 as STRING89_97_1_,
accounttov0_.STRING34 as STRING90_97_1_, accounttov0_.STRING35 as
STRING91_97_1_, accounttov0_.STRING36 as STRING92_97_1_,
accounttov0_.STRING37 as STRING93_97_1_, accounttov0_.STRING38 as
STRING94_97_1_, accounttov0_.STRING39 as STRING95_97_1_,
accounttov0_.STRING40 as STRING96_97_1_, accounttov0_.STRING41 as
STRING97_97_1_, accounttov0_.STRING42 as STRING98_97_1_,
accounttov0_.STRING43 as STRING99_97_1_, accounttov0_.STRING44 as
STRING100_97_1_, accounttov0_.STRING45 as STRING101_97_1_,
accounttov0_.STRING46 as STRING102_97_1_, accounttov0_.STRING47 as
STRING103_97_1_, accounttov0_.STRING48 as STRING104_97_1_,
accounttov0_.STRING49 as STRING105_97_1_, accounttov0_.STRING50 as
STRING106_97_1_, accounttov0_.STRING51 as STRING107_97_1_,
accounttov0_.STRING52 as STRING108_97_1_, accounttov0_.STRING53 as
STRING109_97_1_, accounttov0_.STRING54 as STRING110_97_1_,
accounttov0_.STRING55 as STRING111_97_1_, accounttov0_.STRING56 as
STRING112_97_1_, accounttov0_.STRING57 as STRING113_97_1_,
accounttov0_.STRING58 as STRING114_97_1_, accounttov0_.STRING59 as
STRING115_97_1_, accounttov0_.STRING60 as STRING116_97_1_,
accounttov0_.STRING61 as STRING117_97_1_, accounttov0_.STRING62 as
STRING118_97_1_, accounttov0_.STRING63 as STRING119_97_1_,
accounttov0_.STRING64 as STRING120_97_1_, accounttov0_.STRING65 as
STRING121_97_1_, accounttov0_.STRING66 as STRING122_97_1_,
accounttov0_.STRING67 as STRING123_97_1_, accounttov0_.STRING68 as
STRING124_97_1_, accounttov0_.STRING69 as STRING125_97_1_,
accounttov0_.STRING70 as STRING126_97_1_, accounttov0_.STRING71 as
STRING127_97_1_, accounttov0_.STRING72 as STRING128_97_1_,
accounttov0_.STRING73 as STRING129_97_1_, accounttov0_.STRING74 as
STRING130_97_1_, accounttov0_.STRING75 as STRING131_97_1_,
accounttov0_.TEXT1 as TEXT132_97_1_, accounttov0_.TEXT2 as
TEXT133_97_1_, accounttov0_.TEXT3 as TEXT134_97_1_,
accounttov0_.TEXT4 as TEXT135_97_1_, accounttov0_.TEXT5 as
TEXT136_97_1_, accounttov0_.TEXT6 as TEXT137_97_1_,
accounttov0_.TEXT7 as TEXT138_97_1_, accounttov0_.TEXT8 as
TEXT139_97_1_, accounttov0_.TEXT9 as TEXT140_97_1_,
accounttov0_.TEXT10 as TEXT141_97_1_, accounttov0_.NUM1 as
NUM142_97_1_, accounttov0_.NUM2 as NUM143_97_1_, accounttov0_.NUM3 as
NUM144_97_1_, accounttov0_.NUM4 as NUM145_97_1_, accounttov0_.NUM5 as
NUM146_97_1_, accounttov0_.NUM6 as NUM147_97_1_, accounttov0_.NUM7 as
NUM148_97_1_, accounttov0_.NUM8 as NUM149_97_1_, accounttov0_.NUM9 as
NUM150_97_1_, accounttov0_.NUM10 as NUM151_97_1_, accounttov0_.NUM11
as NUM152_97_1_, accounttov0_.NUM12 as NUM153_97_1_,
accounttov0_.NUM13 as NUM154_97_1_, accounttov0_.NUM14 as
NUM155_97_1_, accounttov0_.NUM15 as NUM156_97_1_, accounttov0_.NUM16
as NUM157_97_1_, accounttov0_.NUM17 as NUM158_97_1_,
accounttov0_.NUM18 as NUM159_97_1_, accounttov0_.NUM19 as
NUM160_97_1_, accounttov0_.NUM20 as NUM161_97_1_, accounttov0_.NUM21
as NUM162_97_1_, accounttov0_.NUM22 as NUM163_97_1_,
accounttov0_.NUM23 as NUM164_97_1_, accounttov0_.NUM24 as
NUM165_97_1_, accounttov0_.NUM25 as NUM166_97_1_, accounttov0_.NUM26
as NUM167_97_1_, accounttov0_.NUM27 as NUM168_97_1_,
accounttov0_.NUM28 as NUM169_97_1_, accounttov0_.NUM29 as
NUM170_97_1_, accounttov0_.NUM30 as NUM171_97_1_, accounttov0_.NUM31
as NUM172_97_1_, accounttov0_.NUM32 as NUM173_97_1_,
accounttov0_.NUM33 as NUM174_97_1_, accounttov0_.NUM34 as
NUM175_97_1_, accounttov0_.NUM35 as NUM176_97_1_, accounttov0_.NUM36
as NUM177_97_1_, accounttov0_.NUM37 as NUM178_97_1_,
accounttov0_.NUM38 as NUM179_97_1_, accounttov0_.NUM39 as
NUM180_97_1_, accounttov0_.NUM40 as NUM181_97_1_, accounttov0_.NUM41
as NUM182_97_1_, accounttov0_.NUM42 as NUM183_97_1_,
accounttov0_.NUM43 as NUM184_97_1_, accounttov0_.NUM44 as
NUM185_97_1_, accounttov0_.NUM45 as NUM186_97_1_, accounttov0_.NUM46
as NUM187_97_1_, accounttov0_.NUM47 as NUM188_97_1_,
accounttov0_.DATE1 as DATE189_97_1_, accounttov0_.DATE2 as
DATE190_97_1_, accounttov0_.DATE3 as DATE191_97_1_,
accounttov0_.DATE4 as DATE192_97_1_, accounttov0_.DATE5 as
DATE193_97_1_, accounttov0_.DATE6 as DATE194_97_1_,
accounttov0_.DATE7 as DATE195_97_1_, accounttov0_.DATE8 as
DATE196_97_1_, accounttov0_.DATE9 as DATE197_97_1_,
accounttov0_.DATE10 as DATE198_97_1_, accounttov0_.DATE11 as
DATE199_97_1_, accounttov0_.DATE12 as DATE200_97_1_,
accounttov0_.DATE13 as DATE201_97_1_, accounttov0_.DATE14 as
DATE202_97_1_, accounttov0_.DATE15 as DATE203_97_1_,
accounttov0_.DATE16 as DATE204_97_1_, accounttov0_.DATE17 as
DATE205_97_1_, accounttov0_.DATE18 as DATE206_97_1_,
accounttov0_.DATE19 as DATE207_97_1_, accounttov0_.DATE20 as
DATE208_97_1_, accounttov0_.DATE21 as DATE209_97_1_,
accounttov0_.DATE22 as DATE210_97_1_, accounttov0_.DATE23 as
DATE211_97_1_, accounttov0_.DATE24 as DATE212_97_1_,
accounttov0_.DATE25 as DATE213_97_1_, accounttov0_.DATE26 as
DATE214_97_1_, accounttov0_.DATE27 as DATE215_97_1_,
accounttov0_.DATE28 as DATE216_97_1_, accounttov0_.DATE29 as
DATE217_97_1_, accounttov0_.DATE30 as DATE218_97_1_,
accounttov0_.DATE31 as DATE219_97_1_, cstpehdr1_.CS_TYPE as
CS1_23_0_, cstpehdr1_.CS_NAME as CS2_23_0_, cstpehdr1_.CS_DESC as
CS3_23_0_, cstpehdr1_.CS_CRTDBY as CS4_23_0_, cstpehdr1_.CS_MODBY as
CS5_23_0_, cstpehdr1_.CS_CRTDDT as CS6_23_0_, cstpehdr1_.CS_MODDT as
CS7_23_0_ from ACCOUNT accounttov0_, CSTPE_HDR cstpehdr1_ where
accounttov0_.ACCOUNTTYPE=cstpehdr1_.CS_TYPE(+) and
accounttov0_.REFID=:1
RATIONALE: SQL statement with SQL_ID "8ypzr7b9bpsd8" was executed 28102
times and had an average elapsed time of 0.026 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
0.026 seconds.
RECOMMENDATION 2: SQL Tuning, 3.4% benefit (305 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"13nnn19dcfq7g".
RELEVANT OBJECT: SQL statement with SQL_ID 13nnn19dcfq7g and
PLAN_HASH 544202911
select count(*) as col_0_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_.IV_CLOSED=0 and (upper(cmtslog0_.CM_TEXT) like '%N%')
RATIONALE: SQL statement with SQL_ID "13nnn19dcfq7g" was executed 1
times and had an average elapsed time of 303 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
294 seconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(34% impact [2996 seconds])
FINDING 7: 4.5% impact (396 seconds)
------------------------------------
Buffer cache writes due to setting of the obsolete parameters
"fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"
were consuming significant database time.
RECOMMENDATION 1: DB Configuration, 4.5% benefit (396 seconds)
ACTION: Oracle's recommended solution is to control MTTR setting using
the "fast_start_mttr_target" parameter instead of the
"fast_start_io_target", "log_checkpoint_interval" and
"log_checkpoint_timeout" parameters.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: The throughput of the I/O subsystem was significantly lower
than expected. (18% impact [1605 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (34% impact [2996 seconds])
FINDING 8: 2.7% impact (243 seconds)
------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
The network latency of the cluster interconnect was within acceptable
limits of 1 milliseconds.
Read and write contention on database blocks was not consuming
significant database time in the cluster.
Global Cache Service Processes (LMSn) in other instances were performing
within acceptable limits of 1 milliseconds.
Waits on "buffer busy" events were not consuming significant database
time.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Cluster" was consuming significant database time.
(2.7% impact [243 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The 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.