RE: SV - contention on RAC EXADATA

>> This situation we have after migration from standalone database to RAC (on EXADATA) for a system that works for many years


That does, at least, tell us that the choice to implement the ORDER option didn't understand Oracle sequences - it's redundant on non-RAC (or OPS as it might have been) systems.

I can't remember if I've said this, but the "resmgr:cpu quantum" doesn't use CPU, it tells you that the session waiting for it is using too much CPU and has voluntarily gone into a wait state.  This could mean that the session is associated with a particular service or consumer group rather than it being the session itself that is the guilty party, and it doesn't even mean that the service/group/session is using a lot of CPU, only that it's exceeding its quota while the machine is under stress.

Two points of attack: check out the resource manager settings;  and take a few snapshots of v$sesstat and v$session_event to find out which sessions are the biggest consumer of CPU and most frequent waiters for "resmgr: cpu quantum", and "enq: SV contention" (and "DFS lock handle").  

You've said something about only one active node - does this mean you're running active/passive (or even that you've installed the RAC s/w but only started one node), or that you've got multiple nodes but only one of them should be running the SQL ? I did a quick test running up a two-node RAC, and tried running the code on both (with v1 set to 10,000) and don't even see any SV enqueues being acquired.  (I haven't added resource manager to the mix, though; nor have I tried to overload the CPUs with other stuff).


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: Pawel Smolarz [pawel.smolarz_at_nordea.com]
Sent: 15 June 2016 07:39
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: SV - contention on RAC  EXADATA

Hi,

This situation we have after migration from standalone database to RAC
(on EXADATA) for a system that works for many years
and it is difficult to decide on any changes in the code.
I know that using sequence in that way is a redundant and the best
solution will be a change in the code.

Thank you all for the information

Pozdrawiam / Regards,
Pawe³

On Tue, 14 Jun 2016 14:57:58 +0000
Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> I find it hard to imagine a good reason why it's okay for you on your
> instance to get (for example) all the odd numbers between 1 and 120
> while I get all the even numbers in the same range while it's not
> okay for you to get 1-60 while I get 1001 - 1060.  Can you give us
> some idea of what the ordering restriction to supposed to achieve ?
>
> If the requirement is simply to ensure that an entire batch with a
> later timestamp can't have numbers lower than a batch with an earlier
> timestamp then a sequence with an increment of (say) 1,000 could be
> created and the code select nextval just once "per batch" then
> execute select :starting_value + rownum from dual connect by level
> where level <= :v1  That should cut down the potential for the DFS
> lock handle quite significantly.
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
>
>

--