Home>Question Details


Thomas -- Thanks for the question regarding "Confusion surrounding "buffer busy waits" and "read by other session" wait events", version 10.2.0.4

Submitted on 6-Aug-2009 20:15 Central time zone
Tom's latest followup | Bookmark | Bottom
Last updated 30-Apr-2012 8:32

You Asked

Tom -

I'm investigating a relatively minor but attention-getting issue that occurs from time to time around my workplace. I observe a few dozen identical queries (select only - no DML) generated by an Oracle Financials concurrent program being executed nearly simultaneously against my EE database.

Alone, each query is rather harmless and simple and returns just a handful of rows. But as new occurrences of the query are spawned (a new one comes about every 10 to 15 seconds or so), my "read by other session" wait event as measured at the system level skyrockets, and it attracts some attention on my Oracle Grid Control graphs for the five or six minutes that span all of these troublesome query executions.

It's interesting to note that each returned row for each of these several dozen simultaneously executing queries is consuming about 4000 LIO's on average. I'm hardly surprised that "read by other session" is very high since blocks must be flying in and out of the buffer cache like mad. I do see both FTS and index access paths in the execution plan.

Shortly following this little explosion of "read by other session", I observe the following state of the two events that interest me: 


column event format a22
select event,
       total_waits,
       average_wait,
       time_waited_micro,
       wait_class
from v$system_event
where event in ('read by other session','buffer busy waits')
/

EVENT                  TOTAL_WAITS AVERAGE_WAIT TIME_WAITED_MICRO WAIT_CLASS
---------------------- ----------- ------------ ----------------- ------------
buffer busy waits           103500          .12         127965796 Concurrency
read by other session       795497          .49        3918717024 User I/O

2 rows selected.



This is all very interesting, of course, but it invites two very related questions:

1. The definitions of these waits (found at 
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref315
9
) seems to imply that every "read by other session" event is also a "buffer busy waits" event as well. I observe no "buffer busy waits" at all during my short, six-minute window of interest. I'm obviously wrong in my interpretation of the documentation because a chunk of time cannot be counted twice. But could you tell me why the documentation seems misleading?

2. I love wait classes, and I study them whenever I get the chance. To me, the "read by other session" wait event seems to be best at home in the "Concurrency" class instead of "User I/O" since it would have been in the former in earlier versions of Oracle anyway if wait classes had existed there. Do you agree?

Any thoughts?

Tom from Colorado

and we said...

1) A buffer busy wait can be caused by many things - you want to modify the block and need it in current mode, but someone else has it - you wait. Or, you want the block, the block is not in the cache, but you notice someone else is already reading it (read by another session) so you buffer busy wait for it. Among others.

In the past, they were all just buffer busy waits. Current releases (10g and up) are breaking the waits out into finer and finer categories.

In older releases - this would have just be "buffer busy wait" - the doc you pointed me to states that "Prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event"

2) read by other session - if you were not waiting on that, you would be doing the IO yourself and the wait event would be db file sequential read. This is not a concurrency thing - it is not that the other session is preventing you from accessing the block - rather you are patiently waiting for the IO the other session is kindly doing for you to complete. It is really a wait for IO to complete - not a concurrency thing.

This is one reason for breaking the waits out into finer levels of detail - a buffer busy wait in the past might have been a concurrency thing (trying to get in current mode) or an IO thing (waiting for the read made by another session to complete). Now we have that information - before we did not.


Reviews    
5 stars Book   August 7, 2009 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader 
Craig Shallahamer's new book explains these things in great detail (chapter 6 to be specific)

http://resources.orapub.com/Oracle_Performance_Firefighting_Book_p/ff_book.htm

I'm currently reading this book and so far the information is excellent.

Tom, I hope you're OK with me posting this in your site.


Followup   August 7, 2009 - 3pm Central time zone:

not at all, please do
5 stars read by other session   August 13, 2009 - 12pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader from india
Dear Tom

how to fix " read by other session " waits

i found three sessions are running with same sql ( select ) .. each sessions waiting for the above 
wait event in circular manner. so here can we run one by one rather running concurrently three 
sessions ... to avoid the above event .. or is there any other workaround  



thanks in advance

Ali


Followup   August 13, 2009 - 12pm Central time zone:

preload all of the data into the buffer cache.

Do you understand that "read by other session" is really just a "db file sequential read" wait with more detail - you are waiting for physical IO to complete, you are not doing the physical IO but you still must wait for it.

To remove these waits, you have to remove the physical IO in the first place, then no other session could exist to be waiting on.
4 stars Understanding   August 13, 2009 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Aru from NZ
Hi Tom,
After reading about buffer busy waits here, I am getting that only one session/process can access 
or read or write to a buffer in memory. If one process updates a row in the buffer (and makes it 
dirty), he has a latch on the buffer and no other process can even read from the buffer for the 
duration of changing the row. After the update is done the latch is released and other session in 
line can do the read or write etc. and take out a latch of his own.
Is my understanding right or am I climbing the wrong tree as I so often do?
Regards and thank you,
Aru.


Followup   August 24, 2009 - 7am Central time zone:

only one session at a time can have a block in what is known as current mode, and that is the mode the block has to be in in order to modify the bits and bytes on it.

So yes, in order to modify something on a given block you will

a) gain exclusive access to this block to modify it (other sessions that only need to READ the block - a consistent read - won't be bothered, just sessions that want to MODIFY the block)

b) make the modification

c) immediately release the block


the latch (lock) taken in (a) is held for an extremely small amount of time...
5 stars read by otrher session   August 14, 2009 - 9am Central time zone
Bookmark | Bottom | Top
Reviewer: ali from india
Dear Tom ,

