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#sthref3159 ) 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