Thanks Karth. I could find the session history.
From the
DBA_HIST_ACTIVE_SESS_HISTORY, I could see the blocking_session, serial#,
Inst_id. In this case, the session seems to be blocking itself. Any
suggestions on how to troubleshoot it further?
Thanks
UpendraSubject: Re: Enq: SS Contention
From: keyantech@xxxxxxxxx
Date: Sun, 6 Sep 2015 07:18:49 -0400
CC: oracle-l@xxxxxxxxxxxxx
To: nupendra@xxxxxxxxxxx
Upendra
For your first question I guess you were looking for
DBA_HIST_ACTIVE_SESS_HISTORY
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3125.htm
Karth
Sent from my IPhone
On Sep 6, 2015, at 5:13 AM, Upendra nerilla <nupendra@xxxxxxxxxxx> wrote:
Hello Everyone -
Oracle RAC 11.2.0.3 on OEL 5.x
Recently we had a meltdown of our production environment with "Enq: SS
Contention" wait event. I noticed a blocking session responsible for most of
the damage. With ASH report I am able to identify the blocking session ID,
Serial#, instance etc.
However when I query the v$active_session_history, I don't see corresponding
session. Is there a way to find out this information?
I am interested in finding out the historical v$session info like client_info,
module, program, osuser etc. This issue happened on 9/4, so I still have the
ASH data, but not for long.
It would be great if you could suggest any way to find out this..
Second part of the question is, I know the contention is related to Temp
tablespace based on the values from p1, p2, p3. What might cause SS enqueue
contention? How could I reduce the possibility of having one in future? FYI, I
already have 1 tempfile/per each Oracle RAC node.
Thanks in advance
-Upendra
