Okay,
I've got it now.
Two things that put me off track:
a) "enq: SV - contention" doesn't show up in session events, it's one of
those "wait class other" events
b) The SV enqueue doesn't show up in v$enqueue_stats.
A call for nextval does two "global enqueue gets sync", one for a global SV
enqueue and one for a global NB enqueue, and each get requires two latch
gets.
Strangely we don't see any waits for the NB enqueue - but I think the SV
enqueue operations may be more expensive because they have to go through a
get_value/put_value cycle.
It certainly seems that the work required to get the SV enqueue is much
higher than you might expect when only a single instance is running.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Pawel Smolarz" <pawel.smolarz@xxxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, June 16, 2016 9:06 AM
Subject: Re: SV - contention on RAC EXADATA
Hi,
4 instances - 3 disabled, only one active.
Problem is visible when we have one active instance - We afraid to
start other instances, due to the fact that the problem of the sequence
may increase significantly.
We also checking configuration of resource
manager.
Pozdrawiam / Regards,
Paweł
On Thu, 16 Jun 2016 06:55:13 +0000
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
I should have been more precise with my language.
"installed on 4 nodes" doesn't necessarily mean that you've actually
got 4 active instances accessing the database.
Sorry to be pedantic, but can you confirm that when the problem is
visible there are 4 active instances, and only one instance is
operates the service that executes this code.
My point about "not knowing about Oracle sequences" is that your
supplier doesn't know WHY they chose ORDER, so whatever they thought
they were trying to achieve they weren't, which makes it easier to
decide that the ORDER is redundant: someone needs to argue the
necessity for or against the case - but if you've got a test RAC
system you could at least check the impact.
The other thing that crosses my mind is that the resource manager may
be causing the problem - has it been configured correctly for the
change from single instance to Exadata RAC. Off the top of my head I
can't remember if there were any significant changes in behaviour but
maybe there's something that means you have to change the
configuration manager to get the same effect.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________