Re: Tuning question - low cpu_time, high elapsed_time - what gives?

you should rewrite some part of the query (tunning method in this case = application design) to decrease unnecessary IO:

1.)

what is the purpose of join with DUAL in

  AND fact.LotId IN(SELECT l.LotId

                     FROM dual fact  -- ???
                        , Lot l
                    WHERE l.DeviceId = 6552
                      AND l.TechnologyId = 3845)

Note that you should not do an extra joins if you want to reduce IO

2.) in one part of the WHERE you are asking for:

    AND et.ExposureToolModelId = etm.ExposureToolModelId(+)

• what means if no rows exists in etm for given criteria then put null for missing data

   but in the next part you are filtering it out:

   AND etm.ExposureToolType = 'scanner'

• remove all null and not 'scanner' from etm

   This means you are removing also data which you got by previous OUTER JOIN You have more such mistakes (point 2.) then fix these first.

Jan Received on Tue Oct 12 2004 - 03:14:06 CDT