Re: read by other session events
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: lyallbarbour@xxxxxxxxxxxxxxx
- Date: Thu, 11 Nov 2010 12:32:23 -0600
Lyall,
I believe these are the renamed event that covers the situation that one
type of the old "buffer busy waits" waits covered.
When your session wants a database block that's not in the database buffer
cache, it would normally issue an OS read call to get it (pread, readv,
...). But, if some other process has already executed a not-yet-complete
read call to obtain that same block that you're wanting, you don't want
Oracle to issue another read call for the block. So your session will
register a "wait" for the other session's read call to complete with a "read
by other session" event. When the other session's read call is complete, the
block will be in the buffer cache for your session to use.
The best way to eliminate such events from your response time is to
eliminate the need for your SQL to need so manipulate blocks, for instance
by optimizing your SQL, as another respondent has already mentioned... You
might be interested in the E2SN virtual
seminar<http://tech.e2sn.com/virtual-conferences>happening next week,
in which Tanel Poder, Kerry Osborne, and Jonathan Lewis
will explain techniques for doing just that.
Cary Millsap
Method R Corporation
http://method-r.com
On Wed, Nov 10, 2010 at 4:19 PM, <lyallbarbour@xxxxxxxxxxxxxxx> wrote:
>
> This morning we had our production 10.2.0.4 database come to practically
> a grinding halt with lots of I/O requests in Grid. running ADDM and ASH
> reports, there were, at first, problems with Segments that needed to be
> tuned. Then the next ADDM reports talked about "read by other session"
> events being 100% the major problem.
> After looking through Oracle Support and more and more help desk calls
> coming in, we decided to bounce the database. This, of course, solved our
> problem.
>
> I don't really understand what "read by other session" events are, so if
> someone can clarify that, that would be helpful. From what i read, they are
> memory waits for the data blocks in the buffer cache. Different logins
> requesting the same blocks in memory... i think. Even if that's right, i
> guess i still don't really understand what that means.
>
> The other thing that i'd really like to know, if we ever encounter this
> again since we don't really know how it happened, what other options do i
> have instead of bouncing the database? Flushing the buffer cache and SGA?
> Killing peoples session that i see in ASH reports that have these "read by
> other session" wait events?
>
> Thanks for any replies!
> Lyall
>
Other related posts:
