Hi list ,
I need your help in tuning this sql.
Oracle 10g,Windows 2008 64 bit running on virtual machine.
SELECT t0.RecID, t0.PrtID, t0.RecOrdDt, t0.RecOrdID, t0.RecExtOrdID, t0.RecPocID, t0.PriID, t0.CasID, t0.CasNo, t0.CasClass, t0.CasPocID, t0.CasAdmit, t0.CasNotValid, t0.PatName, t0.PatFName, t0.PatBName, t0.PatBDate, t0.PatSex, t0.PatNotValid, t0.RstID, t0.RstState, t0.RstMeaningI18N, t0.PrtType, t0.PrtSubType, t0.RpxType, t0.RpxPrio, t0.PerID, t0.PerName, t0.PerFName, t0.PerTitle, t0.PerType, t0.AgnExtID
FROM
IXSERV.XsvRecPerInfoV t0
WHERE
((((t0.AgnExtID = '1728454694') AND (t0.PrtID IN (72, 165)))
AND (t0.RpxType IN (1, 2, 6, 3, 5)))
AND (t0.RstState IN (202, 208, 210, 203, 206)))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 1999 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 5.39 57.42 45408 53080 0 45
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 5.43 57.46 45408 55079 0 45
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
45 NESTED LOOPS (cr=53080 pr=45408 pw=0 time=2611987 us)
45 NESTED LOOPS (cr=52941 pr=45355 pw=0 time=46145568 us)
45 HASH JOIN (cr=52802 pr=45314 pw=0 time=45407144 us)
1 TABLE ACCESS BY INDEX ROWID PERPERSON (cr=3 pr=6 pw=0 time=40510 us)
1 INDEX RANGE SCAN PERIDXAGNEXTID (cr=2 pr=5 pw=0 time=22311 us)(object id 56817)
23623 TABLE ACCESS BY INDEX ROWID XSVRECPERXREF (cr=52799 pr=45308 pw=0 time=50046345 us)
33893 NESTED LOOPS (cr=47543 pr=42261 pw=0 time=73003406 us)
5139 HASH JOIN (cr=37184 pr=40103 pw=0 time=56090695 us)
5 TABLE ACCESS FULL XMFREPORTSTATE (cr=7 pr=6 pw=0 time=22565 us)
66900 TABLE ACCESS BY INDEX ROWID XSVPATIENTRECORD (cr=37177 pr=40097 pw=0 time=39887547 us)
66903 NESTED LOOPS (cr=146 pr=141 pw=0 time=8095232 us)
2 INLIST ITERATOR (cr=4 pr=3 pw=0 time=37200 us)
2 TABLE ACCESS BY INDEX ROWID XMFPATIENTRECORDTYPE (cr=4 pr=3 pw=0 time=37198 us)
2 INDEX UNIQUE SCAN PKPRT (cr=2 pr=1 pw=0 time=26746 us)(object id 56143)
66900 INDEX RANGE SCAN RECIDXPRTID (cr=142 pr=138 pw=0 time=51513 us)(object id 56410)
28753 INDEX RANGE SCAN RPXIDXRECID (cr=10359 pr=2158 pw=0 time=4518986 us)(object id 56453)
45 TABLE ACCESS BY INDEX ROWID ADTCASE (cr=139 pr=41 pw=0 time=423483 us)
45 INDEX UNIQUE SCAN PK_ADTCASE (cr=94 pr=23 pw=0 time=184866 us)(object id 55295)
45 TABLE ACCESS BY INDEX ROWID ADTPATIENT (cr=139 pr=53 pw=0 time=413067 us)
45 INDEX UNIQUE SCAN PK_ADTPATIENT (cr=94 pr=21 pw=0 time=195583 us)(object id 55361)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 114.42 128.67
SQL*Net more data to client 1 0.00 0.00
db file sequential read 27969 0.13 51.57
db file scattered read 3271 0.05 4.61
********************************************************************************
Some lines from trace fils :
WAIT #1: nam='db file sequential read' ela= 12778 file#=4 block#=3726132 blocks=1 obj#=56817 tim=249799016372
WAIT #1: nam='db file scattered read' ela= 9271 file#=4 block#=3726133 blocks=4 obj#=56817 tim=249799025761
WAIT #1: nam='db file sequential read' ela= 18141 file#=4 block#=587487 blocks=1 obj#=55853 tim=249799043945
WAIT #1: nam='db file sequential read' ela= 18304 file#=4 block#=752595 blocks=1 obj#=56219 tim=249799062471
and the view XsvRecPerInfoV is :
SELECT d.RecID, d.PrtID, d.RecOrdDt, d.RecOrdID, d.RecExtOrdID, d.PocID, d.PriID, c.CasID, c. CasNo, c.CasClass, c.PocID, c.CasAdmit, c.CasNotValid, p.PatName, p.PatFName, p.PatBName, p.PatBDate, p.PatSex, p.PatNotValid, r.RstID, r.RstState, r.RstMeaningI18N, prt.PrtType, prt.PrtSubType, x.RpxType, x.RpxPrio, per.PerID, per.PerName, per.PerFName, per.PerTitle, per.PerType, per.AgnExtID
FROM XsvPatientRecord d,
AdtCase c,
AdtPatient p,
XmfReportState r,
XmfPatientRecordType prt,
XsvRecPerXRef x,
PerPerson per
WHERE
d.RecID = x.RecID
AND d.RstID = r.RstID
AND d.PrtID = prt.PrtID
AND d.CasID = c.CasID
AND x.PerID = per.PerID
AND c.PatID = p.PatID
i have two questions :
-1 The problem in tables XSVPATIENTRECORD and XSVRECPERXREF so how can i speed this query either by rewriting it or indexs?
-2 is my system i-o is slow ?
Best wishes
Edited by: welo on Oct 6, 2010 5:05 AM