Read by other session and db sequential read waits
Date: Thu, 16 Oct 2008 00:18:40 -0400
We have a 4-node Linux x86-64 cluster running 10.2.0.4 (CRS, ASM and DB). For the last one week we are running into a strange issue. One of SQLs which usually executes in under a sec suddenly starts running into db sequential and read by other session waits. We have outline hints to make sure the execution plan is not changing and to eliminate sql related issues.
The problem occurs on only one node and so far it has it every node randomly.
Checked for hot-block contention. Came up with only one with TCH of 50 with more than thousand calls.
The read by other session and db sequential read p1, p2 values change every second to point to different datafiles and blocks from user's default TS.
Any help in identifying the issue or pointing in the right direction will be appreciated very much. I am working with OSS as well on this.