Re: Enq: SS Contention

  • From: Stefan Koehler <contact@xxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>, nupendra@xxxxxxxxxxx
  • Date: Tue, 8 Sep 2015 19:48:24 +0200 (CEST)

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: @OracleSK

Upendra nerilla <nupendra@xxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l


Other related posts: