Re: Enq: SS Contention

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 8 Sep 2015 19:48:24 +0200 (CEST)
Message-ID: <427797780.271210.1441734504504.JavaMail.open-xchange_at_app01.ox.hosteurope.de>



Hi Upendra,

> It would be great if you could suggest any way to find out this..
You can use Tanel Poder's script dash_wait_chains.sql for historical DBA_HIST_* wait chain analysis: http://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/

You may need to adjust the query a little bit to get the additional columns (MODULE, ACTION, etc.), but this is basically what you are looking for.

> 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.

1) The good thing is that you already have one tempfile per instance. 2) SS enqueue is used for protecting extent caching and uncaching operations. If you have a huge amount of SS enqueue, it is likely that some process / SQL went crazy with sorting or joining and used up all the cached extents in its (local) instance. If this happens the other instance(s) are asked to release the soft reservation for a bunch of extents. However starting with Oracle 11g uncaching is done in bulks of 100 extents per operation, so there have to be some really big sorts/joins, etc. 3) You can create a separate temp tablespace plus a separate user (for that critical SQLs/processes) and bind them to a specific instance (application affinity). So you eliminate the impact of extent caching and uncaching.

However i would first check, if there is something wrong by using such amount of temp space by identifying the root cause SQL (or process).  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Upendra nerilla <nupendra_at_hotmail.com> hat am 6. September 2015 um 11:13 geschrieben:
>
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 08 2015 - 19:48:24 CEST

Original text of this message