hello all, i am on 10.2.0.3. Looks like one of the session is hung...here is the output of the below sql..
select state, seq#, event, seconds_in_wait from v$session_wait where sid = 194;
STATE SEQ#
------------------- ----------
EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
WAITED SHORT TIME 1487
db file scattered read 3143
then when i check what block, file its waiting on ...its waiting on the same file/block for long time...
select sid, event, state, seq#, seconds_in_wait, p1,p2,p3
from v$session_wait
where sid = 194;
SID EVENT
---------- ----------------------------------------------------------------
STATE SEQ# SECONDS_IN_WAIT P1 P2 P3
------------------- ---------- --------------- ---------- ---------- ----------
194 db file scattered read
WAITED SHORT TIME 1487 3296 13 89 6
what can be done to debug this ? or find why is it taking so long to read that block ?? is the block corrupted or something ?