Thanks for your useful reply ..

we are using 11i application. three concurrent requests fired same time and they are running same 
SQL statement ...


File #    Block # Reason Code
    ---------- ---------- -----------
            95      54104           1
            95      54104           1
            95      54104           1
            95      54104           1
            95      54104           1
            95      54104           1
            95      54104           1
            95      54104           1
           127     128539           1
            95      54104           1
           101      41249           1
            95      54104           1
           369     102890           1

but file#,block# changed continuosly .. ie. it is touching more datafiles ...  so i could not 
colect all data details

you said in your previous reply  " preload all of the data into the buffer cache. "  will solve the 
issue

could you please hep me " how to preload the data into buffer in this situation


Thanks in advance

Ali


Followup   August 24, 2009 - 7am Central time zone:

my suggestion to preload the buffer cache was sort of "tongue in cheek"

You asked how to 'fix' something that is not something to be fixed

Once you see that 'read by another session' is really "waiting for a physical IO", the only thing you can do is "reduce physical IO"

Of course the block number will be changing - once it is read into the buffer cache and in the cache, you won't have a 'read by another session' wait on it - it was already read, it is already in the cache.


You have to wait for physical IO to complete - the only way to 'reduce that' is

a) reduce the number of physical IO calls you need (query tuning typically, algorithm changing too)

b) make physical IO faster


3 stars read by other session   October 20, 2009 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: vicmaq from Mexico
Hi Tom

After upgrade to 10.2.0.4 (from 10.2.0.3),  the database was hang and the waits events was "read by 
other session" 
without changes in the number of concurrents session and the same user activity before the patch.

I was returned to 10.2.0.3 patchset and the database works fine again.

I reviewed all the logs and configurations and i found nothing. But the performance graphics 
indicate that with 10.2.03 there are no parallel process queries,
and with 10.2.0.4 starts automatically some parallel procesess.

It is possible  that the 10.2.0.4 enables some functionality that starts parallel query processing 
and that derive on concurrency and "read by other session" wait events ?


Followup   October 22, 2009 - 5pm Central time zone:

read by other session used to just be buffer busy wait.

and if it was parallel, you wouldn't have buffer busy waits, we use direct io (bypass the cache). So, it is not parallel (you would see parallel plans, I doubt you do)


If 10.2.0.4 was starting parallel processes and 10.2.0.3 was not - I would say there is a good chance you had different parameter files.

what else changed here, besides the patch.
5 stars   December 2, 2010 - 1pm Central time zone
Bookmark | Bottom | Top
Reviewer: A reader 
This was really good explanation with examples ........


4 stars Writers blocking readers?   December 2, 2010 - 8pm Central time zone
Bookmark | Bottom | Top
Reviewer: BA from Seattle, WA
On August 24, 2009, you made the following comment

"in order to modify something on a given block you will

a) gain exclusive access to this block to modify it (other sessions that only need to READ the 
block - a consistent read - won't be bothered, just sessions that want to MODIFY the block)"

Does this mean that reading sessions should no longer experience buffer busy waits (since the 
introduction of 'read by other session' waits)?

Put differently ...

Is there any way in which a writer on a block could block other sessions that only need to READ the 
block - a consistent read?  Can a writing session trigger a buffer busy wait for those reading 
sessions?


Followup   December 7, 2010 - 9am Central time zone:

reading sessions would not experience a buffer busy wait due to a write - but they can experience buffer busy waits (the cache might be full and dbwr needs to clean it out a bit for example, or read by other session)


The writing session will not block the reader.
5 stars Why do we need higher inittrans if concurrent sessions cannot modify buffers   December 16, 2010 - 11pm Central time zone
Bookmark | Bottom | Top
Reviewer: Haris from Herndon, VA
In your earlier response you stated that at any given time only one process can modify data in 
buffer. It leads me to my question that do we have any value in having higher INITTRANS in the 
following example? 

Lets say we have a table t created by all objects and we are updating with this statement. Given 
that we have 2 DBWR in our system with 8 CPU machine and parallel processing enabled. At any point 
there will be 8 concurrent users updating different rows residing in the same block.

I guess what I don't understand is the point of keeping the inittrans high when we can't update the 
buffer by multiple processes?


Followup   December 20, 2010 - 7am Central time zone:

because only one person at a time can write to a data structure (the block buffer) but many people can concurrent have a transaction going on it.

The get a buffer in current mode for a very very VERY short period of time, just enough time to move the bytes around for your modification - then you immediately let go of the buffer so someone else can get it in current mode.

But you haven't committed yet - your transaction is still active - you are taking up a slot in the ITL for that block and you will be until you commit.

modifying a block in current mode - very very fast.
transactions - very very long relatively speaking.

ITLs - they are for transactions - they support multiple concurrent transactions on the block.
3 stars is select statement required "current mode"   April 30, 2012 - 2am Central time zone
Bookmark | Bottom | Top
Reviewer: goh 
Hi Tom,

   Appreicate if you can help to explain the following. Thanks in advance.

   a) if select a block where some other user is updating the rows in the block will cause buffer 
busy wait. 

rdgs
goh


Followup   April 30, 2012 - 8am Central time zone:

no, reads are not blocked by writes - we use a consistent get for the select - not a current mode get.
5 stars Execellent   April 30, 2012 - 11am Central time zone
Bookmark | Bottom | Top
Reviewer: goh 
Thank you sir.




All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.

About Oracle | Legal Notices and Terms of Use | Privacy Statement