Event 'db file seq. read' stuck for hours on the same file#/block# ?
Spendius
We have a procedure that makes the sessions that call it hang for
hours, and I see in V$SESSION_WAIT the event 'db file sequential
read' showing the same file number and block number ALL THE TIME
it keeps hanging (in fact I never waited until it ended but always
killed the sessions).
Is it a normal behaviour ? It's all the more peculiar as the file and
block id's point to an index block, created on a empty table !
Nothing moves, everything seems to be stalled to death. The fields
p1/p2/p3text and p1/p2/p3 keep showing me the same file#, block# and
blocks values: 12, 37173, 1...
Any idea about what's going on please ?
(Oracle 8.1.7.4 64-bit on Solaris 8)
Thanks.
IANAL_VISTA
news:aba30b75.05022...@posting.google.com:
It might be sitting on an enqueue wait
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
DA Morgan
What is the operating system and storage hardware?
Check i/o statistics
Any logical volume manager's in play?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
John Darrah
your query has been doing logical reads for hours? Check v$sess_io if
you see consistent_gets going up, this is probably what you are
experiencing.
Joel Garry
smon topping out your cpu?
(Sometimes killed sessions don't get cleaned up until you bounce.
Sometimes they keep locks.)
jg
--
@home.com is bogus.
"Well, I'm just a simian who writes conservative opinions. And there
are plenty of other simians who share my political predilection who can
ably take my place. " - Joseph Perkins, black conservative.
Haximus
news:aba30b75.05022...@posting.google.com...
Just a thought... latch contention? 'db file seq. read' is for control file
and datafile header access, a latch must be required to access these areas,
the process might be requesting one in willing-to-wait mode, not getting it
and retrying... retrying... etc
Sybrand Bakker
>Just a thought... latch contention? 'db file seq. read'
Could you please look up the definition of 'db file sequential read'
It has exactly *nothing* to do with control file reads, and
consequently your answer is fully incorrect and misleading.
--
Sybrand Bakker, Senior Oracle DBA
Haximus
news:mr88219dle3pb9lud...@4ax.com...
> On Tue, 01 Mar 2005 05:54:09 GMT, "Haximus" <e...@t.me> wrote:
>
>>Just a thought... latch contention? 'db file seq. read'
>
> Could you please look up the definition of 'db file sequential read'
> It has exactly *nothing* to do with control file reads, and
> consequently your answer is fully incorrect and misleading.
"The session waits while a sequential read from the database is performed.
This event is also used for rebuilding the controlfile, dumping datafile
headers, and getting the database file headers."
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/apa5.htm
All I was suggesting is to check if the session is hung on a latch request:
select * from v$session_wait sw, v$latchname ln where sw.p2=ln.latch# where
sw.sid=###
Are all you DBA fixtures here such ogres? ;)
Spendius
>
> Just a thought... latch contention? 'db file seq. read' is for control file
> and datafile header access, a latch must be required to access these areas,
> the process might be requesting one in willing-to-wait mode, not getting it
> and retrying... retrying... etc
or 'latch free' on the same number (106 = library cache) with the
number of tries staying at 0 (ZERO), for hours too, until I kill
the session. But 9 times/10 we get the db file sequential read wait.
I've dbverified the file returned by this event, everything's fine,
I've checked V$SESS_IO, from the moment the session stalls the
values in this view no more change, I've launched the first SQL
on V$LOCK someone gave me in the 1rst reply: I get no row (I've
run anyway ALL my scripts related to locking, including catblock.sql
to create all these relevant views, but it never returns me anything
helpful). It *really* looks as if the session suddenly gets suspended,
everything looks frozen.
A 'tail -f' of 10046 trace file always stops at the same line:
a SELECT FOR UPDATE statement. I'm beginning to suspect the data
in the tables involved must cause this procedure to conk out
(because the same module exists on 22 different databases -they
are replicated to the same master- but it only fails on this one)...
Haximus
Excessive amount of wait time on library cache latches? This can happen
when massive amounts of SQL parsing takes place, causes lots of library
cache latch demand. What does the entire SELECT look like.... are there
literals embedded in it that would cause it to be reparsed every time,
instead of reused from the library cache?
DA Morgan
> Just a thought... latch contention? 'db file seq. read' is for control file
> and datafile header access
Is your advice based on ignorance or intended to do harm?
Source:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/instance_tune.htm#15959
db file sequential read
This event signifies that the user process is reading a buffer into the
SGA buffer cache and is waiting for a physical I/O call to return. A
sequential read is a single-block read.
Haximus
> Haximus wrote:
>
>> Just a thought... latch contention? 'db file seq. read' is for control
>> file and datafile header access
>
> Is your advice based on ignorance or intended to do harm?
>
> Source:
>
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/instance_tune.htm#15959
>
> db file sequential read
> This event signifies that the user process is reading a buffer into the
> SGA buffer cache and is waiting for a physical I/O call to return. A
> sequential read is a single-block read.
Perhaps you should look at the 8.1.7 docs which state "This event is also
used for rebuilding the controlfile, dumping datafile headers, and getting
the database file headers." Now, obviously this isn't the case as the
poster has already identified that the read appears stuck on an index
block.
Anywho... this is beside the point I wanted to make, which is something is
obviously blocking the read from completing... like for example another
session accessing to the same block, in which case could result in a
'buffer busy wait' hang, that sort of thing, or latch contention.... get
the idea?
Just a tip: quoting from manuals doesn't help anyone when obviously you do
not seem to understand the underlying processes involved.
Haximus
Just another thought... check if another session is accessing the same file#
and block#... could be a possible source of blocking.
Sybrand Bakker
>Anywho... this is beside the point I wanted to make, which is something is
>obviously blocking the read from completing... like for example another
>session accessing to the same block, in which case could result in a
>'buffer busy wait' hang, that sort of thing, or latch contention.... get
>the idea?
You are very talented at making a mess of things, aren't you?
What has 'buffer busy wait' to do with 'latch contention', apart from
nothing?
>
>Just a tip: quoting from manuals doesn't help anyone when obviously you do
>not seem to understand the underlying processes involved.
If you would only follow up your own advice and try to understand the
processes involved....
Sybrand Bakker
>Excessive amount of wait time on library cache latches? This can happen
>when massive amounts of SQL parsing takes place, causes lots of library
>cache latch demand. What does the entire SELECT look like.... are there
>literals embedded in it that would cause it to be reparsed every time,
>instead of reused from the library cache?
>
Please stop posting this mess.
If you submit an identical statement with identical literals for the
second time it has the same hash value and it is only soft-parsed.
I haven't seen such utter rubbish in this group for ages.
Sybrand Bakker
>All I was suggesting is to check if the session is hung on a latch request:
>
>select * from v$session_wait sw, v$latchname ln where sw.p2=ln.latch# where
>sw.sid=###
>
>Are all you DBA fixtures here such ogres? ;)
>
latch requests are latch requests, and have nothing to do with
sequential read.
You are by far the worst source of misinformation here and and would
advise everyone not to follow up on your postings. You really don't
know what you are talking about.
I would consider it unprofessional to let 'advise' like this passed
without warning.
Haximus
If you're going to condemn advice, at least provide an explanation of why it
is incorrect but don't condemn a guy for trying to help. At least not
unless you have a better suggestion to identify/resolve the problem, which
so far has been (blank).
Haximus
Unless the statement is using concatenation operators in the expressions and
the literals are not identical, in which case it will require re-parsing.
That's why I asked the poster to show the statement here. And your
offering/suggestion/fix was what... again?
Haximus
> On Tue, 01 Mar 2005 19:58:39 GMT, Haximus <e...@t.me> wrote:
>
>>Anywho... this is beside the point I wanted to make, which is something is
>>obviously blocking the read from completing... like for example another
>>session accessing to the same block, in which case could result in a
>>'buffer busy wait' hang, that sort of thing, or latch contention.... get
>>the idea?
>
> You are very talented at making a mess of things, aren't you?
> What has 'buffer busy wait' to do with 'latch contention', apart from
> nothing?
For example, two concurrent processes trying to read the same data block
into buffer ... one process will block the other until the read has been
completed, resulting in a 'buffer busy wait.' Anyhow, if you go back and
read what I wrote you'll see that your reading comprehension needs a little
work.
>>
>>Just a tip: quoting from manuals doesn't help anyone when obviously you do
>>not seem to understand the underlying processes involved.
>
> If you would only follow up your own advice and try to understand the
> processes involved....
Yes, and again, your offering/fix/solution/advice was what?
Joel Garry
No, we also have trolls here.
jg
--
@home.com is bogus.
"Joel, that is beneath you." - Howard Stern
DA Morgan
And if you remember who posted that rubbish ... it is the same person
posting under a different name. I was so hoping he'd learn SQL Server.
DA Morgan
The onus is not on us to demonstrate that you have been repeatedly
posted advice that wanders blindly between dead-wrong and outright
dangerous.
Pointing out to others that the bridge is out is sufficient for them to
avoid driving off the cliff.
Haximus
> posted advice that wanders blindly between dead-wrong and outright
> dangerous.
>
> Pointing out to others that the bridge is out is sufficient for them to
> avoid driving off the cliff.
"have been repeatedly posted advice..." talk about "ALL YOUR BASE ARE
BELONG TO US" English skills. Time to drop the Oracle stuff and go for
some remedial language studies, or lose the bad translator.
Anyhow, I'm not going anywhere no matter how much you gobble or crow, so
you'll just have to filter or ignore me. There's no hierarchy here and
I don't have to hold my hand up for permission to speak, especially not
from a couple of guys who were too short for the police academy.
NetComrade
But I had a case like this today, where PL/SQL was stuck in an
infinite loop doing select.. the seconds did seem to accumulate
instead of being 0 all the time (which is either a bug, or oracle just
doesn't care to differentiate between different calls to the same
statement).
-A
On 28 Feb 2005 07:53:46 -0800, spen...@muchomail.com (Spendius)
wrote:
.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email
sri...@hotmail.com
truss or ptrace and see what is is doing.
regards
srivenu