All, I have a strange problem and need guidance.
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
