Re: SQL tuning nightmare - db file sequential reads

Jonathan sir, Yong, Michel, Steve and all other helpful posters,

I am sorry for taking such a long time in getting back. I have been trying to recreate this scenario ever since my last post, so that I may be able to run trace on it, but I have not been able to do so. The stats were gathered again on that database and original stats are lost forever, and the scenario is just not appearing with the new stats.

Anyway, I have been able to tune the query using Hash Anti-Join as many of you suggested.

I think I understand now that the 800+ seconds shown in V$SESSION was not spent on the 'db file sequential read' event, but on something else which was consuming CPU like hell. This would probably be due to large number of LIOs as (I have read about this in 'Why you should focus on LIOs instead of PIOs). But again I wouldn't know for sure until I am able to take the 10046 trace.

If I do manage to replicate this scenario and take the trace I will definitely post the findings.

Really appreciate the helpful responses from you all. Thanks very much.

Charu. Received on Mon Jul 17 2006 - 07:46:37 CDT