Book
A reader, August 07, 2009 - 2:49 pm UTC
August 07, 2009 - 3:39 pm UTC
not at all, please do
read by other session
A reader, August 13, 2009 - 12:02 pm UTC
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
August 13, 2009 - 12:43 pm UTC
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.
Understanding
Aru, August 13, 2009 - 10:22 pm UTC
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.
August 24, 2009 - 7:25 am UTC
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...
read by otrher session
ali, August 14, 2009 - 9:40 am UTC
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
August 24, 2009 - 7:51 am UTC
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
read by other session
vicmaq, October 20, 2009 - 2:02 pm UTC
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 ?
October 22, 2009 - 5:13 pm UTC
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.
A reader, December 02, 2010 - 1:16 pm UTC
This was really good explanation with examples ........
Writers blocking readers?
BA, December 02, 2010 - 8:33 pm UTC
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?
December 07, 2010 - 9:13 am UTC
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.
Why do we need higher inittrans if concurrent sessions cannot modify buffers
Haris, December 16, 2010 - 11:25 pm UTC
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?
December 20, 2010 - 7:21 am UTC
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.
is select statement required "current mode"
goh, April 30, 2012 - 2:25 am UTC
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
April 30, 2012 - 8:32 am UTC
no, reads are not blocked by writes - we use a consistent get for the select - not a current mode get.
Execellent
goh, April 30, 2012 - 11:01 am UTC
Thank you sir.
Radek, May 17, 2018 - 8:37 am UTC
Tom, when you said
"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) ..
You meant "free buffer waits" ?? Or I just misunderstood ?
Regards
Radek
May 18, 2018 - 2:41 am UTC
yes that is correct.