RE: Help with SQL Tuning

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_at_ingrambarge.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ronnie Doggart Sent: Thursday, December 06, 2007 7:35 AM To: oracle-l_at_freelists.org
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

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




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