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