This means you spend some time waiting for a db file sequential read and then
spend 6 or 7 seconds on CPU.
The column seconds_in_wait would be better named "second since this wait
started" - you need to look at STATE and WAIT_TIME to find out if the session
is still waiting, and how long it waited for if it isn't.
Better still, look at v$session_wait_history to get information on the last 10
waits for the session; even better - if you are licensed to view
v$active_session_history, check the per-second history for the session and
you'll probably get 6 or 7 explcit "ON CPU" rows for the time you've
interpreted as wait time.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jithin Sarath [jithinsarath@xxxxxxxxx]
Sent: 02 March 2016 07:18
To: oracle-l@xxxxxxxxxxxxx
Subject: db_file_sequential_reads takes over 7 seconds per seq# change jump for
single block read.
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
