RE: db_file_sequential_reads takes over 7 seconds per seq# change jump for single block read.
Date: Wed, 2 Mar 2016 08:26:11 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282C5A9A_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jithin Sarath [jithinsarath_at_gmail.com] Sent: 02 March 2016 07:18
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 02 2016 - 09:26:11 CET