db_file_sequential_reads takes over 7 seconds per seq# change jump for single block read.

I support databases for Oracle E-Business Suite, and in one of our production instances, a job is long running. This in itself is not new, but what perplexes me is that when I look at v$session_waits for this session, I see that the seconds_in_wait climbs to 6 or 7 per db_file_sequential_read. Once it reached this situation, the seq# will increment slowly as opposed to quick jumps in 1000s.

If I flush the buffer_cache, the processing speeds up and after a while it hit the slow db_file_sequential_reads again. Other DB session do not show this problem. I tried to dump the blocks that are referenced when it is slow, and it points to one of the tow indexes. When I query the table directly using the index, the results are quick. The execution plan doesn't show any "outliers" and the expected execution is < 2 sections.

How do I explain this?

Thanks in advance, Jithin

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