Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 21495 invoked from network); 6 Dec 2007 20:06:30 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Dec 2007 20:06:30 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6ECB57D4447; Thu, 6 Dec 2007 21:06:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10004-07; Thu, 6 Dec 2007 21:06:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D8C687D4417; Thu, 6 Dec 2007 21:06:29 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 06 Dec 2007 20:19:34 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E1AD47D43BD for ; Thu, 6 Dec 2007 20:19:33 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00606-06 for ; Thu, 6 Dec 2007 20:19:33 -0500 (EST) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.242]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 82C717D3955 for ; Thu, 6 Dec 2007 20:19:33 -0500 (EST) Received: by an-out-0708.google.com with SMTP id b36so217463ana for ; Thu, 06 Dec 2007 17:19:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Mjcvp3Z7q8mrDDlRbicGcm9QNj1QWuR9n/T5H1fpVFY=; b=EZ3tJQKmWuNnbZMJxlEJ79lOAgS7YFEgZCmqXleNwfcs0HV8Qe5fmNnvPiq31ppAG0gkldSzd2I/D8ZXiIvnKwaUQoHEatFOT9337uq/suIkkdZMAmIS/ZbzXAcy/WvXdcqNprtz65DFwUVht4UMdRw3+yzfeL/ugUnL2te5ygA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=gAM/lXD2d3RQf9tKKw+rX4BjVJS6s8j1F7nADd6nSPeFr6k+2cL4nvLzgJGvQLnLqz8xE9AJBDvdUyG5MFdDZJ6LTfy6dDhLRQvDznHj2LCLOVvfEMPhGC9ddrQgUa/opdwgOMHsuzsvuuWHVIp0nyZMys1ssJTwJEkk6ouopzU= Received: by 10.100.112.6 with SMTP id k6mr656788anc.1196990365642; Thu, 06 Dec 2007 17:19:25 -0800 (PST) Received: by 10.35.119.5 with HTTP; Thu, 6 Dec 2007 17:19:25 -0800 (PST) Message-ID: <74f79c6b0712061719s451f8e05oe8226482d1de7dae@mail.gmail.com> Date: Thu, 6 Dec 2007 20:19:25 -0500 From: "Finn Jorgensen" To: ronnie_doggart@lagan.com Subject: Re: Help with SQL Tuning Cc: oracle-l@freelists.org In-Reply-To: <38EF9F340B22654AA2B30DC97369F0F70A302C3A@tempo.lagan.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14023_9394989.1196990365614" References: <38EF9F340B22654AA2B30DC97369F0F70A302C3A@tempo.lagan.com> X-archive-position: 3769 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: finn.oracledba@gmail.com Precedence: normal Reply-to: finn.oracledba@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_14023_9394989.1196990365614 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 AS REFERENCE, > 0 AS TYPE, > -- [DESCRIPTION] > ENQUIRY.TITLE AS LINE1, > 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 > LEFT OUTER JOIN LGNCC_ENQUIRYRELATION REL ON 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 > > > ------=_Part_14023_9394989.1196990365614 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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@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 AS REFERENCE,
 0 AS TYPE,
-- [DESCRIPTION]
 ENQUIRY.TITLE AS LINE1,
 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
 LEFT OUTER JOIN LGNCC_ENQUIRYRELATION REL ON 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



------=_Part_14023_9394989.1196990365614-- -- http://www.freelists.org/webpage/oracle-l