Re: read by other session
Date: Thu, 03 Dec 2009 06:49:25 -0700
Message-ID: <4B17C1E5.2000008_at_optimaldba.com>
Read by other session indicates that two or more sessions need data that is in the same block. Queries could be running different parameters, but the data to be read may be in the same block.
I was recently faced with a similar scenario. The development team had decided that if 5 concurrent reports were good then 20 were 4x as good. Of course, the end to end performance tanked so they contacted me. After explaining the issue, they reduced the concurrency setting to 5 and saw the performance go back to acceptable. We also identified some tuning opportunities with the sql that would reduce the amount of data they had to retrieve.
The solution is to reduce the data contention, either by scaling back the concurrent access or tuning the query.
Dba DBA wrote:
> My interpretation on this is that multiple sessions are trying to read
> the same block. Oracle can only let 1 person read the same block and
> makes the other sessions wait. Is this correct?
>
> We have the same queries that regularly run in concurrency. now we are
> getting performance problems with a wait of "read by other sessions'.
> My take on it is that now multiple users are running the same query
> with similiar parameters. Where in the past they may have run the same
> query, but with different parameters so we did not get read by other
> session.
>
> when I run the query in sqlplus it returns quickly with minimal read
> by other session waits, but users in the application frequently have
> to wait 45 seconds. My take on this is that at the moment I run the
> query, i am not querying the exact same blocks as other people.
>
> Is that how I interpret read by other sessions? Yes I have traced
> sessions and AWR, but now I need to interpret my results.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 03 2009 - 07:49:25 CST