By JeffG-Oracle on Sep 06, 2012
For the questions regarding "read by other session" and its relation to "db file sequential/scattered read",
the logic is like this:
When a "db file sequential/scattered read" is done, the blocks are either already in the cache or on the disk. Since any operation on blocks is done in the cache and since and the issue is "read by other session" I will relate to the case the blocks are on the disk.
Process A is reading the needed block from the disk to the cache. During that time, if process B (and C and others) need the same block, it will wait on "read by other session". A and B can be threads of the same process running in parallel or unrelated processes. For example two processes doing full table scan on mdp_matrix etc.
Solutions for that can be lowering the number of processes competing on the same blocks, increasing PCTFREE. If it is a full table scan, maybe an index is missing that can result in less blocks being read from the cache and so on.