RE: Help with SQL Tuning
- From: "Taylor, Chris David" <Chris.Taylor@xxxxxxxxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Dec 2007 07:45:48 -0600
Well there are a couple of things here if I'm not mistaken.
1.) Full table scans almost always use sequential file reads versus db file
scattered reads
2.) Looks like lgncc_commoncaseview is a view. Look at the text that makes up
the view and tune that. If you need more help/suggestions you'll need to
supply the text of the view.
Thanks,
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ronnie Doggart
Sent: Thursday, December 06, 2007 7:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Help with SQL Tuning
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
