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

janik_at_pobox.sk (Jan) wrote in message news:<81511301.0410120014.37e8c525_at_posting.google.com>...
> 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

I'm aware of these issues - they are primarily due to the code that we have that generates these queries - it is extremely complex and occasionally produces "odd" examples like you mention. However, making the changes you mention (removing outer join markers, getting rid of fact on subquery) doesn't change the performance of the queries, so it's not worth the additional complexity to remove these corner cases.

Other issues on the query that might look "weird" (e.g., the +1-1 next to ExposeTime) are done to influence the optimizer towards plans we know are optimal - in that case, we want to force the optimizer not to choose the B-Tree index that exists on the ExposeTime column (which is sometimes does) and use a bitmapped plan instead - this works more reliably than using index-related hints (which oftentimes don't do anything at all for queries like this). For queries that constrain on very few "Id" columns, we reverse pattern (we put +1-1 on the Id columns and leave the ExposeTime column alone), because then it becomes advantages to reverse-traverse the B-Tree index compared to a bitmap merge.

Anyway, note that the problem that I'm having does not have to do with i/o - the i/o statistics in these queries are identical to similar queries that perform in under .1 second. The real issue appears to be one that has to do with resource contention, as I mentioned in the other reply, as the "slow" statements in question run perfectly well sometimes, and poorly at other times.

-Steve Received on Tue Oct 12 2004 - 10:50:27 CDT