"Sort Segment Request" wait and SMON

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Tue, 31 May 2005 12:38:45 -0400
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C001C4C1FE@usa0300ms03.na.xerox.net>

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

Received on Tue May 31 2005 - 12:44:12 CDT