DETAILED ADDM REPORT FOR TASK 'TASK_161417' WITH ID 161417
----------------------------------------------------------
Analysis Period: from 05-JUN-2011 23:00 to 07-JUN-2011 15:00
Database ID/Instance: 4127787440/1
Database/Instance Names: CAPGENT/capgent1
Host Name: rac1.capgent.com
Database Version: 10.2.0.4.0
Snapshot Range: from 21358 to 21398
Database Time: 30463 seconds
Average Database Load: .2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (30463 seconds)
--------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 86% benefit (26250 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 6720 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "3840 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(57% impact [17471 seconds])
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (5.9% impact [1800 seconds])
SYMPTOM: Contention for latches related to the shared pool was
consuming significant database time. (1.9% impact [581
seconds])
INFO: Waits for "cursor: pin S wait on X" amounted to 1% of
database time.
SYMPTOM: Wait class "Concurrency" was consuming significant
database time. (2.1% impact [625 seconds])
FINDING 2: 36% impact (11042 seconds)
-------------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 18% benefit (5437 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: 71 full object
scans, 751822 physical reads, 31902 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "237t2j21fva8x" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 237t2j21fva8x
/* named native SQL query search.account.assignment */ SELECT /*+
INDEX(CST_HDR CST_HDR_ENID_TYPE_CSACT_IDX)*/ CST_HDR.CS_ID FROM
CST_HDR INNER JOIN INV_TXN ON CST_HDR.CS_ID = INV_TXN.CS_ID
WHERE (CST_HDR.CS_ACTIVE = 1 AND CST_HDR.EN_ID = :1 AND
CST_HDR.CS_TYPE =
(SELECT /*+ INDEX(CSTPE_HDR CSTPE_HDR_CSNAME_INDEX)*/ CS_TYPE FROM
CSTPE_HDR WHERE CS_NAME = :2) AND INV_TXN.IV_TYPE = :3 AND
INV_TXN.IV_ASSGNTO IS NULL)
RATIONALE: The SQL statement with SQL_ID "bkhf6mdtuuark" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID bkhf6mdtuuark
select * from ( select row_.*, rownum rownum_ from ( select /*+
ALL_ROWS */ stshdr3_.ST_NAME as col_0_0_,
sum(cstagehdr1_.CS_AGEBUCKET1+cstagehdr1_.CS_AGEBUCKET2+cstagehdr1_.C
S_AGEBUCKET3+cstagehdr1_.CS_AGEBUCKET4+cstagehdr1_.CS_AGEBUCKET5+csta
gehdr1_.CS_AGEBUCKET6+cstagehdr1_.CS_AGEBUCKET7+cstagehdr1_.CS_AGEBUC
KET8+cstagehdr1_.CS_AGEBUCKET9+cstagehdr1_.CS_AGEBUCKET10+cstagehdr1_
.CS_AGEBUCKET11+cstagehdr1_.CS_AGEBUCKET12) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_, stshdr3_.ST_ID as
col_10_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
stshdr3_.ST_NAME , stshdr3_.ST_ID , csthdr0_.rowid order by
csthdr0_.rowid ASC ) row_ where rownum <= :1) where rownum_ > :2
RATIONALE: The SQL statement with SQL_ID "9wvwyr9w4ubn0" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 9wvwyr9w4ubn0
select * from ( select /*+ INDEX(INVTXN2_INV_TXN_CSID_IDX) */
stshdr3_.ST_NAME as col_0_0_,
sum(cstagehdr1_.CS_AGEBUCKET1+cstagehdr1_.CS_AGEBUCKET2+cstagehdr1_.C
S_AGEBUCKET3+cstagehdr1_.CS_AGEBUCKET4+cstagehdr1_.CS_AGEBUCKET5+csta
gehdr1_.CS_AGEBUCKET6+cstagehdr1_.CS_AGEBUCKET7+cstagehdr1_.CS_AGEBUC
KET8+cstagehdr1_.CS_AGEBUCKET9+cstagehdr1_.CS_AGEBUCKET10+cstagehdr1_
.CS_AGEBUCKET11+cstagehdr1_.CS_AGEBUCKET12) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_, stshdr3_.ST_ID as
col_10_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
stshdr3_.ST_NAME , stshdr3_.ST_ID ) where rownum <= :1
RATIONALE: The SQL statement with SQL_ID "4hwgx07x6cctb" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 4hwgx07x6cctb
update CST_HDR set CS_STMT=2 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)=54
and invtxns2_.IV_TXAMT>0 and csthdr1_.CS_NUM9>invtxns2_.IV_TXAMT/10
and (csthdr1_.CS_STMT is null or 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 "c13h8s2r8a3jh" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID c13h8s2r8a3jh
select * from ( select /*+ ALL_ROWS */ invtxns2_.IV_ASSGNTO as
col_0_0_, sum(cstagehdr1_.CS_AGEBUCKET1+cstagehdr1_.CS_AGEBUCKET2+cst
agehdr1_.CS_AGEBUCKET3+cstagehdr1_.CS_AGEBUCKET4+cstagehdr1_.CS_AGEBU
CKET5+cstagehdr1_.CS_AGEBUCKET6+cstagehdr1_.CS_AGEBUCKET7+cstagehdr1_
.CS_AGEBUCKET8+cstagehdr1_.CS_AGEBUCKET9+cstagehdr1_.CS_AGEBUCKET10+c
stagehdr1_.CS_AGEBUCKET11+cstagehdr1_.CS_AGEBUCKET12) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_ from CST_HDR csthdr0_,
CST_AGEHDR cstagehdr1_, INV_TXN invtxns2_ where
csthdr0_.CS_ID=cstagehdr1_.CS_ID(+) and
csthdr0_.CS_ID=invtxns2_.CS_ID(+) and (csthdr0_.EN_ID in (4060)) and
cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account' and
csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
invtxns2_.IV_ASSGNTO ) where rownum <= :1
RECOMMENDATION 2: Segment Tuning, 5.9% benefit (1806 seconds)
ACTION: Investigate application logic involving I/O on TABLE
"DNBI.STS_LOG" with object id 56506.
RELEVANT OBJECT: database object with id 56506
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 48422 physical reads, 41131 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "39q7fgx59n296" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 39q7fgx59n296
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24) or (stslogs0_.IV_ID=:25 and
stslogs0_.IV_DATE=:26) or (stslogs0_.IV_ID=:27 and
stslogs0_.IV_DATE=:28) or (stslogs0_.IV_ID=:29 and
stslogs0_.IV_DATE=:30) or (stslogs0_.IV_ID=:31 and
stslogs0_.IV_DATE=:32) or (stslogs0_.IV_ID=:33 and
stslogs0_.IV_DATE=:34) or (stslogs0_.IV_ID=:35 and
stslogs0_.IV_DATE=:36) or (stslogs0_.IV_ID=:37 and
stslogs0_.IV_DATE=:38) or (stslogs0_.IV_ID=:39 and
stslogs0_.IV_DATE=:40) or (stslogs0_.IV_ID=:41 and
stslogs0_.IV_DATE=:42) or (stslogs0_.IV_ID=:43 and
stslogs0_.IV_DATE=:44) or (stslogs0_.IV_ID=:45 and
stslogs0_.IV_DATE=:46) or (stslogs0_.IV_ID=:47 and
stslogs0_.IV_DATE=:48) or (stslogs0_.IV_ID=:49 and
stslogs0_.IV_DATE=:50))
RATIONALE: The SQL statement with SQL_ID "3kbhbc0tjmwgm" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 3kbhbc0tjmwgm
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24))
RATIONALE: The SQL statement with SQL_ID "frz0mh8y2abxu" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID frz0mh8y2abxu
update STS_LOG set ST_IDT1=:1, ST_IDT2=:2, ST_TXNTPE=:3, ST_DESC=:4,
ST_CRTDBY=:5, ST_MODBY=:6, ST_CRTDDT=:7, ST_MODDT=:8, ST_ISCURR=:9,
EN_ID=:10, STE_ID=:11, TID=:12, AID=:13, ST_ID=:14, IV_ID=:15,
IV_DATE=:16, CS_ID=:17 where LOG_ID=:18
RATIONALE: The SQL statement with SQL_ID "8ut36qw578dfw" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8ut36qw578dfw
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2
RATIONALE: The SQL statement with SQL_ID "8szmwam7fysa3" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RECOMMENDATION 3: Segment Tuning, 5.7% benefit (1746 seconds)
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, 64497 physical reads, 4522 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "3w74n8znm9192" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 3w74n8znm9192
select invtxn1_.IV_GPINVNO as col_0_0_, invtxn1_.IV_TYPE as col_1_0_,
cmtslog0_.CM_CRTDT as col_2_0_, cmtslog0_.CM_CRTDBY as col_3_0_,
cmtslog0_.CM_ACTION as col_4_0_, cmtslog0_.CM_TEXT as col_5_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 (4160))
and to_date(cmtslog0_.CM_CRTDT, 'yyyy-MM-dd')=to_date(current_date,
'yyyy-MM-dd') and invtxn1_.IV_TYPE='Account' order by
cmtslog0_.CM_CRTDT DESC
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 "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 "8szmwam7fysa3" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: The SQL statement with SQL_ID "4q0zz1jbwnwp2" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 4q0zz1jbwnwp2
RECOMMENDATION 4: Segment Tuning, 4.2% benefit (1290 seconds)
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: 1 full object
scans, 44383 physical reads, 676 physical writes and 0 direct reads.
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
RATIONALE: The SQL statement with SQL_ID "7wgks43wrjtrz" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 7wgks43wrjtrz
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM
TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0, :B4 ,
'TRUE', :B5 )) U
RATIONALE: The SQL statement with SQL_ID "8szmwam7fysa3" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
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 "gfmnhfnr4w9ay" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID gfmnhfnr4w9ay
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.en_id=3220 AND iv_closed=0 AND iv_type='Invoice' GROUP BY
inv.cs_id
RECOMMENDATION 5: Segment Tuning, 2.5% benefit (764 seconds)
ACTION: Investigate application logic involving I/O on INDEX
"DNBI.STS_LOG_COMP_IDX" with object id 86115.
RELEVANT OBJECT: database object with id 86115
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 26639 physical reads, 5814 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "39q7fgx59n296" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 39q7fgx59n296
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24) or (stslogs0_.IV_ID=:25 and
stslogs0_.IV_DATE=:26) or (stslogs0_.IV_ID=:27 and
stslogs0_.IV_DATE=:28) or (stslogs0_.IV_ID=:29 and
stslogs0_.IV_DATE=:30) or (stslogs0_.IV_ID=:31 and
stslogs0_.IV_DATE=:32) or (stslogs0_.IV_ID=:33 and
stslogs0_.IV_DATE=:34) or (stslogs0_.IV_ID=:35 and
stslogs0_.IV_DATE=:36) or (stslogs0_.IV_ID=:37 and
stslogs0_.IV_DATE=:38) or (stslogs0_.IV_ID=:39 and
stslogs0_.IV_DATE=:40) or (stslogs0_.IV_ID=:41 and
stslogs0_.IV_DATE=:42) or (stslogs0_.IV_ID=:43 and
stslogs0_.IV_DATE=:44) or (stslogs0_.IV_ID=:45 and
stslogs0_.IV_DATE=:46) or (stslogs0_.IV_ID=:47 and
stslogs0_.IV_DATE=:48) or (stslogs0_.IV_ID=:49 and
stslogs0_.IV_DATE=:50))
RATIONALE: The SQL statement with SQL_ID "8szmwam7fysa3" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: The SQL statement with SQL_ID "8ut36qw578dfw" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8ut36qw578dfw
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2
RATIONALE: The SQL statement with SQL_ID "3kbhbc0tjmwgm" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 3kbhbc0tjmwgm
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24))
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(57% impact [17471 seconds])
FINDING 3: 27% impact (8255 seconds)
------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 8.2% benefit (2487 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"237t2j21fva8x".
RELEVANT OBJECT: SQL statement with SQL_ID 237t2j21fva8x and
PLAN_HASH 2034021177
/* named native SQL query search.account.assignment */ SELECT /*+
INDEX(CST_HDR CST_HDR_ENID_TYPE_CSACT_IDX)*/ CST_HDR.CS_ID FROM
CST_HDR INNER JOIN INV_TXN ON CST_HDR.CS_ID = INV_TXN.CS_ID
WHERE (CST_HDR.CS_ACTIVE = 1 AND CST_HDR.EN_ID = :1 AND
CST_HDR.CS_TYPE =
(SELECT /*+ INDEX(CSTPE_HDR CSTPE_HDR_CSNAME_INDEX)*/ CS_TYPE FROM
CSTPE_HDR WHERE CS_NAME = :2) AND INV_TXN.IV_TYPE = :3 AND
INV_TXN.IV_ASSGNTO IS NULL)
RATIONALE: SQL statement with SQL_ID "237t2j21fva8x" was executed 24
times and had an average elapsed time of 101 seconds.
RECOMMENDATION 2: SQL Tuning, 7.7% benefit (2352 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"bkhf6mdtuuark".
RELEVANT OBJECT: SQL statement with SQL_ID bkhf6mdtuuark and
PLAN_HASH 2154183644
select * from ( select row_.*, rownum rownum_ from ( select /*+
ALL_ROWS */ stshdr3_.ST_NAME as col_0_0_,
sum(cstagehdr1_.CS_AGEBUCKET1+cstagehdr1_.CS_AGEBUCKET2+cstagehdr1_.C
S_AGEBUCKET3+cstagehdr1_.CS_AGEBUCKET4+cstagehdr1_.CS_AGEBUCKET5+csta
gehdr1_.CS_AGEBUCKET6+cstagehdr1_.CS_AGEBUCKET7+cstagehdr1_.CS_AGEBUC
KET8+cstagehdr1_.CS_AGEBUCKET9+cstagehdr1_.CS_AGEBUCKET10+cstagehdr1_
.CS_AGEBUCKET11+cstagehdr1_.CS_AGEBUCKET12) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_, stshdr3_.ST_ID as
col_10_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
stshdr3_.ST_NAME , stshdr3_.ST_ID , csthdr0_.rowid order by
csthdr0_.rowid ASC ) row_ where rownum <= :1) where rownum_ > :2
RATIONALE: SQL statement with SQL_ID "bkhf6mdtuuark" was executed 27
times and had an average elapsed time of 85 seconds.
RECOMMENDATION 3: SQL Tuning, 4.7% benefit (1442 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"39q7fgx59n296".
RELEVANT OBJECT: SQL statement with SQL_ID 39q7fgx59n296 and
PLAN_HASH 1246553133
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24) or (stslogs0_.IV_ID=:25 and
stslogs0_.IV_DATE=:26) or (stslogs0_.IV_ID=:27 and
stslogs0_.IV_DATE=:28) or (stslogs0_.IV_ID=:29 and
stslogs0_.IV_DATE=:30) or (stslogs0_.IV_ID=:31 and
stslogs0_.IV_DATE=:32) or (stslogs0_.IV_ID=:33 and
stslogs0_.IV_DATE=:34) or (stslogs0_.IV_ID=:35 and
stslogs0_.IV_DATE=:36) or (stslogs0_.IV_ID=:37 and
stslogs0_.IV_DATE=:38) or (stslogs0_.IV_ID=:39 and
stslogs0_.IV_DATE=:40) or (stslogs0_.IV_ID=:41 and
stslogs0_.IV_DATE=:42) or (stslogs0_.IV_ID=:43 and
stslogs0_.IV_DATE=:44) or (stslogs0_.IV_ID=:45 and
stslogs0_.IV_DATE=:46) or (stslogs0_.IV_ID=:47 and
stslogs0_.IV_DATE=:48) or (stslogs0_.IV_ID=:49 and
stslogs0_.IV_DATE=:50))
RATIONALE: SQL statement with SQL_ID "39q7fgx59n296" was executed 17572
times and had an average elapsed time of 0.081 seconds.
RECOMMENDATION 4: SQL Tuning, 4.5% benefit (1361 seconds)
ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
call dbms_stats.gather_database_stats_job_proc ( )
RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1
times and had an average elapsed time of 1361 seconds.
RECOMMENDATION 5: SQL Tuning, 2.4% benefit (726 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"800tka2nr78k4".
RELEVANT OBJECT: SQL statement with SQL_ID 800tka2nr78k4 and
PLAN_HASH 3196279720
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
RATIONALE: SQL statement with SQL_ID "800tka2nr78k4" was executed 591615
times and had an average elapsed time of 0.0012 seconds.
FINDING 4: 24% impact (7263 seconds)
------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
RECOMMENDATION 1: SQL Tuning, 8.2% benefit (2487 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"237t2j21fva8x".
RELEVANT OBJECT: SQL statement with SQL_ID 237t2j21fva8x and
PLAN_HASH 2034021177
/* named native SQL query search.account.assignment */ SELECT /*+
INDEX(CST_HDR CST_HDR_ENID_TYPE_CSACT_IDX)*/ CST_HDR.CS_ID FROM
CST_HDR INNER JOIN INV_TXN ON CST_HDR.CS_ID = INV_TXN.CS_ID
WHERE (CST_HDR.CS_ACTIVE = 1 AND CST_HDR.EN_ID = :1 AND
CST_HDR.CS_TYPE =
(SELECT /*+ INDEX(CSTPE_HDR CSTPE_HDR_CSNAME_INDEX)*/ CS_TYPE FROM
CSTPE_HDR WHERE CS_NAME = :2) AND INV_TXN.IV_TYPE = :3 AND
INV_TXN.IV_ASSGNTO IS NULL)
RATIONALE: SQL statement with SQL_ID "237t2j21fva8x" was executed 24
times and had an average elapsed time of 101 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
85 seconds.
RECOMMENDATION 2: SQL Tuning, 7.7% benefit (2352 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"bkhf6mdtuuark".
RELEVANT OBJECT: SQL statement with SQL_ID bkhf6mdtuuark and
PLAN_HASH 2154183644
select * from ( select row_.*, rownum rownum_ from ( select /*+
ALL_ROWS */ stshdr3_.ST_NAME as col_0_0_,
sum(cstagehdr1_.CS_AGEBUCKET1+cstagehdr1_.CS_AGEBUCKET2+cstagehdr1_.C
S_AGEBUCKET3+cstagehdr1_.CS_AGEBUCKET4+cstagehdr1_.CS_AGEBUCKET5+csta
gehdr1_.CS_AGEBUCKET6+cstagehdr1_.CS_AGEBUCKET7+cstagehdr1_.CS_AGEBUC
KET8+cstagehdr1_.CS_AGEBUCKET9+cstagehdr1_.CS_AGEBUCKET10+cstagehdr1_
.CS_AGEBUCKET11+cstagehdr1_.CS_AGEBUCKET12) as col_1_0_,
sum(cstagehdr1_.CS_AGEBUCKET1) as col_2_0_,
sum(cstagehdr1_.CS_AGEBUCKET2) as col_3_0_,
sum(cstagehdr1_.CS_AGEBUCKET3) as col_4_0_,
sum(cstagehdr1_.CS_AGEBUCKET4) as col_5_0_,
sum(cstagehdr1_.CS_AGEBUCKET5) as col_6_0_,
sum(cstagehdr1_.CS_AGEBUCKET6) as col_7_0_,
sum(cstagehdr1_.CS_AGEBUCKET7) as col_8_0_,
sum(cstagehdr1_.CS_AGEBUCKET8) as col_9_0_, stshdr3_.ST_ID as
col_10_0_ from CST_HDR csthdr0_, CST_AGEHDR cstagehdr1_, INV_TXN
invtxns2_, STS_HDR stshdr3_ where csthdr0_.CS_ID=cstagehdr1_.CS_ID(+)
and csthdr0_.CS_ID=invtxns2_.CS_ID(+) and
invtxns2_.IV_STID=stshdr3_.ST_ID(+) and (csthdr0_.EN_ID in (4060))
and cstagehdr1_.CS_ID=csthdr0_.CS_ID and invtxns2_.IV_TYPE='Account'
and csthdr0_.CS_TYPE=2502 and csthdr0_.CS_ACTIVE=1 group by
stshdr3_.ST_NAME , stshdr3_.ST_ID , csthdr0_.rowid order by
csthdr0_.rowid ASC ) row_ where rownum <= :1) where rownum_ > :2
RATIONALE: SQL statement with SQL_ID "bkhf6mdtuuark" was executed 27
times and had an average elapsed time of 85 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
71 seconds.
RECOMMENDATION 3: SQL Tuning, 4.7% benefit (1442 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"39q7fgx59n296".
RELEVANT OBJECT: SQL statement with SQL_ID 39q7fgx59n296 and
PLAN_HASH 1246553133
select stslogs0_.IV_ID as IV16_1_, stslogs0_.IV_DATE as IV17_1_,
stslogs0_.LOG_ID as LOG1_1_, stslogs0_.LOG_ID as LOG1_73_0_,
stslogs0_.ST_IDT1 as ST2_73_0_, stslogs0_.ST_IDT2 as ST3_73_0_,
stslogs0_.ST_TXNTPE as ST4_73_0_, stslogs0_.ST_DESC as ST5_73_0_,
stslogs0_.ST_CRTDBY as ST6_73_0_, stslogs0_.ST_MODBY as ST7_73_0_,
stslogs0_.ST_CRTDDT as ST8_73_0_, stslogs0_.ST_MODDT as ST9_73_0_,
stslogs0_.ST_ISCURR as ST10_73_0_, stslogs0_.EN_ID as EN11_73_0_,
stslogs0_.STE_ID as STE12_73_0_, stslogs0_.TID as TID73_0_,
stslogs0_.AID as AID73_0_, stslogs0_.ST_ID as ST15_73_0_,
stslogs0_.IV_ID as IV16_73_0_, stslogs0_.IV_DATE as IV17_73_0_,
stslogs0_.CS_ID as CS18_73_0_ from STS_LOG stslogs0_ where (
stslogs0_.st_txntpe='Receivables') and ((stslogs0_.IV_ID=:1 and
stslogs0_.IV_DATE=:2) or (stslogs0_.IV_ID=:3 and
stslogs0_.IV_DATE=:4) or (stslogs0_.IV_ID=:5 and
stslogs0_.IV_DATE=:6) or (stslogs0_.IV_ID=:7 and
stslogs0_.IV_DATE=:8) or (stslogs0_.IV_ID=:9 and
stslogs0_.IV_DATE=:10) or (stslogs0_.IV_ID=:11 and
stslogs0_.IV_DATE=:12) or (stslogs0_.IV_ID=:13 and
stslogs0_.IV_DATE=:14) or (stslogs0_.IV_ID=:15 and
stslogs0_.IV_DATE=:16) or (stslogs0_.IV_ID=:17 and
stslogs0_.IV_DATE=:18) or (stslogs0_.IV_ID=:19 and
stslogs0_.IV_DATE=:20) or (stslogs0_.IV_ID=:21 and
stslogs0_.IV_DATE=:22) or (stslogs0_.IV_ID=:23 and
stslogs0_.IV_DATE=:24) or (stslogs0_.IV_ID=:25 and
stslogs0_.IV_DATE=:26) or (stslogs0_.IV_ID=:27 and
stslogs0_.IV_DATE=:28) or (stslogs0_.IV_ID=:29 and
stslogs0_.IV_DATE=:30) or (stslogs0_.IV_ID=:31 and
stslogs0_.IV_DATE=:32) or (stslogs0_.IV_ID=:33 and
stslogs0_.IV_DATE=:34) or (stslogs0_.IV_ID=:35 and
stslogs0_.IV_DATE=:36) or (stslogs0_.IV_ID=:37 and
stslogs0_.IV_DATE=:38) or (stslogs0_.IV_ID=:39 and
stslogs0_.IV_DATE=:40) or (stslogs0_.IV_ID=:41 and
stslogs0_.IV_DATE=:42) or (stslogs0_.IV_ID=:43 and
stslogs0_.IV_DATE=:44) or (stslogs0_.IV_ID=:45 and
stslogs0_.IV_DATE=:46) or (stslogs0_.IV_ID=:47 and
stslogs0_.IV_DATE=:48) or (stslogs0_.IV_ID=:49 and
stslogs0_.IV_DATE=:50))
RATIONALE: SQL statement with SQL_ID "39q7fgx59n296" was executed 17572
times and had an average elapsed time of 0.081 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
0.079 seconds.
RECOMMENDATION 4: SQL Tuning, 4.2% benefit (1274 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8szmwam7fysa3".
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and
PLAN_HASH 2976124318
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 46
times and had an average elapsed time of 27 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
26 seconds.
RECOMMENDATION 5: SQL Tuning, 2.4% benefit (726 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"800tka2nr78k4".
RELEVANT OBJECT: SQL statement with SQL_ID 800tka2nr78k4 and
PLAN_HASH 3196279720
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
RATIONALE: SQL statement with SQL_ID "800tka2nr78k4" was executed 591615
times and had an average elapsed time of 0.0012 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
0.0011 seconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(57% impact [17471 seconds])
FINDING 5: 22% impact (6689 seconds)
------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
RECOMMENDATION 1: Host Configuration, 22% benefit (6689 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 83 K per second for reads and 15 K per second for
writes. The average response time for single block reads was 16
milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(57% impact [17471 seconds])
FINDING 6: 8.4% impact (2554 seconds)
-------------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 4.7% benefit (1426 seconds)
ACTION: Investigate the SQL statement with SQL_ID "6mcpb06rctk0x" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 6mcpb06rctk0x
call dbms_space.auto_space_advisor_job_proc ( )
RATIONALE: SQL statement with SQL_ID "6mcpb06rctk0x" was executed 1
times and had an average elapsed time of 1425 seconds.
RATIONALE: Average time spent in PL/SQL execution was 1333 seconds.
RECOMMENDATION 2: SQL Tuning, 4.2% benefit (1274 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8szmwam7fysa3".
RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and
PLAN_HASH 2976124318
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 46
times and had an average elapsed time of 27 seconds.
RATIONALE: Average time spent in PL/SQL execution was 26 seconds.
FINDING 7: 7.8% impact (2374 seconds)
-------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: Application Analysis, 5.7% benefit (1740 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, 2.1% benefit (634 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"849twcr87pk8z".
RELEVANT OBJECT: SQL statement with SQL_ID 849twcr87pk8z and
PLAN_HASH 1979856718
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 ('9079698' , '6750740' , '9108698' ,
'9108700' , '9108699' , '9300831' , '9422880' , '9422875' , '9422887'
, '9422885' , '9422878' , '9422872' , '9422873' , '9422881' ,
'9422876' , '9422874' , '9422883' , '9422884' , '9422882' , '9422871'
, '9422886' , '9422879' , '9422877' , '9629697' , '9570338' ,
'9570343' , '9570339' , '9570342' , '9570340' , '9570341' , '9607675'
, '9607674' , '9607676' , '9607673' , '9607679' , '9607677' ,
'9607678' , '9620277' , '9620278' , '10166739' , '10160168' ,
'10160169' , '10160166' , '10160167' , '10160170' , '9732345' ,
'10041386')) and (attmtslog0_.AT_DATE in ('06-APR-11' , '21-OCT-10' ,
'07-APR-11' , '07-APR-11' , '07-APR-11' , '18-APR-11' , '15-APR-11' ,
'18-MAR-11' , '21-APR-11' , '15-APR-11' , '15-APR-11' , '18-MAR-11' ,
'18-MAR-11' , '15-APR-11' , '15-APR-11' , '18-MAR-11' , '15-APR-11' ,
'15-APR-11' , '15-APR-11' , '18-MAR-11' , '15-APR-11' , '15-APR-11' ,
'15-APR-11' , '28-APR-11' , '25-APR-11' , '25-APR-11' , '25-APR-11' ,
'25-APR-11' , '25-APR-11' , '25-APR-11' , '26-APR-11' , '26-APR-11' ,
'26-APR-11' , '26-APR-11' , '26-APR-11' , '26-APR-11' , '26-APR-11' ,
'27-APR-11' , '27-APR-11' , '03-JUN-11' , '01-JUN-11' , '01-JUN-11' ,
'01-JUN-11' , '01-JUN-11' , '01-JUN-11' , '03-MAY-11' , '25-MAY-11'))
RATIONALE: SQL statement with SQL_ID "849twcr87pk8z" was executed 1
times and had an average elapsed time of 633 seconds.
RATIONALE: Average CPU used per execution was 633 seconds.
FINDING 8: 7.4% impact (2255 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, 7.4% benefit (2255 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. (22% impact [6689 seconds])
SYMPTOM: Wait class "User I/O" was consuming significant database
time. (57% impact [17471 seconds])
FINDING 9: 6.6% impact (2006 seconds)
-------------------------------------
Wait class "Other" was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Database latches in the "Other" wait class were not consuming
significant database time.
FINDING 10: 2.9% impact (878 seconds)
-------------------------------------
Wait event "Streams AQ: qmn coordinator waiting for slave to start" in wait
class "Other" was consuming significant database time.
RECOMMENDATION 1: Application Analysis, 2.9% benefit (878 seconds)
ACTION: Investigate the cause for high "Streams AQ: qmn coordinator
waiting for slave to start" waits. Refer to Oracle's "Database
Reference" for the description of this wait event.
RECOMMENDATION 2: Application Analysis, 2.9% benefit (878 seconds)
ACTION: Investigate the cause for high "Streams AQ: qmn coordinator
waiting for slave to start" waits in Service "SYS$BACKGROUND".
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Other" was consuming significant database time.
(6.6% impact [2006 seconds])
INFO: Database latches in the "Other" wait class were not consuming
significant database time.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" 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 19% 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.