I count 4 Nested Loops and only 1 hash join. I'm guessing one of the outer loops is returning a lot of rows and therefore the inner loops are executed many times, each causing a lot of index reads. Finn On Dec 6, 2007 8:35 AM, Ronnie Doggart <ronnie_doggart@xxxxxxxxx> wrote: > Hi All, > > I have a problem with a customer database and performance. I have ran > statspack and identified the worst performing SQL statement, but have run > out of ideas on how to get the SQL to perform better. The query is from an > application and so cannot be changed. Why are we doing so many 'DB File > Sequential Reads' when the hash join is doing full table accesses ? > > TKprof output: > > > select * from lgncc_commoncaseview where > clientid = :b1 and > clienttype = :b2 and (1=1) > union > select * from lgncc_commoncaseview where > xref1 = :b3 and > objecttype = :b4 and (1=1) > > > > call count cpu elapsed disk query current > rows > > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > > Parse 1 0.00 0.00 0 0 0 0 > Execute 1 0.00 0.00 0 0 0 0 > Fetch 2 4.49 34.89 1787 21664 0 2 > > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > > total 4 4.49 34.90 1787 21664 0 2 > > > > Misses in library cache during parse: 0 > > Optimizer goal: CHOOSE > > Parsing user id: 64 > > > > Rows Row Source Operation > > ------- --------------------------------------------------- > > 2 SORT UNIQUE > > 2 UNION-ALL > > 1 NESTED LOOPS > > 1 HASH JOIN > > 4 TABLE ACCESS BY INDEX ROWID OBJ#(35867) > > 4 INDEX RANGE SCAN OBJ#(38739) (object id 38739) > > 117901 HASH JOIN OUTER > > 117901 TABLE ACCESS FULL OBJ#(35878) (lgncc_enquiry) > > 1475502 TABLE ACCESS FULL OBJ#(35880) (lgncc_enquiryrelation) > > 1 TABLE ACCESS BY INDEX ROWID OBJ#(35873) > > 1 INDEX UNIQUE SCAN OBJ#(35874) (object id 35874) > > 1 NESTED LOOPS OUTER > > 1 NESTED LOOPS OUTER > > 1 NESTED LOOPS > > 1 TABLE ACCESS BY INDEX ROWID OBJ#(35878) > > 1 INDEX RANGE SCAN OBJ#(38738) (object id 38738) > > 1 TABLE ACCESS BY INDEX ROWID OBJ#(35873) > > 1 INDEX UNIQUE SCAN OBJ#(35874) (object id 35874) > > 1 INDEX RANGE SCAN OBJ#(38516) (object id 38516) > > 1 TABLE ACCESS BY INDEX ROWID OBJ#(35867) > > 1 INDEX UNIQUE SCAN OBJ#(35868) (object id 35868) > > > > > > Elapsed times include waiting on following events: > > Event waited on Times Max. Wait Total Waited > > ---------------------------------------- Waited ---------- ------------ > > SQL*Net message to client 2 0.00 0.00 > > db file sequential read 1382 0.17 16.42 > > db file scattered read 175 0.10 3.45 > > latch free 6 0.01 0.01 > > buffer busy waits 1137 0.08 11.15 > > SQL*Net more data to client 1 0.00 0.00 > > SQL*Net message from client 2 6.89 6.89 > > > > > > > > Lgncc_CommonCaseview Definition > > SELECT > -- [ID] > ENQUIRY.ID <http://enquiry.id/> AS REFERENCE, > 0 AS TYPE, > -- [DESCRIPTION] > ENQUIRY.TITLE AS LINE1, > TYPE.NAME <http://type.name/> AS LINE2, > -- [CASE] > ENQUIRY.CASEID AS CASEID, > ENQUIRY.CASEREF AS RELATEDCASE, > ENQUIRY.ENQUIRYTYPE AS ENQUIRYTYPE, > ENQUIRY.OBJECTTYPE AS OBJECTTYPE, > ENQUIRY.XREF1 AS XREF1, > ENQUIRY.XREF2 AS XREF2, > ENQUIRY.XREF3 AS XREF3, > ENQUIRY.OBJECTDESC AS OBJECTDESC, > -- [INTERACTION] > INT.CLIENTTYPE AS CLIENTTYPE, > INT.CLIENTID AS CLIENTID, > INT.CLIENTNAME AS CLIENTNAME, > INT.LOGID AS INTERACTIONID, > INT.INTREF AS INTERACTIONREF, > INT.VERIFIED AS INTERACTIONVERIFIED, > nvl(INT.INITCHANNEL, -1) AS INTERACTIONCHANNEL, > INT.REFERENCE AS INTERACTIONREFERENCE, > INT.STARTTIME AS INTERACTIONDATE, > -- [STATUS] > NULL AS TARGETDATE, > ENQUIRY.STATUS AS STATUS, > -- [AUDIT] > ENQUIRY.CREATIONDATE AS CREATIONDATE, > ENQUIRY.SOURCEID AS CREATEDBY, > ENQUIRY.CREATIONDATE AS MODIFIEDDATE, > NULL AS MODIFIEDBY > FROM > LGNCC_ENQUIRY ENQUIRY > INNER JOIN LGNCC_ENQUIRYTYPE TYPE ON ENQUIRY.ENQUIRYTYPE = > TYPE.ID<http://type.id/> > LEFT OUTER JOIN LGNCC_ENQUIRYRELATION REL ON ENQUIRY.ID<http://enquiry.id/>= > REL.ENQUIRYID AND REL.RELATION = 1 > LEFT OUTER JOIN LGNCC_INTLOGHDR INT ON INT.LOGID = REL.INTERACTIONID > WHERE ENQUIRY.CASEREF IS NOT NULL AND ENQUIRY.DELETEDDATE IS NULL > > > > Ronnie Doggart > > -- > //www.freelists.org/webpage/oracle-l > > >