Buffer Busy waits vs. "read by other session"
waits
Some respected Oracle professionals
have noted a relationship between the traditional "buffer
busy wait" and the "read by other session" wait.
Check out this
outstanding Oracle blog by
Glenn Faucett of Sun Microsystems:
"Just to keep us on our toes,
Oracle decided to refer to "buffer busy waits" as "read by other
session" waits in Oracle 10g. I guess this is OK, but it should
at least be documented :) "
Here is a query to display "read by
other session" waits on Oracle 10g:
SELECT
p1 “file#”,
p2 “block#”,
p3 “class#”
FROM
v$session_wait
WHERE
event = 'read by other session';
Confio has a great article on the "Read
by other session" wait and its relation to buffer busy waits:
"read by other session
Definition: When information is requested from the database,
Oracle will first read the data from disk into the database
buffer cache. If two or more sessions request the same
information, the first session will read the data into the
buffer cache while other sessions wait.
In previous versions this wait was classified under the “buffer
busy waits” event.
However, in Oracle 10.1 and
higher this wait time is now broken out into the “read by other
session” wait event. Excessive waits for this event are
typically due to several processes repeatedly reading the same
blocks, e.g. many sessions scanning the same index or performing
full-table scans on the same table. Tuning this issue is a
matter of finding and eliminating this contention."
Confio concludes with a summary that
"read by other session waits" are very similar to buffer busy waits::
"When a session waits on the
“read by other session” event, it indicates a wait for another
session to read the data from disk into the Oracle buffer cache.
If this happens too often the
performance of the query or the entire database can suffer.
Typically this is caused by contention for “hot” blocks or
objects so it is imperative to find out which data is being
contended for. Once that is known this document lists several
alternative methods for solving the issue."
My other notes on segment and buffer
busy contention includes:
|
