"Sort Segment Request" wait and SMON

Hello,
Does anyone know if there is a correlation between the "Sort Segment
Request" wait while SMON is rolling back a transaction? We had a
situation yesterday in our production system where after the database
bounce, we observed a very large number of blocking lock waits. There
was one process that was waiting on the "Sort Segment Request" wait,
where as the rest were waiting on the "enqueue" event. The database was
practically unusable at that point. As soon as I killed the session that
was waiting on the 'Sort Segment Request' wait, another process started
waiting on that wait. I killed a few processes but as soon as I killed
one, another process started waiting on this event. The alert log file
showed  the infamous "waiting for smon to disable tx recovery" message.
I used oradebug and turned on 10046 on the SMON process. The trace file
showed the following
line:===================================================================
======= 
WAIT #0: nam='db file sequential read' ela= 6004 p1=664 p2=96976 p3=1 
========================================================================
== 
From the dba_extents view, the above file# and block# got mapped to a
rollback segment:
Owner   Segment Name    Segment Type    Tablespace Name 
--------        ----------------- ------------  ------------
SYS             _SYSSMU5$               TYPE2 UNDO
APPS_UNDOTS1 
The v$transaction view showed one active transaction in the above undo
segment. We use the following sequence to bounce sequence to bounce this
database:
Shutdown abort
Startup
Shutdown immediate

Since this situation started to occur soon after the bounce, I suspect
that during the time when the SMON was rolling back an aborted
transaction and at the same time one or more user processes were
requesting a sort segment, there had to be some correlation between
these two activities.

Any help would be appreciated

Thanks
Amir


--
http://www.freelists.org/webpage/oracle-l

Other related posts: