Re: Help with SQL Tuning

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_at_lagan.com> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l