Re: blocking enq-ss wait events

We tried killing the process id of the session which was stuck with event "sort segment request" and the align the user to different temp tablespace. But after we again rerun the query it's again stuck in the same event "sort segment request" even though it's now running on a different temp tablespace. So my thought was it will work as now being a different temp tablespace will take care of the request.

On Thu, May 6, 2021 at 10:15 AM Bhavani Dhulipalla <bhavanidba6_at_gmail.com> wrote:

> We did run into this on Exadata and oracle support had told us it was some
> bug - we had to create the separate temp tablespace for the user to avoid
> contention on sort segment
>
> On Wed, May 5, 2021 at 10:43 PM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> I had to look up ENQ-SS on Oracle support. That's a sort segment
>> contention, usually encountered when SMON is really busy cleaning up a dead
>> / killed transaction.
>>
>> Oracle support has a few notes on this but the most applicable seem to
>> stop at 11.1 .
>>
>> There is one note that says to try to increase PGA_AGGREGATE_TARGET
>> SS Sort Segment Enqueue: 'enq: SS - contention' (Doc ID 2601825.1)
>>
>>
>> Chris
>>
>> On Wed, May 5, 2021 at 11:56 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Hi All, Need some help, we had killed one of the long running sessions
>>> which was running since ~8hrs+ , but after we kill that we see a lot of
>>> "wait for a undo record" wait events but we ignored that thinking that will
>>> run for sometime as because it will do rollback. But suddenly now we are
>>> seeing "enq-SS contention" wait event in addition to "wait for a undo
>>> record" and that is blocking all other application queries. So wondering
>>> how we should mitigate this issue?
>>>
>>> The version is 11.2.0.4 of Exadata.
>>>
>>> Regards
>>> Lok
>>>
>>

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